How To Properly Use Xml Parameter For Update Procedure In Sql Server

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 / A

Solution 1:

Try updating like this.

UPDATE A
SET    title = b.title,
       summary = b.summary,
       post = b.post,
       departmentID = b.departmentID
FROM   RC_Posts A
       JOIN (SELECT title=[Xml_Tab].[Cols].value('(title)[1]', 'nvarchar(100)'),
                    summary=[Xml_Tab].[Cols].value('(summary)[1]', 'nvarchar(500)'),
                    post=[Xml_Tab].[Cols].value('(post)[1]', 'nvarchar(max)'),
                    departmentID=[Xml_Tab].[Cols].value('(departmentID)[1]', 'int'),
                    PostID=[Xml_Tab].[Cols].value('(postID)[1]', 'int')
             FROM@xmlMain.nodes('/root') AS [Xml_Tab]([Cols])) B
         ON a.postID = b.postID 

Solution 2:

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

I suggest you use an XmlDocument to build the desired XML in your code rather than string concatenation. This will properly handle entity references that will otherwise break the XML parser. For example, a title value of "Travel & Expenses" will be invalid as element text. Using XML methods will replace the ampersand with the proper entity reference.

One method to update the table with the XML element values is with the UPDATE...FROM syntax. This assumes @PostID is passed as a separate parameter.

CREATE PROC dbo.usp_UpdateRC_PostsFromXml
      @postIDint
    , @xmlMain XML
ASUPDATE  p
SET     title = [Cols].value('(title)[1]', 'nvarchar(100)'),
        summary = [Cols].value('(summary)[1]', 'nvarchar(500)'),
        post = [Cols].value('(post)[1]', 'nvarchar(max)'),
        departmentID = [Cols].value('(departmentID)[1]', 'int')
FROM RC_Posts AS p
CROSS APPLY @xmlMain.nodes('/root') AS [Xml_Tab]([Cols])
WHERE   postID =@postID;
GO