extracting xml value in sproc

Assuming I have the following XML:

<root><test1>Hello</test1><test2>World</test2></root>

and in my stored procedure I have declared a variable like this:

DECLARE @Stuff nvarchar(50)

If the XML is passed into the stored procedure as

@MyXml

What would be the statement to parse the incoming XML to get the value held in element test1?

I am using SQL Server 2008

Answers


You have to use the xml type instead of nvarchar type, but the conversion is easy. Then you can query like this:

DECLARE @Stuff nvarchar(MAX) = '<root><test1>Hello</test1><test2>World</test2></root>'
DECLARE @Xml xml = @Stuff
SELECT @Xml.value('(/root/test1)[1]', 'nvarchar(50)') AS Test1

For more information, see the MSDN page on xml Data Type Methods.


Need Your Help

Generic monkey runner scripts

android generics automation coordinates monkeyrunner

I have multiple test cases, for which i have respective monkeyrunner scripts.