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.
Please try to modify namespaces declaration as follows, and use it in XPath expression as a prefix in a proper locations.
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