Select in XML with namespace returning null



I receive a XML from a web service and, sometimes this XML comes with namespace, and sometimes don't. When I have the XML contained in the @myDoc variable, the select returns null. But, if a remove the "xmlns" part, the select returns the value normally.


What I am 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

No comments:

Post a Comment