Using the XMLTABLE function in UPDATE and MERGE statements

The XMLTABLE function is part of the SQL standard. It can take XML as input, use XPath or XQuery expressions to extract values or pieces from the XML, and return the result in a relational row and column format.

As discussed in a previous blog post, the XMLTABLE function is often used in queries to read XML documents from an XML column in the database and return the extracted values as a relational result set.

But, the XMLTABLE function can also be used in INSERT and UPDATE statements. This allows an application to pass an XML document as a parameter to an INSERT or UPDATE statement that extractx selected values and uses them to insert or update relational rows in a table.

Using the XMLTABLE function in INSERT statements is quite common if requirements dictate that XML needs to be shredded to relational tables rather than stored natively in an XML column.

Here is a simple example that extracts values from a small XML document and inserts these values into the table “reltable”:

CREATE TABLE reltable(id int, col2 int, col3 varchar(20));

INSERT INTO reltable
SELECT id, x, y
FROM XMLTABLE ('$doc/mydata'
       PASSING xmlparse(document '<mydata id="1">
                                     <elem1>555</elem1>
                                     <elem2>test</elem2>
                                  </mydata>') as "doc"
       COLUMNS
          id INTEGER     PATH '@id',
          x  INTEGER     PATH 'elem1',
          y  VARCHAR(20) PATH 'elem2'   );

SELECT * FROM reltable;

ID          COL2        COL3
----------- ----------- --------------------
          1         555 test

1 record(s) selected.

The PASSING clause assigns the input document to the variable “$doc” which is then the starting point for the extraction.

You don’t necessarily need to hardcode the XML input document in the INSERT statement. It can also be supplied via a parameter marker or host variable, as in this example:

INSERT INTO reltable
SELECT id, x, y
FROM XMLTABLE ('$doc/mydata'  PASSING  cast(? as XML) as "doc"
       COLUMNS
          id INTEGER     PATH '@id',
          x  INTEGER     PATH 'elem1',
          y  VARCHAR(20) PATH 'elem2'   );

Ok, this was the easy part.

Now what if we want to use the XMLTABLE function in an UPDATE statement to replace the values in an existing row in “reltable” with new values from an incoming XML document? It might not be quite as obvious how to write such an UPDATE statement, but as it turns out it’s not very hard either!

Here is an UPDATE statement that extracts the values of the XML elements “elem1” and “elem2” to update the columns “col2” and “col3” in “reltable” for the row that has id = 1.

UPDATE reltable
SET (col2, col3) = (SELECT x, y
                    FROM XMLTABLE ('$doc/mydata' PASSING
                           xmlparse(document '<mydata id="1">
                                                 <elem1>777</elem1>
                                                 <elem2>abcd</elem2>
                                              </mydata>') as "doc"
                         COLUMNS
                           x  INTEGER     PATH 'elem1',
                           y  VARCHAR(20) PATH 'elem2'   )
                    )
WHERE id = 1;

SELECT * FROM reltable;

ID          COL2        COL3
----------- ----------- --------------------
          1         777 abcd

1 record(s) selected.

The UPDATE statement above uses a simple WHERE clause to select a specific row to be updated. But, what if we don’t know which target row the incoming XML document needs to be applied to? Ideally, we want to extract the @id attribute from the input document and update whichever row matches its value.

We might be tempted to simply extract the @id attribute in the same XMLTABLE function and add a join predicate to the subselect, like this:

-- this statement is not a good idea!
UPDATE reltable r
SET (r.col2, r.col3) = (SELECT T.x, T.y
                        FROM XMLTABLE ('$doc/mydata'
                              PASSING  cast(? as XML) as "doc"
                              COLUMNS
                                id INTEGER     PATH '@id',
                                x  INTEGER     PATH 'elem1',
                                y  VARCHAR(20) PATH 'elem2' ) T
                        WHERE r.id = T.id
                       );

But, this statement would update all rows in the table (many of them with NULL values) because the WHERE clause only applies to the rows produced by the subselect, not to the rows in “reltable”.

One possible solution is to add a WHERE clause that extracts the @id attribute as needed to filter the rows in “reltable”:

-- this statement is better, but not optimal
UPDATE reltable
SET (col2, col3) = (SELECT x, y
                    FROM XMLTABLE ('$doc/mydata'
                          PASSING  cast(? as XML) as "doc"
                          COLUMNS
                            x  INTEGER     PATH 'elem1',
                            y  VARCHAR(20) PATH 'elem2' )
                    )
WHERE id = XMLCAST( XMLQUERY('$doc/mydata/@id'
                    PASSING cast(? as XML) as "doc") AS INTEGER);

Yes, this works, but it requires us to pass the XML document into the statement twice: once into the subselect in the SET clause, and once into the XMLQUERY function in the WHERE clause. That’s not very elegant and probably  not ideal for performance either.

There might be multiple ways to improve the UPDATE statement above. One nice solution is to use a MERGE statement:

MERGE INTO reltable r
USING (SELECT id, x, y
       FROM XMLTABLE ('$doc/mydata' PASSING  cast(? as XML) as "doc"
             COLUMNS
               id INTEGER     PATH '@id',
               x  INTEGER     PATH 'elem1',
               y  VARCHAR(20) PATH 'elem2'   )) t
ON r.id = t.id
WHEN MATCHED
THEN UPDATE SET r.col2 = t.x, r.col3 = t.y;

The nice thing about the MERGE statement is that it can also handle multiple cases where the XMLTABLE function produces multiple rows whose keys may or may not already exist in the target table, so that UPDATE and/or INSERT operations need to be performed. Here is an example:

MERGE INTO reltable r
USING (SELECT id, x, y
       FROM XMLTABLE ('$doc/root/mydata'
             PASSING xmlparse(document '<root>
                                         <mydata id="1">
                                          <elem1>999</elem1>
                                          <elem2>xyz</elem2>
                                         </mydata>
                                         <mydata id="3">
                                          <elem1>333</elem1>
                                          <elem2>test33</elem2>
                                         </mydata>
                                        </root>') as "doc"
             COLUMNS
               id INTEGER     PATH '@id',
               x  INTEGER     PATH 'elem1',
               y  VARCHAR(20) PATH 'elem2'   )) t
ON r.id = t.id
WHEN MATCHED     THEN UPDATE SET r.col2 = t.x, r.col3 = t.y
WHEN NOT MATCHED THEN INSERT VALUES(t.id, t.x, t.y);

SELECT * FROM reltable;

ID          COL2        COL3
----------- ----------- --------------------
          1         999 xyz
          3         333 test33

2 record(s) selected.

This blog post has provided some basic examples that might serve as a starting point to develop your own INSERT, UPDATE, and MERGE statements with the XMLTABLE function.