Wednesday, November 10, 2010

How long is long? Maximum size of an XML text node in DB2

I was recently asked about the maximum size of a single text node when stored in DB2 pureXML. Basically, when you create a table
CREATE TABLE test(id int, doc xml)

and insert an XML document with several elements, how long can the text between the open and closing tag be, e.g., the value of myText?
INSERT INTO test VALUES(1,'<root><myText>all the text that you want</myText></root>')

The background to this question is that XML values are formatted to data pages. If the XML document is larger than the page size, the document is broken up into subtrees that fit into the page. However, a text node is already a single node. Can a text value be larger than the page size?

My answer: It can be really, really large. Try it out and let me know the biggest value that you could insert (or produce in an update statement).