CODEWITHSUNDEEP
xmlsql-server-2008stored-proceduresxquery
Retrocoder
Retrocoder

Reputation: 4713

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

Upvotes: 0

Views: 113

Answers (1)

Joseph Sturtevant
Joseph Sturtevant

Reputation: 13360

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.

Upvotes: 2

Related Questions