Sunday, March 11, 2012

Turn XML data into columnar, relational format using SQL (lots of useful links inside)

The SQL standard offers many functions, some very useful are defined in part 14 of the SQL standard, "ISO/IEC 9075-14:2011 Part 14: XML-Related Specifications". That part 14 was published 2003, so it is rather old. However, one of the gems in SQL/XML (the name for that XML addition to SQL) and in DB2, the function XMLTABLE and its capabilities seem to be hidden as a recent email to me indicates. Or is it how Google, Bing, and other are used...? Anyway, in this article I will provide pointers to some useful resources, mostly older articles, and tag them with the right buzzwords. All in the hope that it is one email less in my inbox...

On IBM's developerWorks is a two-part article giving a detailed overview about XMLTABLE. In part 1 we learn about all the ways XML data can be turned into relational format, including best practices. The 2nd part deals with advanced processing, such as splitting XML documents into smaller pieces (including parameter passing), shredding XML documents during insert processing (turn XML into tables during insert), relational views over XML data, and creating field/value pairs out of XML data (is this already NoSQL...?). Parameter passing for XML processing is also a topic of one of my older posts. XMLTABLE can also be used to extract XML data embedded into another XML document via CDATA. And last, a useful webcast that discussed XMLTABLE in DB2 for both the mainframe and the distributed platform can be found at the pureXML devotees. That group, now driven by IBM customers using pureXML, has upcoming meetings/webcasts. Last but not least, Matthias Nicola has written many articles about XMLTABLE in his blog.

Let's see what the search engines do with this article. Have a nice week...