SQL server 2012 parsing XML with namespaces

Question

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

Jayvee · Accepted Answer · 2016-10-20 18:16:14Z

3

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)

answered Oct 20, 2016 at 18:16

Jayvee

10.9k3 gold badges35 silver badges43 bronze badges

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

Comments

David דודו Markovitz · Accepted Answer · 2016-10-20 18:23:40Z

The whole point of namespaces is to differentiate between elements that were brought together from multiple documents. It is similar to the way we qualify columns with tables' names or aliases, e.g. t1.x Vs. t2.x. So when you refer to an element you should qualify it with the right namespace.

You might also want to use outer apply instead of cross apply in case there's a missing element.

create table mytable (x xml);
insert into mytable (x) 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

            outer apply x.nodes('/msit:message/env:Body/ns0:parent') t2(field)
;

Collectives™ on Stack Overflow

SQL server 2012 parsing XML with namespaces

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related