Example: Use XMLTABLE with namespaces

XML namespaces are a W3C XML standard for providing uniquely named elements and attributes in an XML document. XML documents may contain elements and attributes from different vocabularies but have the same name. By giving a namespace to each vocabulary, the ambiguity is resolved between identical element or attribute names.

In XML documents, you declare XML namespaces with the reserved attribute xmlns, whose value must contain an Universal Resource Identifier (URI). URIs are used as identifiers; they typically look like a URL but they don't have to point to an existing web page. A namespace declaration can also contain a prefix, used to identify elements and attributes. Below is an example of a namespace declaration with and without prefix:

xmlns:ibm = "http://www.ibm.com/xmltable/"
xmlns = "http://www.ibm.com/xmltable/"

To demonstrate the use of namespaces with XMLTABLE, a sample document is added to the previous example, so we are working with the following three rows:

<dept bldg="101">
  <employee id="901">
    <name>
      <first>John</first>
      <last>Doe</last>
    </name>
    <office>344</office>
    <salary currency="USD">55000</salary>
  </employee>
  <employee id="902">
    <name>
      <first>Peter</first>
      <last>Pan</last>
    </name>
    <office>216</office>
    <phone>905-416-5004</phone>
  </employee>
</dept>

<dept bldg="114">
  <employee id="903">
    <name>
      <first>Mary</first>
      <last>Jones</last>
    </name>
    <office>415</office>
    <phone>905-403-6112</phone>
    <phone>647-504-4546</phone>
    <salary currency="USD">64000</salary>
  </employee>
</dept>

<ibm:dept bldg="123" xmlns:ibm="http://www.ibm.com/xmltable">
  <ibm:employee id="144">
    <ibm:name>
      <ibm:first>James</ibm:first>
      <ibm:last>Bond</ibm:last>
    </ibm:name>
    <ibm:office>007</ibm:office>
    <ibm:phone>905-007-1007</ibm:phone>
    <ibm:salary currency="USD">77007</ibm:salary>
  </ibm:employee>
</ibm:dept>

In order to return all the employees in the database, you can use the * wildcard for the namespace prefix in the path expressions. This causes all elements to be considered, regardless of namespaces, because this wildcard (*) matches any namespace including no namespace.

SELECT X.*
  FROM emp,
       XMLTABLE ('$d/*:dept/*:employee' PASSING doc AS "d"
                 COLUMNS
                      empID     INTEGER     PATH '@*:id',
                      firstname VARCHAR(20) PATH '*:name/*:first',
                      lastname  VARCHAR(25) PATH '*:name/*:last') AS X

The result of the query is:

EMPID       FIRSTNAME            LASTNAME
----------- -------------------- -------------------------
        901 John                 Doe
        902 Peter                Pan
        903 Mary                 Jones
        144 James                Bond

For this specific data, the namespace wildcard for the attribute @id was not strictly necessary. The reason is that the @id attribute employee James Bond has no namespace. Attributes never inherit namespaces from their element and also never assume the default namespace. So, unless the attribute name has a prefix, it doesn't belong to any namespace.

The use of the wildcard expression is the simplest way to return all employees, regardless of namespace.

Declaring a default element namespace

When all the elements you want to query belong to the same namespace, declaring a default element namespace can be the simplest way to write your queries. You just need to declare the default namespace in the beginning of your XPath expression and, after that, all unqualified elements you reference are tied to that namespace.

SELECT X.*
  FROM emp,
       XMLTABLE ('declare default element namespace "http://www.ibm.com/xmltable";
                 $d/dept/employee' PASSING doc AS "d"
                 COLUMNS
                      empID     INTEGER     PATH '@id',
                      firstname VARCHAR(20) PATH 
                 'declare default element namespace "http://www.ibm.com/xmltable"; name/first',
                      lastname  VARCHAR(25) PATH 
                 'declare default element namespace "http://www.ibm.com/xmltable"; name/last') AS X

The result is:

EMPID       FIRSTNAME            LASTNAME
----------- -------------------- -------------------------
        144 James                Bond

The column-generating expressions do not inherit the namespace declaration from the row-generating expression. Each column-generating expression is a separate XPath query and needs its own namespace declaration. These namespace declarations may differ from each other, for example, if your document contains multiple namespaces.

Often there is only one namespace, in which case it would be convenient to declare a single namespace for all expressions in the XMLTABLE function. This can be achieved by using the function XMLNAMESPACES(). This function allows you to declare a default element namespace and/or several namespace prefixes to be used within the XMLTABLE function. The advantage of using the XMLNAMESPACES function is that the declared namespaces are global for all expressions in the XMLTABLE context, so all the XPath expressions will be aware of these namespaces declarations and repeated namespace declarations are not required.

The default namespace declared by the XMLNAMESPACES function applies to both the row-generating expression and all the column-generating expressions. This way only one namespace declaration is needed for all XPath expressions in an XMLTABLE function. The result of the following query is exactly the same as the previous example.

SELECT X.*
  FROM emp,
       XMLTABLE (XMLNAMESPACES(DEFAULT 'http://www.ibm.com/xmltable'),
                 '$d/dept/employee' PASSING doc AS "d"
                 COLUMNS
                      empID     INTEGER     PATH '@id',
                      firstname VARCHAR(20) PATH 'name/first',
                      lastname  VARCHAR(25) PATH 'name/last') AS X

Declaring a namespace prefix with XMLNAMESPACES

If you want to select elements and attributes from multiple specific namespaces, then using namespace prefixes can be your best option. Unless you use the XMLNAMESPACES function, the namespaces prefixes need to be declared for every expression. But, just like for default element namespaces, you can use the XMLNAMESPACES function to avoid repeated namespace declarations.

SELECT X.*
  FROM emp,
       XMLTABLE (XMLNAMESPACES('http://www.ibm.com/xmltable' AS "ibm"),
                 '$d/ibm:dept/ibm:employee' PASSING doc AS "d"
                 COLUMNS
                      empID     INTEGER     PATH '@id',
                      firstname VARCHAR(20) PATH 'ibm:name/ibm:first',
                      lastname  VARCHAR(25) PATH 'ibm:name/ibm:last') AS X