如何在 SQL Server 中为更新过程正确使用 XML 参数 [英] How to properly use XML parameter for Update procedure in SQL Server

问题描述

我对 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 

这篇关于如何在 SQL Server 中为更新过程正确使用 XML 参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！