TSQL Parse XML with namespace

Question

I'm trying to parse some xml that's stored in the database inside of stored procedure. The procedure should return 2 columns, id and value. I'm only part of the way through this, I'm stuck on the fact that I can't list even list the "Setting" nodes.

declare @PolicySettingsXml xml

set @PolicySettingsXml = '<?xml version="1.0" encoding="utf-8"?>
<Policy xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.myurl.com/app/Policies">
  <Setting id="VendorInfo0">
    <string>fsdfdsfds</string>
  </Setting>
  <Setting id="VendorInfo1">
    <string />
  </Setting>
  <Setting id="VendorInfo2">
    <string />
  </Setting>
  <Setting id="SendSurchargeAsSeparateTransaction">
    <boolean>false</boolean>
  </Setting>
  <Setting id="VendorSurchargeInfo0">
    <string />
  </Setting>
  <Setting id="VendorSurchargeInfo1">
    <string />
  </Setting>
  <Setting id="VendorSurchargeInfo2">
    <string />
  </Setting>
</Policy>'



select T.c.query('string') as value, T.c.query('@id') as id from @PolicySettingsXml.nodes('/Policy/Setting') T(c)

I'm getting an error, "XQuery [query()]: Attribute may not appear outside of an element," I'd expect to see:

id                                 |    value
VendorInfo0                        | fsdfdsfds
VendorInfo1                        | <null>  
VendorInfo2                        | <null>  
SendSurchargeAsSeparateTransaction | <null>
VendorSurchargeInfo1               | <null>  
VendorSurchargeInfo2               | <null>

Possible duplicate of [SQL Server Xml query with multiple namespaces](https://stackoverflow.com/questions/22818591/sql-server-xml-query-with-multiple-namespaces) — GSerg, Oct 14 '19 at 17:49
For the error message specifically, Possible duplicate of [Extracting Attributes from XML Fields in SQL Server 2008 Table](https://stackoverflow.com/q/15846990/11683) — GSerg, Oct 14 '19 at 17:53

score 0 · Answer 1 · answered Oct 14 '19 at 19:08

The first issue is related to syntax. This:

from PSXML.x.nodes('/*:Policy/*:Setting') T(c);

should be:

from (VALUES(@PolicySettingsXml)) AS PSXML(x)
CROSS APPLY PSXML.x.nodes('/*:Policy/*:Setting') T(c);

For what you are doing you can use the value method for the id column.

select
  id      = T.c.value('(@id)[1]', 'varchar(100)'),
  [value] = T.c.query('(*:string/text())[1]')
from (VALUES(@PolicySettingsXml)) AS PSXML(x)
CROSS APPLY PSXML.x.nodes('/*:Policy/*:Setting') T(c);

Note that I am cheating on the namespaces using the "all namespaces" syntax: * :Object. A google search for "sql server t-sql xml namespaces" will show you the way you're supposed to do it. I cheated due to time constraints.

TSQL Parse XML with namespace

1 Answers1