Cannot Read Contents When XML Has 2 Wrappers

This code works fine when you remove the wrapper, from the XML and the nodes, but when you add it, like below, i get 0 results. -- Declare XML variable DECLARE @da

Solution 1:

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..