解析XML生成正常/转置表格：SQL重复数据问题解决

原代码出现32行重复结果的核心问题是：两次outer apply未设置关联条件，导致<th>下的4个标签与所有8个数据单元格形成笛卡尔积（4×8=32），进而产生错误的配对组合。以下是针对两种目标表格的修正方案：

一、生成常规列式表格 #

通过位置索引关联表头与数据单元格，再通过透视转换为标准列结构：

declare @xmldata nvarchar(4000) = '<browse result="1">
  <th>
    <td label="Company ID"></td>
    <td label="Company Name"></td>
    <td label="Country"></td>
    <td label="Region"></td>
  </th>
  <tr>
    <td>ABC01</td>
    <td>Company 1</td>
    <td>United States</td>
    <td>North America</td>
  </tr>
  <tr>
    <td>ABC02</td>
    <td>Company 2</td>
    <td>China</td>
    <td>Asia</td>
  </tr>
</browse>'

;WITH XMLData AS (
    SELECT CAST(@xmldata AS XML) AS xml_content
),
Headers AS (
    -- 提取表头标签并分配位置索引
    SELECT 
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS col_index,
        td.value('@label', 'varchar(100)') AS col_name
    FROM XMLData
    CROSS APPLY xml_content.nodes('/browse/th/td') AS T(td)
),
RowData AS (
    -- 提取每行数据，同时分配行号和列索引
    SELECT 
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS row_index,
        td.value('.', 'varchar(100)') AS cell_value,
        ROW_NUMBER() OVER(PARTITION BY tr ORDER BY (SELECT NULL)) AS col_index
    FROM XMLData
    CROSS APPLY xml_content.nodes('/browse/tr') AS TR(tr)
    CROSS APPLY tr.nodes('td') AS TD(td)
)
-- 透视转换为列式表格
SELECT 
    [Company ID], [Company Name], [Country], [Region]
FROM RowData
JOIN Headers ON RowData.col_index = Headers.col_index
PIVOT (
    MAX(cell_value)
    FOR col_name IN ([Company ID], [Company Name], [Country], [Region])
) AS PivotTable

二、生成转置后的行式表格 #

将表头作为行、每行数据作为列，通过关联位置索引后透视实现：

declare @xmldata nvarchar(4000) = '<browse result="1">
  <th>
    <td label="Company ID"></td>
    <td label="Company Name"></td>
    <td label="Country"></td>
    <td label="Region"></td>
  </th>
  <tr>
    <td>ABC01</td>
    <td>Company 1</td>
    <td>United States</td>
    <td>North America</td>
  </tr>
  <tr>
    <td>ABC02</td>
    <td>Company 2</td>
    <td>China</td>
    <td>Asia</td>
  </tr>
</browse>'

;WITH XMLData AS (
    SELECT CAST(@xmldata AS XML) AS xml_content
),
Headers AS (
    SELECT 
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS col_index,
        td.value('@label', 'varchar(100)') AS label
    FROM XMLData
    CROSS APPLY xml_content.nodes('/browse/th/td') AS T(td)
),
RowData AS (
    SELECT 
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS row_index,
        td.value('.', 'varchar(100)') AS cell_value,
        ROW_NUMBER() OVER(PARTITION BY tr ORDER BY (SELECT NULL)) AS col_index
    FROM XMLData
    CROSS APPLY xml_content.nodes('/browse/tr') AS TR(tr)
    CROSS APPLY tr.nodes('td') AS TD(td)
),
LabelValues AS (
    -- 关联表头与对应行的数据，生成统一结构
    SELECT 
        h.label,
        'value' + CAST(r.row_index AS varchar(10)) AS value_col,
        r.cell_value
    FROM Headers h
    JOIN RowData r ON h.col_index = r.col_index
)
-- 透视转换为行式表格
SELECT 
    label, [value1], [value2]
FROM LabelValues
PIVOT (
    MAX(cell_value)
    FOR value_col IN ([value1], [value2])
) AS PivotTable

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