When selecting fields from XML column in SQL server I get no result and no error

Question

I have a table with an XML Typed column containing an XML structure with a namespace. Table is named PersonX It contains an XML typed column named customData

The customData column contains this structure:

<personX:CustomData xmlns:personX="http://www.provider.com/PersonX">
    <MailingStreet>123 First Street, APT 123</MailingStreet>
    <MailingCity>Dallas</MailingCity>
    <MailingState>TX</MailingState>
    <MailingZip>73425</MailingZip>
    <ResidenceCity>Dallas</ResidenceCity>
    <ResidenceCounty>Dallas</ResidenceCounty>
    <ExportDate>2021-05-19T00:00:00Z</ExportDate>
    <IssueDate>2021-05-18T00:00:00Z</IssueDate>
    <ExpireDate>2022-05-23T00:00:00Z</ExpireDate>
    <SubmissionDate>2021-05-03T00:00:00Z</SubmissionDate>
</personX:CustomData>

My query:

declare @xml xml
select @xml = customData from PersonX

;with xmlnamespaces('http://www.provider.com/PersonX' as ns, DEFAULT 'http://www.provider.com/PersonX')
select Person.x.value('MailingStreet[1]','varchar(max)') as street
from @xml.nodes('personX') as Person(x)

When I run, I get a blank value for street. I think it has something to do with the colon in the root element <personX:CustomData ...

I think I've missed something simple?

Also as a bonus question, how do I just run the query on the table itself instead of selecting into a variable?

Answer 1

Please try the following solution.

Directly from the table, no temporary variables.

There is no need to define the same namespace twice.

SQL

-- DDL and sample data population, start
DECLARE @tbl table (ID INT IDENTITY PRIMARY KEY, customData XML);
INSERT INTO @tbl ( customData) VALUES
(N'<personX:CustomData xmlns:personX="http://www.provider.com/PersonX">
    <MailingStreet>123 First Street, APT 123</MailingStreet>
    <MailingCity>Dallas</MailingCity>
    <MailingState>TX</MailingState>
    <MailingZip>73425</MailingZip>
    <ResidenceCity>Dallas</ResidenceCity>
    <ResidenceCounty>Dallas</ResidenceCounty>
    <ExportDate>2021-05-19T00:00:00Z</ExportDate>
    <IssueDate>2021-05-18T00:00:00Z</IssueDate>
    <ExpireDate>2022-05-23T00:00:00Z</ExpireDate>
    <SubmissionDate>2021-05-03T00:00:00Z</SubmissionDate>
</personX:CustomData>');
-- DDL and sample data population, end

WITH XMLNAMESPACES ('http://www.provider.com/PersonX' AS personX)
SELECT ID
    , c.value('(MailingStreet/text())[1]', 'VARCHAR(50)') AS MailingStreet
    , c.value('(MailingCity/text())[1]', 'VARCHAR(50)') AS MailingCity
FROM @tbl
CROSS APPLY customData.nodes('/personX:CustomData') AS t(c);

-- if enforced by the XML Schema
WITH XMLNAMESPACES ('http://www.provider.com/PersonX' AS personX)
SELECT ID
    , c.value('(MailingStreet)[1]', 'VARCHAR(50)') AS MailingStreet
    , c.value('(MailingCity)[1]', 'VARCHAR(50)') AS MailingCity
FROM @tbl
CROSS APPLY customData.nodes('/personX:CustomData') AS t(c);

Output

+----+---------------------------+-------------+
| ID |       MailingStreet       | MailingCity |
+----+---------------------------+-------------+
|  1 | 123 First Street, APT 123 | Dallas      |
+----+---------------------------+-------------+