sql server parse xml with namespace

Question

I'm trying to parse the following xml to get the first tag following the s:Body tag (in this case I'm looking for the string queryEE, in other messsages with the same Envelope/Body structure it will be different)

I began playing with it with something like this:

declare @text varchar(max)

set @text = N'
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
  <s:Body xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <queryEE xmlns="http://xx.gob.gcaba.xx/">
      <codeEE xmlns="">xxxx</codeEE>
    </queryEE>
  </s:Body>
</s:Envelope>'

declare @x xml

set @x = cast(@text as xml)

select @x.query('/s:Envelope')

But I get the error:

Msg 2229, Level 16, State 1, Line 16
XQuery [query()]: The name "s" does not denote a namespace.

Seems like I'm having troubles with the namespace stuff

When I try with select @x.query('/Envelope') I don't get any results at all

Thanks to the answers I got from @shnugo I could finally solve it with:

select @x.value('local-name((/*:Envelope/*:Body/*)[1])','nvarchar(100)')

Gottfried Lesigang · Accepted Answer · 2019-09-26 09:20:48Z

3

Try it like this:

--your declaration

declare @text varchar(max)

set @text = N'
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
  <s:Body xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <queryEE xmlns="http://xx.gob.gcaba.xx/">
      <codeEE xmlns="">xxxx</codeEE>
    </queryEE>
  </s:Body>
</s:Envelope>'

declare @x xml

set @x = cast(@text as xml);

--The query

WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS s
                  ,'http://xx.gob.gcaba.xx/' AS innerDflt) 
select @x.value('(/s:Envelope/s:Body/innerDflt:queryEE/codeEE/text())[1]','nvarchar(100)');

Some background:

Your XML is a bit weird looking at the namespaces... if the construction is under your control, it would be worth to start here.

There is a namespace s: to define <Envelope> and <Body>. That is fine so far., But then the element <queryEE> defines a default namespace (no prefix!) and the embedded <codeEE> defines another (but empty!) default namespace. I'm pretty sure, that this empty namespaces is created within a query by combining XMLs together...

The default namespace tells the engine, that all nodes without a specific prefix are living within this namespace. So we have to address that.

My code is using WITH XMLNAMESPACES to declare all namespaces occuring in the XML. Different to the original XML I define a prefix (innerDflt) for the first defualt namespace. That means, we can address <innerDflt:queryEE>. The embedded element does not need a namespace. It is living within an empty default (=> no) namespace.

All this said, I just want to point out, that you can use a wildcard too:

select @x.value('(/*:Envelope/*:Body/*:queryEE/*:codeEE/text())[1]','nvarchar(100)')

And you might even use a deep search

select @x.value('(//*:codeEE/text())[1]','nvarchar(100)')

But the general advise is: Be as specific as possible.

answered Sep 26, 2019 at 9:20

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

4 Comments

opensas Over a year ago

Thanks a lot, that' s a great start, but how can I get the node name of the first node after Body? in this case it would be codeEE, I tried with select @x.query('(/*:Envelope/*:Body/*[1]/local-name(.))') but I get a XQuery [query()]: The XQuery syntax '/function()' is not supported. error

Gottfried Lesigang Over a year ago

@opensas Try local-name(your/xpath/here) and use. value() rather than. query()

opensas Over a year ago

thanks a lot for the tip, I could solve it like this: select T.c.value('local-name(.)', 'nvarchar(100)') FROM @x.nodes('/*:Envelope/*:Body/*[1]') as T(c) but I could not do somethng like select @x.value('local-name(/*:Envelope/*:Body/*[1])','nvarchar(100)'), it gives me

XQuery [value()]: 'local-name()' requires a singleton (or empty sequence), found operand of type 'element(*,xdt:untyped) *'

Gottfried Lesigang Over a year ago

@opensas use local-name((your/xpath/here)[1])

Ed Bangga · Accepted Answer · 2019-09-26 08:21:00Z

1

Declare your namespace again when using xquery for xml with namespaces.

declare @text varchar(max)

set @text = N'
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
  <s:Body xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <queryEE xmlns="http://xx.gob.gcaba.xx/">
      <codeEE xmlns="">xxxx</codeEE>
    </queryEE>
  </s:Body>
</s:Envelope>'

declare @x xml

set @x = cast(@text as xml)

select @x.query('declare default element namespace "http://schemas.xmlsoap.org/soap/envelope/";   
    /Envelope')

answered Sep 26, 2019 at 8:21

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Collectives™ on Stack Overflow

sql server parse xml with namespace

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related