0

I went through at least 5 question on SE, but I must be doing something very bad out of frustration already.

I have a stored procedure that returns an XML like this:

<pmsg:Messages>
    <pmsg:Message Info="blah" />
</pmsg:Messages>

I need to get the value of Info.

DECLARE @xmlMessage XML;

EXEC procReturnsXml @xmlMessage OUTPUT;

 -- returns 0 rows
WITH XMLNAMESPACES('pmsg' AS pmsg)
SELECT msgs.msg.value('@Info', 'nvarchar(max)')
FROM   @xmlMessage.nodes('pmsg:Messages/pmsg:Message') msgs(msg);

 -- returns NULL
WITH XMLNAMESPACES('pmsg' AS pmsg)   
SELECT @xmlMessage.value('(/pmsg:Messages/pmsg:Message/@Info)[1]', 'nvarchar(max)');

-- returns NULL
WITH XMLNAMESPACES('pmsg' AS pmsg)  
SELECT t.x.value('(/pmsg:Messages/pmsg:Message/@Info)[1]', 'nvarchar(max)') AS INFO
FROM   @xmlMessage.nodes('/*') t(x); 

Generally, I don't understand what these mean at all. Can you provide an example exactly on this case, please?

  • What is the proper URI associated with the pmsg namespace? – Martin Smith Jul 14 '16 at 12:03
  • The URI that's in there leads to 404 Error. I doubt it's of any significance in that case. – ROAL Jul 14 '16 at 12:06
  • It's totally of significance. if you do WITH XMLNAMESPACES('http://properuri' as pmsg) it will likely work. – Martin Smith Jul 14 '16 at 12:07
  • Looks like @Phil has posted it already. – Martin Smith Jul 14 '16 at 12:21
5

I believe the reason you're getting these results is because the namespace for the xml is not the same as in the select statements. I've altered your example to briefly explain each case.

declare @xmlMessage xml

set @xmlMessage ='<pmsg:Messages xmlns:pmsg="pmsg">
    <pmsg:Message Info="blah"/>
    <pmsg:Message Info="blah"/>
</pmsg:Messages>'

;WITH XMLNAMESPACES('pmsg' as pmsg)
SELECT msgs.msg.value('@Info', 'nvarchar(max)') FROM @xmlMessage.nodes('pmsg:Messages/pmsg:Message') msgs(msg); 
-- return twos rows 'blah'

The nodes() expression provides a set of two pmsg:Message nodes, and the select reads the value of @Info from each row.

;WITH XMLNAMESPACES('pmsg' as pmsg)
SELECT @xmlMessage.value('(/pmsg:Messages/pmsg:Message/@Info)[1]', 'nvarchar(max)'); 
--Returns a single rowset of one row containing 'blah'

This will only return a single row 'blah' because the [1] in the query restricts to the first item found. Actually in this form the [1] is required. SQL will throw exeception of you dont include it becuase the expression must evaluate to a single value.

;WITH XMLNAMESPACES('pmsg' as pmsg)
SELECT t.x.value('(/pmsg:Messages/pmsg:Message/@Info)[1]', 'nvarchar(max)') as INFO FROM @xmlMessage.nodes('/*') t(x);
--Returns a rowset of one row containing 'blah'

Like the previous example, the xpath query evaluates to a single item and the nodes() expresson provides a row set on the top level node:

4

Try these statements:

DECLARE @xmlMessage XML = '<pmsg:Messages xmlns:pmsg="someUrl.com">
    <pmsg:Message Info="blah"/>
</pmsg:Messages>'

;WITH XMLNAMESPACES('someUrl.com' as pmsg)
SELECT msgs.msg.value('@Info', 'nvarchar(max)') FROM @xmlMessage.nodes('pmsg:Messages/pmsg:Message') msgs(msg); -- returns 0 rows

WITH XMLNAMESPACES('someUrl.com' as pmsg)
SELECT @xmlMessage.value('(/pmsg:Messages/pmsg:Message/@Info)[1]', 'nvarchar(max)'); -- returns NULL

WITH XMLNAMESPACES('someUrl.com' as pmsg)
SELECT t.x.value('(/pmsg:Messages/pmsg:Message/@Info)[1]', 'nvarchar(max)') as INFO FROM @xmlMessage.nodes('/*') t(x); -- returns NULL

Your sample XML is not valid XML so I have corrected it, assuming the stored proc does pass back valid XML. XML Namespaces are usually URLs, so I have provided a sample one - change the URL to your URL to make it work with your code. Your second statement does not work because it references the messages element twice ie messsages/messages, where the inner element is called message. You also have some semi-colons in the wrong place. Once corrected each of these three statements brought back a result:

Results

learn more about XML namespaces in SQL Server here:

Adding Namespaces Using WITH XMLNAMESPACES
http://msdn.microsoft.com/en-us/library/ms177400.aspx

  • 1
    BTW I had fixed those issues as the code didn't run without errors until they were fixed and the question was about unexpected results rather than runtime errors. – Martin Smith Jul 14 '16 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.