xmlpostgresqlnamespacesxmltable

Postgres XMLTABLE problem with namespaces in the XML data


I have an example XML dataset that contains a namespace reference:

<Records xmlns="http://example.com/sample.xsd">
   <Record>
     <Id>1</Id>
   </Record>
   <Record>
     <Id>2</Id>
   </Record>
   <Record>
     <Id>3</Id>
   </Record>
   <Record>
     <Id>4</Id>
   </Record>
 </Records>

When I run the following query, I should get 4 rows, one for each record:

with cte_data as (
    select '<Records xmlns="http://example.com/sample.xsd">
   <Record>
     <Id>1</Id>
   </Record>
   <Record>
     <Id>2</Id>
   </Record>
   <Record>
     <Id>3</Id>
   </Record>
   <Record>
     <Id>4</Id>
   </Record>
 </Records>'::xml as xml_data
    )
select *
from cte_data d
left join xmltable(xmlnamespaces('xmlns="http://example.com/sample.xsd"' as x),
     'Records/Record' passing d.xml_data
     columns id text path 'Id'
     ) as x on true;

Instead, I get a single result row (the XML) without any data from the XML (id is NULL).

Interestingly, if I remove the namespace from the XML, it works just fine! The problem is that I cannot change the XML data that I'm processing to remove the namespaces. I hope that I'm merely overlooking something that someone can point out to me.

EDIT: The answer provided by @Yitzhak does work, however the true situation is a bit more complex:

with cte_data as (
    select '<Records xmlns:ns="http://example.com/sample.xsd">
   <ns:Record>
     <ns:Id>1</ns:Id>
   </ns:Record>
   <ns:Record>
     <ns:Id>2</ns:Id>
   </ns:Record>
   <ns:Record>
     <ns:Id>3</ns:Id>
   </ns:Record>
   <ns:Record>
     <ns:Id>4</ns:Id>
   </ns:Record>
 </Records>'::xml as xml_data
    )
select x.*
from cte_data d
, xmltable(xmlnamespaces('http://example.com/sample.xsd' as "ns"),
     '/ns:Records/ns:Record' 
     passing d.xml_data
     columns id text path 'ns:Id'
     ) x

This also fails to return results.


Solution

  • Please try to modify namespaces declaration as follows, and use it in XPath expression as a prefix in a proper locations.

    db-fiddle

    SQL

    with cte_data as (
        select '<Records xmlns:ns="http://example.com/sample.xsd">
        <ns:Record>
            <ns:Id>1</ns:Id>
        </ns:Record>
        <ns:Record>
            <ns:Id>2</ns:Id>
        </ns:Record>
        <ns:Record>
            <ns:Id>3</ns:Id>
        </ns:Record>
        <ns:Record>
            <ns:Id>4</ns:Id>
        </ns:Record>
    </Records>'::xml as xml_data
        )
    select x.*
    from cte_data d
    , xmltable(xmlnamespaces('http://example.com/sample.xsd' as "ns"),
         '/Records/ns:Record' 
         passing d.xml_data
         columns id text path 'ns:Id'
         ) x