XML and XQuery support in SQL procedures

SQL procedures support parameters and variables of data type XML. They can be used in SQL statements in the same way as variables of any other data type. In addition, variables of data type XML can be passed as parameters to XQuery expressions in XMLEXISTS, XMLQUERY and XMLTABLE expressions.

  CREATE TABLE T1(C1 XML) %

  CREATE PROCEDURE proc1(IN parm1 XML, IN parm2 VARCHAR(32000))
  LANGUAGE SQL
  BEGIN
     DECLARE var1 XML;

     /* check if the value of XML parameter parm1 
        contains an item with a value less than 200 */
     IF(XMLEXISTS('$x/ITEM[value < 200]' passing by ref parm1 as "x"))THEN

       /* if it does, insert the value of parm1 into table T1 */
       INSERT INTO T1 VALUES(parm1);

     END IF;

     /* parse parameter parm2's value and assign it to a variable */
     SET var1 = XMLPARSE(document parm2 preserve whitespace);

     /* insert variable var1 into table T1
     INSERT INTO T1 VALUES(var1);

  END %

In this example, there is a table T1 with an XML column. The SQL procedure accepts two parameters of data type XML named parm1 and parm2. Within the SQL procedure an XML variable is declared named var1.

The logic of the SQL procedure checks if the value of XML parameter parm1 contains an item with a value less than 200. If it does, the XML value is directly inserted into column C1 in table T1.

Then the value of parameter parm2 is parsed using the XMLPARSE function and assigned to XML variable var1. The XML variable value is then also inserted into column C1 in table T1.

The ability to implement control flow logic around XQuery operations makes it easy to develop complex algorithms that query and access XML data stored in a database.