Problem with querying an XML column with namespaces in SQL

Question

I need to get data from an XML:

DECLARE @input XML= 
'<Data xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Data xmlns="http://test.com/xmlschema/Data_Schema.xsd">
    <Person defID="414123">
      <GlobalID Type="People" Code="112233" />
      <FirstName>John</FirstName>
      <LastName>Smith</LastName>
      <Email>[email protected]</Email>
      <Department>Sales</Department>
      <CountryCode>US</CountryCode>
      <CompanyName>Test Company</CompanyName>
    </Person>
  </Data>
</Data>';

I can't do anything about the XML structure, this is what I've tried already:

WITH XMLNAMESPACES('http://test.com/xmlschema/Data_Schema.xsd' AS x1)
   SELECT [Email] = PersonLevel.value('(Email)[1]', 'nvarchar(64)'), 
        [FirstName] = PersonLevel.value('(FirstName)[1]', 'nvarchar(64)'), 
        [LastName] = PersonLevel.value('(LastName)[1]', 'nvarchar(64)'), 
        [Department] = PersonLevel.value('(Department)[1]', 'nvarchar(64)'), 
        [CountryCode] = PersonLevel.value('(CountryCode)[1]', 'nvarchar(64)'), 
        [CompanyName] = PersonLevel.value('(CompanyName)[1]', 'nvarchar(64)')
 FROM @Input.nodes('/Data/x1:Data/x1:Person') AS XT1(PersonLevel);

My query results as NULL values, what am I doing wrong?

Thom A · Accepted Answer · 2019-03-26 17:02:30Z

1

You need to include the namespace in your value expression:

WITH XMLNAMESPACES ('http://test.com/xmlschema/Data_Schema.xsd' AS x1)
SELECT PersonLevel.value('(x1:Email)[1]', 'nvarchar(64)') AS [Email],
       PersonLevel.value('(x1:FirstName)[1]', 'nvarchar(64)') AS [FirstName],
       PersonLevel.value('(x1:LastName)[1]', 'nvarchar(64)') AS [LastName],
       PersonLevel.value('(x1:Department)[1]', 'nvarchar(64)') AS [Department],
       PersonLevel.value('(x1:CountryCode)[1]', 'nvarchar(64)') AS [CountryCode],
       PersonLevel.value('(x1:CompanyName)[1]', 'nvarchar(64)') AS [CompanyName]
FROM @input.nodes('/Data/x1:Data/x1:Person') AS XT1(PersonLevel);

On a different note, do you really need everything to be an nvarchar(64)? I'd suggest that 64 characters may well not be enough for an email address (I've seen some really silly ones at times when businesses have long domain names, with sub domains, and full name addresses), however, 64 unicode characters is 62 characters for a country code (which is made up of 2 ASCII characters). You probably want to reconsider your datatypes here.

edited Mar 26, 2019 at 17:02

answered Mar 26, 2019 at 16:50

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ManageThisData Over a year ago

Thank you! Just what I was llooking for. I will reconsider the datatypes, just added something there quickly

Collectives™ on Stack Overflow

Problem with querying an XML column with namespaces in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related