SQL Server中XML插入SQL表异常问题求助

我来帮你搞定这个问题！从你提供的XML和SQL代码来看，问题出在你只从SessionDataIds节点里提取了固定位置的单个值，而且只做了一次节点解析，所以只能得到一行数据。下面我来详细分析并给出可行的解决方案：

问题根源 #

你的现有查询里，T.C.value('(SelectedIds/SelectedId/text())[1]', 'int')只取了第一个SelectedId的值，(VendorId/VendorIds/text())[2]也只取了第二个VendorIds的值，再加上nodes('//SessionDataIds')只返回一个SessionDataIds节点，最终只能插入一行数据，这就是你遇到的问题。

要正确插入所有对应的数据，我们需要把SelectedId和VendorIds按它们的顺序一一匹配（两者数量都是6个，顺序是对应的）。

方案一：用CTE生成行号关联节点 #

这个方法逻辑清晰，先分别解析两个节点集合，给每个节点分配行号，再通过行号匹配对应的数据：

WITH SelectedIds AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum,
        IdNode.value('.', 'int') AS sessionDataId
    FROM @sessionDataIdXmlInner.nodes('//SessionDataIds/SelectedIds/SelectedId') AS Selected(IdNode)
),
VendorIds AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum,
        VendorNode.value('.', 'int') AS vendorId
    FROM @sessionDataIdXmlInner.nodes('//SessionDataIds/VendorId/VendorIds') AS Vendor(VendorNode)
)
INSERT INTO #sessionData_CTE (sessionDataId, vendorId)
SELECT si.sessionDataId, vi.vendorId
FROM SelectedIds si
INNER JOIN VendorIds vi ON si.RowNum = vi.RowNum;

方案二：用XML位置索引直接匹配 #

如果你不想用CTE，也可以先生成和节点数量一致的行号，然后用行号作为XML节点的位置索引，一次性提取对应的值：

INSERT INTO #sessionData_CTE (sessionDataId, vendorId)
SELECT 
    SessionData.value('(SelectedIds/SelectedId[sql:column("RowNum")]/text())[1]', 'int') AS sessionDataId,
    SessionData.value('(VendorId/VendorIds[sql:column("RowNum")]/text())[1]', 'int') AS vendorId
FROM (
    -- 生成与XML中SelectedId数量匹配的行号
    SELECT TOP (SELECT COUNT(*) FROM @sessionDataIdXmlInner.nodes('//SelectedId') AS T(C))
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
    FROM sys.all_columns
) AS RowNumbers
CROSS JOIN @sessionDataIdXmlInner.nodes('//SessionDataIds') AS Session(SessionData);

效果说明 #

这两种方法都能把XML里的6组对应数据完整插入到#sessionData_CTE临时表中，你可以根据自己的习惯选择其中一种。

内容的提问来源于stack exchange，提问作者Prasanna