How to validate XML in a Stored Procedure?

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.