使用XML更新SQL Server表

Question

我有一个XML变量，它看起来像这样：

<code>
<IDs>
    <ID id="1">a</ID>
    <ID id="43">d</ID>
    <ID id="3">b</ID>

</IDs>
</code>

我希望在一个存储过程中使用该语句（SQL Server），以更新一个表。

我的表格如下：

ID INT,
a INT,
b INT,
c INT,
d INT

该语句应该增加与ID相关联的字母值。

因此，它应该是这样的：

Table Row with ID = 1, update column "a" by increasing the current value by 1.
Table Row with ID = 43 - update column "d" by increasing current value by 1.
Finally Table row with ID= 3 - update column "b" by increasing value by 1.

这是目前我已经完成的内容 - （第二行需要最多帮助）：

Update MyTable
SET @letter = letterVal +1
WHERE ID IN(
SELECT x.v.value('@id','INT')
FROM @xmlIDs.nodes('/IDs/ID') x(v)
)

- jpsnow72

---

你说的是 SQL（结构化查询语言），但你真正想表达的是 SQL Server 吗？ - marc_s

这个...设计有些尴尬。你有能力在这个阶段改变数据的形状吗？ - Jeremy Holovacs

是的，SQL Server。我可以根据需要重新设计。我真正想做的就是计算每个不同字母的数量。我实际上有一场考试。考试中的每个问题都有一个ID。我想计算每个问题中回答A或B等选项的人数。我希望能够使用单个存储过程来完成这个任务，而不是为每个问题调用相同的过程。 - jpsnow72

Answer 1

您需要按照以下方式进行操作：

DECLARE @input XML = '<code>
<IDs>
    <ID id="1">a</ID>
    <ID id="43">d</ID>
    <ID id="3">b</ID>

</IDs>
</code>'

;WITH ParsedXML AS
(
SELECT
    ID = C.value('(@id)[1]', 'int'),
    ColumnName = C.value('(.)[1]', 'varchar(10)')
FROM @Input.nodes('/code/IDs/ID') AS T(C)
)
UPDATE MyTable 
SET a = CASE WHEN p.ColumnName = 'a' THEN t.a + 1 ELSE t.a END,
    b = CASE WHEN p.ColumnName = 'b' THEN t.b + 1 ELSE t.b END,
    c = CASE WHEN p.ColumnName = 'c' THEN t.c + 1 ELSE t.c END,
    d = CASE WHEN p.ColumnName = 'd' THEN t.d + 1 ELSE t.d END
FROM MyTable t
INNER JOIN ParsedXml p ON t.ID = p.ID

SELECT * FROM Mytable

这样做可以实现，但非常丑陋。主要问题是：除非采用动态SQL方法（这种方法有其自身的优缺点并且可能会变得混乱不堪），否则无法从其他地方获取列名作为值，以在UPDATE语句中使用它。如果你对动态SQL感兴趣，Erland Sommarskog的《动态SQL的诅咒和祝福》是一篇必读的文章 - 在使用动态SQL之前务必阅读该文！

- marc_s

---

谢谢，这正是我在寻找的！你觉得是否最好有4个不同的过程（每个过程更新一个不同的字母），然后在我的C#代码中循环并在那时选择正确的过程？ - jpsnow72

@JustinPeterson：如果只是这四列 - 你绝对可以使用这段代码。它不是很流畅或漂亮 - 但它能工作。如果你将要处理更多的“选择”，那么也许你需要将其拆分以单独处理。 - marc_s

Answer 2

我建议您将XML更改为类似于这样的格式：

<questions>
    <question id="1" answer="a" />
    <question id="43" answer="d" />
    <question id="3" answer="b" />
</questions>

使用这种格式，您可以编写如下查询：

with CTE as (
    select
         x.n.value('@answer', 'char(1)') as answer
    from
        @Input.nodes('//questions/question') as x(n)
)
select 
    answer,
    count(answer) as answerCount
from
    CTE
group by
    answer;

然后您可以根据需要将它们添加到表中，但是您设计的表看起来并不是非常有用。

- Jeremy Holovacs

Answer 3

一个动态 SQL 解决方案。不太优雅但完全动态的：

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
GO
CREATE TABLE #tmp ( ID INT, a INT, b INT, c INT, d INT )
GO

INSERT INTO #tmp VALUES
    ( 1, 0, NULL, NULL, NULL ),
    ( 43, NULL, NULL, NULL, 0 ),
    ( 3, NULL, 0, NULL, NULL )
GO

SELECT 'before' s, * FROM #tmp

DECLARE @xml XML

SET @xml = '<code>
  <IDs>
    <ID id="1">a</ID>
    <ID id="43">d</ID>
    <ID id="3">b</ID>
  </IDs>
</code>'


-- Run a cursor through the XML resultset to update the table
DECLARE update_cursor CURSOR FAST_FORWARD LOCAL FOR 
SELECT
    x.y.value('@id', 'INT') id,
    x.y.value('.', 'VARCHAR(50)') columnName
FROM @xml.nodes('code/IDs/ID') AS x(y)

-- Cursor variables
DECLARE @id INT, @columnName VARCHAR(50)
DECLARE @sql NVARCHAR(MAX)

OPEN update_cursor

FETCH NEXT FROM update_cursor INTO @id, @columnName
WHILE @@fetch_status = 0
BEGIN

    --SELECT @id, @columnName

    SET @sql = 'UPDATE #tmp SET ' + @columnName + ' += 1 WHERE ID = ' + CAST( @id AS VARCHAR(20) )

    EXEC(@sql)
    --SELECT @sql

    FETCH NEXT FROM update_cursor INTO @id, @columnName
END

CLOSE update_cursor
DEALLOCATE update_cursor
GO

SELECT 'after' s, * FROM #tmp

- wBob