XQuery and Other XML Manipulation in SQL Stored Procedures

Relational databases have supported SQL stored procedures for a log time. The benefits of SQL procedures are well-known and include:

• Reduced coding labor due to the creation of reusable processing modules
• Richer processing capabilities in the databases by defining custom data processing logic
• Improved performance and reduced network traffic because stored procedures are executed close to the data, i.e. in the database engine itself

With the introduction of XML support in relational databases, several database products (such as DB2) allow you to access and manipulate not only relational data but also XML in stored procedures. This is made possible through:

(a) the introduction of the XML data type in the SQL type system, and

(b) several XML functions in the SQL language such as XMLQUERY, XMLEXISTS, and XMLTABLE that can contain XPath or XQuery expressions.

The picture below provides an example of some of the basic capabilities for XML handling stored procedures:

• Input and output parameters of type XML. In DB2, if an XML document already exists in a parsed format in the database then passing it to the XML parameter of the procedure does not require the document to be reparsed. XML procedures can operate directly on the parsed representation of the XML. Also, if an application calls a stored procedure and passes a textual XML document to the XML parameter, the document gets parsed only once upon entering the procedure regardless of how often the XML parameter is used in the body of the procedure.
• XML type variables. XML variables can be assigned values of type XML, such as a document or some document fragment, even a single XML element or atomic value. XML variables hold XML in a parsed format, which is important for performance. For example, the second and last SET statement in the picture below uses an XQuery FLWOR expression to read a certain address from the input document and assigns it to the XML variable “address”.
• XML value extraction. The XMLQUERY function can contain XPath or XQuery expression to extract values from a document. In the first SET statement in the picture below, the XMLQUERY function on the right-hand side takes the XML input parameter “custDoc” as context and uses an XPath to extract the @Cid attribute from the document. The XMLCAST function converts the attribute to the SQL type INTEGER, so that it can be assigned to the SQL variable “id”.
• XML conditions. If-then-else statements in the SQL procedure language can use XML conditions in the IF-clause to decide what to do next in the control flow of the stored procedure.

(Click the picture for full size)

The examples above are certainly not an exhaustive list of what you can do with XML in stored procedure. For example, while the stored procedure above only operates on a single document that is passed in via the XML parameter, stored procedures can also use XQuery or SQL/XML to access sets of XML documents that are stored in XML columns.

Stored procedures can also compose, prepare, and execute XQuery or SQL/XML statements dynamically. You can define cursors over the result sequence of an XQuery and process each returned item one by one in a loop. You can also construct, split, shred, modify, merge, compare, or validate XML documents in stored procedures.

Of course, all the error and exception handling that is available for stored procedures can also be used with any XML operations. In summary, you can code sophisticated XML processing logic (or hybrid XML/relational processing logic) with the familiar concepts of SQL stored procesures.

Many more examples of XML processing in DB2 Stored Procedures can be found in Chapter 18 of the DB2 pureXML Cookbook.