CODEWITHSUNDEEP
xmlpostgresqlnamespacesxmltable
SQL RV
SQL RV

Reputation: 97

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.

Upvotes: 0

Views: 103

Answers (2)

Pepe N O
Pepe N O

Reputation: 2354

Just do something like this to fix a bad formed namespace, should be xmlns:namespace_name="namespace_uri"

select id from
xmltable(xmlnamespaces('http://example.com/sample.xsd' as _),
'//_:Id'
passing ('<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>')
columns id int path 'text()') xt;

Result

id
1
2
3
4

Fiddle to test

Upvotes: 0

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22293

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

Upvotes: 1

Related Questions