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.
XML Storage in DB2: To inline or not to inline?
July 11, 2012
First, let’s revisit the concept of inlined XML storage and then discuss the pros and cons of inlining.
What is XML Inlining?
In short, inlining is an optional storage optimization in DB2 for “small” XML documents.
When you define a table with an XML column in DB2, such as CREATE TABLE mytable(id INTEGER, ….. , doc XML), the DB2 server creates three storage objects in the table space:
- A data object (DAT), which holds the relational rows of the table
- An index object (INX), which stores any indexes for the table
- An XML storage object (XDA), which is the XML Storage Area and holds any XML documents
Optionally, you can assign these three objects to different table spaces but by default they all go into the same table space.
As illustrated in the following picture, the XML column in the data object doesn’t contain the actual XML document, but only references (descriptors) of where the documents can be found. The XML document trees are stored in the XDA object, and if a tree is larger than a single page then it is automatically cut into multiple regions. This way, large documents can span many pages, and that’s completely transparent to your application.
The region index -automatically defined and maintained by DB2- essentially remembers which regions belong to the same XML document for any given row in the data object. The regions index also enables very efficient access to any portion of an XML document. If only some part of a large document is required, e.g. to answer a query, DB2 does not necessarily need to bring all pages of the document into the buffer pool.
The XML documents that are small enough to fit onto a single page just have a single region, and multiple regions can be stored on the same page, if space is available.
As it turns out, there are very many applications that deal with small XML documents, often just 1KB to 20KB for most documents. The access to such small documents can be optimized by storing there document trees right in the DAT object, together with the rows that they belong to. This is called inlined XML storage and is illustrated in the following picture.
To enable inlining, define the XML column with an INLINE LENGTH that indicates that maximum size up to which you want documents to be inlined. For example:
CREATE TABLE mytable(id INTEGER, ….. , doc XML INLINE LENGTH 30000)
In this example, any XML documents whose parsed hierarchical representation is less than 30000 bytes will be inlined. Any documents that are larger than this are automatically stored in the XDA object as usual. The application sees no difference.
The specified INLINE LENGTH must be smaller than the page size of the DB2 table space minus the length of any other columns in the table.
Inlined documents are stored in the same tree format as documents that are not inlined. Just in a different location.
The Pros and Cons of Inlining
Performance measurements and plenty of experience with real XML applications have shown that inlining is almost always recommended, if a large percentage of your XML documents (for example, more than 70%) can be inlined. The benefits of inlining include the following:
- Faster access to inlined documents – no redirection via the regions index
- The regions index has no entries for inlined documents. If a large percentage of your documents are inlined then this reduces the space and maintenance cost associated with the regions index.
- Better prefetching since inlined XML documents are prefetched as part of the row they belong to.
A key characteristic of inlining is that it drastically increases the row size on the data pages, and hence reduces the number of rows per data page. This can negatively impact the performance of queries that read only relational columns and do not access the XML column. For example, consider the following table and query:
CREATE TABLE myxml(c1 INT, c2 INT, c3 INT, doc XML INLINE LENGTH 30000);
SELECT SUM(c1 + c2 + c3)
FROM myxml;
This query reads the 3 integer columns from *all* rows in the table. Due to the inlined XML column, these rows are spread over a much larger number of pages than without inlining, so this query needs to fetch a lot more pages than without inlining. If you have many such queries, then inlining might not be the best choice for you.
How do I know whether a given document is inlined?
The DB2 function ADMIN_IS_INLINED can be applied to an XML column and it returns 1 if a document is inlined, and zero otherwise. This enables you to determine which and how many documents are inlined.
The DB2 function ADMIN_EST_INLINE_LENGTH can also be applied to an XML document in an XML column and returns the smallest required inline length for this document to be inlined, or -1 if the document is too large to be inlined.
Native XML Database 