Cannot read contents when XML has 2 wrappers

sql-server-2008, xml, xquery

Solution

The problem has nothing to do with the number of "wrappers" around your XML data. The issue is: your first sample defines an XML namespace (`xmlns="test.xsd"`) on the `<data>` node, but your query isn't respecting that.

You need to change your query to be something like this:

-- Using the query() method
;WITH XMLNAMESPACES (DEFAULT 'test.xsd')
SELECT 
    T.customer.query('id').value('.', 'INT') AS customer_id,
    T.customer.query('name').value('.', 'VARCHAR(20)') AS customer_name
FROM 
    @data.nodes('data/subdata/customer') AS T(customer);

Then you'll get results....

Without this XML namespace declaration, your query would work just fine - two wrappers or more doesn't matter at all..

Problem

This code works fine when you remove the `<data>` wrapper, from the XML and the nodes, but when you add it, like below, i get 0 results. ``` -- Declare XML variable DECLARE @data XML; -- Element-centered XML SET @data = N' <data xmlns="test.xsd"> <subdata> <customer> <id>1</id> <name>Allied Industries</name> </customer> <customer> <id>2</id> <name>Trades International</name> </customer> </subdata> </data>'; -- Using the query() method SELECT T.customer.query('id').value('.', 'INT') AS customer_id, T.customer.query('name').value('.', 'VARCHAR(20)') AS customer_name FROM @data.nodes('data/subdata/customer') AS T(customer); ``` But works fine when i do it like this: ``` -- Element-centered XML SET @data = N' <subdata> <customer> <id>1</id> <name>Allied Industries</name> </customer> <customer> <id>2</id> <name>Trades International</name> </customer> </subdata> '; -- Using the query() method SELECT T.customer.query('id').value('.', 'INT') AS customer_id, T.customer.query('name').value('.', 'VARCHAR(20)') AS customer_name FROM @data.nodes('subdata/customer') AS T(customer); ``` Does anyone know how or why I'm not getting results in the first example, when the `<data>` parent wrapper is there?

Original source