Guru: The XML Composition Conundrum of XMLNAMESPACES or XMLATTRIBUTES

April 3, 2017 Michael Sansoterra

Hey, Mike:

After reading the IT Jungle Guru tips on DB2 XML Composition (see Related Stories below), I’ve been able to build XML from relational data. I have everything working but now I need to enclose all data in a “Document” tag after the XML declaration like the example here:

<?xml version="1.0" encoding="utf-8"?>
<Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns="http://MyCompany.com" 
xsi:schemaLocation="http://MyCompany.com validate.xsd">

I tried using XMLNAMESPACES but can’t get it to produce the “xsi:schemaLocation” tag. How can I construct the document tag I need?

–N.C.

The problem here is that the xsi:schemaLocation in your example is not a namespace, but an attribute (as identified in articles by w3schools.com and microsoft.com). Therefore, you should use the XMLATTRIBUTES function rather than XMLNAMESPACES to incorporate it into your XML.

Let me illustrate how to build all of this using the DB2 XML functions. To keep it easy to understand, the following SQL builds XML from basic relational data using the XMLELEMENT function:

CREATE TABLE MyTable (Contents VARCHAR(12));
INSERT INTO MyTable VALUES ('NADA');
SELECT XMLELEMENT(NAME "MyData", Contents) AS MyXML
  FROM MyTable;

The query returns this simple XML:

<MyData>NADA</MyData>

Now, we’ll embed the above query in a common table expression called BUILD_XML and then incorporate the query’s XML within the desired <Document> root element using the XMLELEMENT and XMLSERIALIZE functions:

WITH BUILD_XML (MY_XML) AS  (
SELECT XMLELEMENT(NAME "MyData", Contents) AS MyXML
  FROM MyTable
)
SELECT XMLSERIALIZE(
XMLELEMENT(NAME "Document",MY_XML)
AS CLOB(1M) INCLUDING XMLDECLARATION) 
  FROM BUILD_XML

XMLELEMENT is used to build the Document root element (with the MY_XML column supplying the element’s child nodes) and the XMLSERIALIZE function adds the declaration:

<?xml version="1.0" encoding="UTF-8"?>
<Document>
<MyData>NADA</MyData>
</Document>

Now all that is left to do is to add the two namespaces:

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns="http://MyCompany.com" 

And then the troublesome schemaLocation attribute to the Document element:

xsi:schemaLocation="http://MyCompany.com validate.xsd"

Both of these can be added to the XML by specifying the XMLNAMESPACES and XMLATTRIBUTES functions as parameters to the XMLELEMENT function (for the Document element):

WITH BUILD_XML (MY_XML) AS (
SELECT XMLELEMENT(NAME "MyData", Contents) AS MyXML
  FROM MyTable
)
SELECT XMLSERIALIZE(
XMLELEMENT(NAME "Document",
XMLNAMESPACES(
'http://www.w3.org/2001/XMLSchema-instance' as "xsi",
DEFAULT 'http://MyCompany.com'
),
XMLATTRIBUTES(
'http://MyCompany.com validate.xsd' AS "xsi:schemaLocation"
),
MY_XML)
AS CLOB(1M) INCLUDING XMLDECLARATION) 
  FROM BUILD_XML

The result is:

<?xml version="1.0" encoding="UTF-8"?>
<Document 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns="http://MyCompany.com" 
xsi:schemaLocation="http://MyCompany.com validate.xsd">
<MyData>NADA</MyData>
</Document>

And that’s all it takes to build the required XML. XML has so many facets, that when you encounter something unfamiliar, sometimes it’s easier to search for it online (such as schemaLocation) to make sure you’ve correctly identified what it is (attribute, namespace, element, CDATA, etc.). Thereafter you can pick the appropriate DB2 for i function to assist with the XML composition.

RELATED STORIES

Composing An XML Document From Relational Data, Part 1

Composing An XML Document From Relational Data: Part 2