DB2 Version 9.7 for Linux, UNIX, and Windows

XML data is passed by reference in SQL stored procedures

In SQL stored procedures, when you assign XML data to XML input, output, or input/output parameters or XML local variables, the XML values are now passed by reference. Therefore, some operations using XML data return results that are different from the results returned by the same operations in DB2® Version 9.5 and earlier.

Details

When you assign XML data to a parameter or local variable and the values are passed by reference, the node identities and the parent property are preserved. Therefore, results of the following types of operations might change:

Operations that use the node identities of the XML value
Operations that use the parent axis in an XPath expression

The following types of expressions use node identity:

Node comparisons. The IS operator uses node identity to determine if two nodes have the same identity. The >> operator and << operator use node identity to compare the document order of nodes.
Path expressions. Path expressions use node identity to eliminate duplicate nodes.
Sequence expressions. The UNION, INTERSECT, and EXCEPT operators use node identity to eliminate duplicate nodes.

In DB2 Version 9.5 and earlier, when you assign XML data to a parameter or local variable, the XML data is passed by value. Therefore, node identities and the parent property are not preserved.

User response

Check the stored procedure to ensure that it returns the correct results when performing operations comparing node identities and operations that use the parent axis in path expressions.

Example

The stored procedure in the example shows that different results are returned when passing XML data by reference and by value.

The stored procedure uses data from a table that contains an XML column and returns results into a second table. The following statements create the tables and insert data into the first table:

CREATE TABLE t1 (c1 INT, c2 XML) ~
INSERT INTO t1 VALUES (1, '<a><b><d>1</d></b><c>2</c></a>') ~

CREATE TABLE t2 (c1 INT, c2 VARCHAR(1000)) ~
 ~

The stored procedure assigns the XML data from the XML column to two XML variables and performs operations that return different results depending on the version of the DB2 database server being used:

CREATE PROCEDURE MYTESTPROC (  )
BEGIN
  DECLARE v1, v2, v3 XML;

  -- Assign XML value to v1 and v2
  SELECT XMLQUERY('$c/a/b' passing by ref c2 as "c") INTO v1
  FROM t1  WHERE c1 = 1;

  SELECT XMLQUERY('$c/a/b' passing by ref c2 as "c") INTO v2
  FROM t1  WHERE c1 = 1;

  -- insert XML value into t2 
  INSERT INTO t2 VALUES (1, xmlserialize(v1 as VARCHAR(1000)));

  -- OR operator combining sequences of nodes
  -- If node identities are identical, sequence expression will drop duplicate nodes
  SET v3 = xmlquery ('$x | $y' passing v1 as "x", v2 as "y"); 
  INSERT INTO t2 VALUES (2, xmlserialize(v3 as VARCHAR(1000)));

  -- Creating a sequnce of nodes
  SET v3 = xmlquery ('($x,$y)' passing v1 as "x", v2 as "y"); 
  -- If node identities are identical, path expression will drop duplicate nodes
  SET v3 = xmlquery ('$x/d' passing v3 as "x");
  INSERT INTO t2 VALUES (3, xmlserialize(v3 as VARCHAR(1000)));

  -- Test of parent axis property
  SET v3 = xmlquery('$x/..' passing v1 as "x");
  INSERT INTO t2 VALUES (4, xmlserialize(v3 as VARCHAR(1000)));
 
  -- NODE ID comparison
  if(xmlcast(xmlquery('$X is $Y' passing by ref v1 as X, v2 as Y) as VARCHAR(5))='true') then
    INSERT INTO t2 VALUES (5, 'NODE ID preserved');
  else
   INSERT INTO t2 VALUES (5, 'NODE ID NOT preserved');
  end if;
END

The stored procedure returns the following values for the different versions of the DB2 database server.

Table 1. Stored procedure values inserted to table t2
Column c1	DB2 Version 9.7 (passing by reference)	DB2 Version 9.5 (passing by value)
1	<b><d>1</d></b>	<b><d>1</d></b>
2	<b><d>1</d></b>	<b><d>1</d></b><b><d>1</d></b>
3	<d>1</d>	<d>1</d><d>1</d>
4	<a><b><d>1</d></b><c>2</c></a>	NULL
5	NODE ID preserved	NODE ID NOT preserved