Get attribute value of XML

Question

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?

Answer 1

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:

Answer 2

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:

learn more about XML namespaces in SQL Server here:

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