Ben Ben - 1 month ago 9
SQL Question

SQL server 2012 parsing XML with namespaces

I have a table containing rows of xml in the following format:

<msit:message xmlns:wsa="http://URL1" xmlns:msit="http://URL2" xmlns:env="http://URL3">
<env:Body>
<ns0:parent xmlns:ns0="http://URL4">
<ns0:child>123456789</ns0:child>
...
</ns0:parent>
</env:Body>
</msit:message>`


in a table name mytable, column name data.

I have written the following query:

;with xmlnamespaces('http://URL2' as msit,
'http://URL3' as env,
'http://URL1' as wsa,
'http://URL4' as ns0)
select
t2.field.value('child[1]','varchar(20)') as ban
from mytable
cross apply data.nodes('/message/Body/parent') t2(field)


it returns empty set, when I need to return 123456789

What am I doing wrong ?

Thank you

Answer

you may need to include the prefixes in the xpath expressions:

declare @mytable table (data xml)
insert into @mytable values
('<msit:message xmlns:wsa="http://URL1" xmlns:msit="http://URL2" xmlns:env="http://URL3">
  <env:Body>
    <ns0:parent xmlns:ns0="http://URL4">
      <ns0:child>123456789</ns0:child>
    </ns0:parent>
  </env:Body>
</msit:message>')

;with xmlnamespaces('http://URL2' as msit, 
                   'http://URL3' as env, 
                   'http://URL1' as wsa,
                   'http://URL4' as ns0)
select
t2.field.value('ns0:child[1]','varchar(20)') as ban
from @mytable
cross apply data.nodes('/msit:message/env:Body/ns0:parent') t2(field)