如何在 SQL Server 中为更新过程正确使用 XML 参数

2022-01-17 00:00:00 xml sql-update sql sql-server stored-procedures

我对 XML 很陌生，正在寻找一种方法来从输入参数中使用 XML 用于存储过程的以下部分(使用 SQL Server 2012).XML 通过 JS/Ajax 提交，如下所示:

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>';

SQL中的参数定义为:

The parameter in SQL is defined as: 

@xmlMain xml

要从 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

推荐答案

试试这样更新.

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

相关文章