将XML导入SQL时合并多个NCList节点的NCnumber为单列求助

原有代码的问题 #

你之前的代码用CROSS APPLY拆分每个Article下的NCList节点，会导致一个Article如果有多个NCList就生成多条重复数据，不符合将多个NCnumber合并到同一列的需求。

修改后的SQL代码（支持SQL Server 2017及以上版本） #

DECLARE @XmlFile XML
SELECT @XmlFile = BulkColumn
FROM  OPENROWSET(BULK 'C:\temp\smallfile.xml', SINGLE_BLOB) x;

INSERT INTO GATEWAY_Table (ITEMID, STOPPEDSTATUS, STOPDESCRIPTIONITEM, ECOSTATUS, ECODESCRIPTION, NCNUMBER)
SELECT
   MY_XML.Item.value('(ItemId/text())[1]', 'VARCHAR(20)'),
   MY_XML.Item.value('(StoppedList/StoppedStatus/text())[1]', 'VARCHAR(20)'),
   MY_XML.Item.value('(StoppedList/StopDescriptionItem/text())[1]', 'VARCHAR(MAX)'),
   MY_XML.Item.value('(ECOList/ECOStatus/text())[1]', 'VARCHAR(20)'),
   MY_XML.Item.value('(ECOList/ECODescription/text())[1]', 'VARCHAR(MAX)'),
   -- 合并同一个Article下所有非空的NCnumber，用逗号分隔
   STRING_AGG(XT2.NCNumber.value('(./text())[1]', 'VARCHAR(50)'), ',') AS NCNUMBER
FROM 
@XmlFile.nodes('AX_PDM_DATA/ArticleCategory/Article') AS MY_XML(Item)
CROSS APPLY
Item.nodes('NCList/NCnumber') AS XT2(NCNumber)
-- 按Article的唯一标识分组，确保每个Article只生成一行
GROUP BY 
   MY_XML.Item.value('(ItemId/text())[1]', 'VARCHAR(20)'),
   MY_XML.Item.value('(StoppedList/StoppedStatus/text())[1]', 'VARCHAR(20)'),
   MY_XML.Item.value('(StoppedList/StopDescriptionItem/text())[1]', 'VARCHAR(MAX)'),
   MY_XML.Item.value('(ECOList/ECOStatus/text())[1]', 'VARCHAR(20)'),
   MY_XML.Item.value('(ECOList/ECODescription/text())[1]', 'VARCHAR(MAX)')

低版本SQL Server兼容方案（2016及以下） #

如果你的SQL Server版本不支持STRING_AGG，可以用FOR XML PATH的方式实现字符串拼接：

DECLARE @XmlFile XML
SELECT @XmlFile = BulkColumn
FROM  OPENROWSET(BULK 'C:\temp\smallfile.xml', SINGLE_BLOB) x;

INSERT INTO GATEWAY_Table (ITEMID, STOPPEDSTATUS, STOPDESCRIPTIONITEM, ECOSTATUS, ECODESCRIPTION, NCNUMBER)
SELECT
   T.ItemId,
   T.StoppedStatus,
   T.StopDescriptionItem,
   T.ECOStatus,
   T.ECODescription,
   STUFF((
      SELECT ',' + XT2.NCNumber.value('(./text())[1]', 'VARCHAR(50)')
      FROM MY_XML.Item.nodes('NCList/NCnumber') XT2(NCNumber)
      WHERE XT2.NCNumber.exist('./text()') = 1
      FOR XML PATH(''), TYPE
   ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS NCNUMBER
FROM (
   SELECT
      MY_XML.Item.value('(ItemId/text())[1]', 'VARCHAR(20)') AS ItemId,
      MY_XML.Item.value('(StoppedList/StoppedStatus/text())[1]', 'VARCHAR(20)') AS StoppedStatus,
      MY_XML.Item.value('(StoppedList/StopDescriptionItem/text())[1]', 'VARCHAR(MAX)') AS StopDescriptionItem,
      MY_XML.Item.value('(ECOList/ECOStatus/text())[1]', 'VARCHAR(20)') AS ECOStatus,
      MY_XML.Item.value('(ECOList/ECODescription/text())[1]', 'VARCHAR(MAX)') AS ECODescription,
      MY_XML.Item
   FROM @XmlFile.nodes('AX_PDM_DATA/ArticleCategory/Article') AS MY_XML(Item)
) T

补充说明 #

• 两种方案都会自动过滤空的NCnumber值，不会生成多余的逗号
• 如果你需要用其他分隔符，替换代码里的逗号即可
• 优化后的XML取值写法用(节点/text())[1]比先query再value执行效率更高

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