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.