How to properly use XML parameter for Update proce

问题:

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

回答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 

回答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
      @postID int
    , @xmlMain XML
AS

UPDATE  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