Select in XML with namespace returning null

Question

I receive an XML file from a web service and, and sometimes this XML comes with a namespace, and sometimes it doesn't.

When I have the XML contained in the @myDoc variable, the select returns null. But, if I remove the "xmlns" part, the select returns the value normally.

What am I doing wrong?

DECLARE @myDoc xml
DECLARE @CSTAT VARCHAR(MAX);
SET @myDoc = '<infProt Id="ID311140002329206" xmlns="some_namespace">
    <cStat>100</cStat>
</infProt>'
SET @CSTAT =  @myDoc.value('(/infProt/cStat)[1]', 'VARCHAR(MAX)' )
SELECT @CSTAT

possible duplicate of Selecting values out of XML with SQL Server 2008 with namespaces — Patrick Hofman
– Patrick Hofman, Commented Dec 18, 2014 at 12:44
Patrick, I read the links, but my problem was that, sometimes the namespace came in the XML, and sometimes didn't. When I used the wildcards proposeds by Mikael, it worked! — abeppler
– abeppler, Commented Dec 18, 2014 at 13:05
@abeppler then it is not an issue of your code but a web service's one... you can work around this broken behaviour of the web service learning to handle namespaces inside your sql code but the correct solution is to fix the web service. — Paolo
– Paolo, Commented Dec 18, 2014 at 13:07
I agree with you @Paolo, but I just use the web service, I'm not the developer of it... :( — abeppler
– abeppler, Commented Dec 18, 2014 at 13:11

StuartLC · Accepted Answer · 2014-12-19 06:16:26Z

4

You need to specify the name space using WITH XMLNAMESPACES, and attribute an alias to the namespace (I've used x here). Note that you'll also need to switch the SET to a SELECT to use this:

WITH XMLNAMESPACES ('some_namespace' as x)
SELECT @CSTAT =  @myDoc.value('(/x:infProt/x:cStat)[1]', 'VARCHAR(MAX)' );

SqlFiddle Demo

Edit, Re : Dynamic with / without namespaces

You can use the namespace agnostic function local-name() to bypass namespaces:

SELECT @CSTAT =  @myDoc.value(
      '(/*[local-name()="infProt"]/*[local-name()="cStat"])[1]', 'VARCHAR(MAX)');

Updated Fiddle

The mandatory caveat when using local-name() is that this will obviously detect an element in any namespace with the same name in the path, unless you also use namespace-uri.

edited Dec 19, 2014 at 6:16

answered Dec 18, 2014 at 12:44

StuartLC

108k18 gold badges225 silver badges294 bronze badges

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

4 Comments

abeppler Over a year ago

Thanks Stuart, and when I had a xml without the namespace? Because I will have the two possibilities. Can I do it dinamically?

StuartLC Over a year ago

Updated, with fiddle.

Mikael Eriksson Over a year ago

Or a wildcard. @myDoc.value('(/*:infProt/*:cStat)[1]', 'VARCHAR(MAX)' )

abeppler Over a year ago

Thanks Mikael! It's exactly what I was looking for!

fly_ua · Accepted Answer · 2014-12-18 12:48:35Z

0

For value() method declare namespace inside select text

DECLARE @myDoc xml
DECLARE @CSTAT VARCHAR(MAX);
SET @myDoc = '<infProt Id="ID311140002329206" xmlns="some_namespace">
    <cStat>100</cStat>
</infProt>'
select  @CSTAT =  @myDoc.value('declare namespace x="some_namespace"; ( /x:infProt/x:cStat)[1]', 'VARCHAR(MAX)' )
select @CSTAT

answered Dec 18, 2014 at 12:48

fly_ua

1,0548 silver badges12 bronze badges

Collectives™ on Stack Overflow

Select in XML with namespace returning null

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