SQL Server: How to properly use xml parameter for Update procedure



I am pretty new to XML and am looking for a way to use XML from an input parameter for the below part of a stored procedure (using SQL Server 2012).

The XML is submitted via JS / Ajax and looks like this:



var xmlMain = '<root><title>' + title + '</title><summary>' + summary + '</summary><post>' + post + '</post><departmentID>' + departmentID + '</departmentID></root>';


The parameter in SQL is defined as:



@xmlMain xml


To select from the XML the following works:



SELECT [Xml_Tab].[Cols].value('(title)[1]', 'nvarchar(100)'),
[Xml_Tab].[Cols].value('(summary)[1]', 'nvarchar(500)'),
[Xml_Tab].[Cols].value('(post)[1]', 'nvarchar(max)'),
[Xml_Tab].[Cols].value('(departmentID)[1]', 'int')
FROM @xmlMain.nodes('/root') AS [Xml_Tab]([Cols])


What I can't figure out is how to apply something like this to the below:



UPDATE RC_Posts
SET title = @title,
summary = @summary,
post = @post,
departmentID = @departmentID
WHERE postID = @postID


I hope someone here can help me with this.


Many thanks in advance, Tim.


No comments:

Post a Comment