Encapsulating database operations in stored procedures has a variety of potential benefits. For example, stored procedures allow you to code more complex data manipulation logic than what can be done with an individual SQL statement.  Also, combining multiple operations in a single stored procedure can improve performance because the operations are executed close to the data (in the database engine) with a single invocation from the application, reducing the number of API calls to the database.

For these reasons it is a quite common practice to implement pieces of XML processing logic in database stored procedures. Some applications want the database to validate XML documents against an XML Schema, which is easy to do as part of insert, update, or load operations, or even in database queries. But, how to validation XML in a stored procedure?

It’s easy. Let’s look at the following procedure as a simple example. It takes an XML document “doc” as an input parameter, and returns a flag “isvalid” which is 1 if the document is valid and 0 if the document is not valid. Of course, you could return more detailed information if needed, but 0 and 1 should be sufficient as an example.

The procedure declares a condition handle for SQLSTATE 2200M, which is raised when an XML document is not well-formed or not valid in an attempted schema validation. If that state should occur during the execution of the stored procedure, then the corresponding exit handler gets executed. In this example, the exit handler simply sets “isvalid” to zero.

CREATE PROCEDURE processXML(IN doc XML, OUT isvalid INTEGER)
 BEGIN
  DECLARE invalid_document CONDITION FOR '2200M';
  DECLARE EXIT HANDLER FOR invalid_document 
       SET isvalid = 0; -- indicate that doc failed to validate
  IF (XMLVALIDATE(doc ACCORDING TO XMLSCHEMA ID db2admin.myschema) IS VALIDATED)
   THEN
      -- indicate that the document is valid:
      SET isvalid = 1;
      -- then process the XML as intended:
             ....
             ....
             ....
    END IF;
END # -- end of procedure

 

After these declarations, the first operation in the stored procedure is the document validation with the XMLVALIDATE function. The XMLVALIDATE function specifies the name (ID) of the XML Schema that is being used for validation. In this example, the schema name is hardcoded as “db2admin.myschema”, but it could also be another parameter into the stored procedure.

If the validation is successful (i.e. IS VALIDATED is true) then we set the “isvalid” flag to 1 and proceed with whatever processing we want to perform on the XML document. If the validation is not successful, then the procedure executes the exit handler and terminates.

This example provides a simple skeleton for XML stored procedures, to ensure that the XML processing in the body of the procedure is performed only if validation is successful.

I spent this week in Philadelphia at the annual DB2 user group conference. The week was packed with excellent technical sessions, panel discussions, customer presentations, hands-on labs, seminars, and lots of networking opportunities.

As you would expect from such a big event, a very large variety of DB2 and application development topics were on the grid, including DB2 BLU, high availability, DB2 11 for z/OS, query optimization, DB2 in the Cloud, and many others.

And, almost 9 years since the introduction of pureXML in DB2 9.1, XML was still a popular topic at this year’s conference, with some excellent sessions from DB2 users and from IBMers:

E03 – XML – Essentials for the DB2 z/OS DBA
Thanikachalam “Billy” Sundarrajan, Fifth Third Bank

E05 – XML and JSON in Examples
Jan Marek, CA Technologies

E17 – XML Data Processing Performance
Aleskey Slutch and Alexander Veremyev, Raiffeisenbank Russia

Hands-on Lab: Learning Exciting JSON and XML in IBM DB2 for z/OS
Jane Man, IBM Corporation