时间戳

首页 > 解决方案 > 在 SQL Server 中将 XML 列转换为行

sql-server - 在 SQL Server 中将 XML 列转换为行

问题描述

我将表格与 XML 格式相结合以存储多值数据。像这样的样本

ID 姓名 数量
1 <multi><m>BALANCE</m><m>INTEREST</m><m>FEE</m></multi> <multi><m>1000</m><m>100</m><m>10</m></multi>
2 <multi><m>BALANCE</m><m>INTEREST</m><m>FEE</m></multi> <multi><m>2000</m><m>200</m><m>20</m></multi>
3 <multi><m>BALANCE</m><m>INTEREST</m><m>FEE</m></multi> <multi><m>3000</m><m>300</m><m>30</m></multi>
4 <multi><m>BALANCE</m><m>INTEREST</m><m>FEE</m></multi> <multi><m>4000</m><m>400</m><m>40</m></multi>
5 <multi><m>BALANCE</m><m>INTEREST</m><m>FEE</m></multi> <multi><m>5000</m><m>500</m><m>50</m></multi>

我需要将此 XML 转换为行，按 ID 重复

ID 姓名 数量
1 平衡 1000
1 兴趣 100
1 费用 10
2 平衡 2000
2 兴趣 200
2 费用 20
3 平衡 3000
3 兴趣 300
3 费用 30
4 平衡 4000
4 兴趣 400
4 费用 40
5 平衡 5000
5 兴趣 500
5 费用 50

如何在 SQL Server 中实现此查询？

我试过使用这段代码：

SELECT  
    p.ID, 
    NAME = Name.value('.', 'varchar(MAX)'), 
    AMOUNT = Amount.value('.', 'varchar(MAX)')
FROM 
    PRODUCTS p 
CROSS APPLY 
    NAME.nodes('/multi/m') AS tag (Name)
CROSS APPLY 
    AMOUNT.nodes('/multi/m') AS tag2 (Amount)

结果不是我所期望的，因为 NAME 会为每个 AMOUNT 重复

标签： sql-serverxmltsql

解决方案

您似乎想要m在 NAME 和 AMOUNT 列之间选择匹配的 XML 节点对。您可以通过结合使用sql:column()XQuery 函数和一些索引值来通过它们的子索引选择它们来做到这一点，例如：

SELECT
  p.ID,
  NAME = tag.Name.value('.', 'varchar(MAX)') ,
  AMOUNT = tag2.Amount.value('.', 'varchar(MAX)')
FROM PRODUCTS p
cross apply (values (1), (2), (3) ) L (LinkingIndex)
cross apply NAME.nodes('(/multi/m)[sql:column("LinkingIndex")]') AS tag ( Name )
cross apply AMOUNT.nodes('(/multi/m)[sql:column("LinkingIndex")]') AS tag2 ( Amount );

这会产生结果......

ID 姓名 数量
1 平衡 1000
1 兴趣 100
1 费用 10
2 平衡 2000
2 兴趣 200
2 费用 20
3 平衡 3000
3 兴趣 300
3 费用 30
4 平衡 4000
4 兴趣 400
4 费用 40
5 平衡 5000
5 兴趣 500
5 费用 50

推荐阅读