SQL Server 2017 XML根元素属性取值及根元素添加问题

我来帮你搞定这两个XML处理的问题，先从你遇到的第一个核心问题说起——没法提取根元素的global_id属性值：

问题1：提取根元素global_id属性失败的原因及修复 #

你当前的查询里，CROSS APPLY V.X.nodes('/FileId/file') F([File])返回的是每个<file>节点，所以当你用F.[File].value(N'@global_id','varchar(100)')时，是在<file>节点里找global_id属性，而这个属性是属于上层的<FileId>根节点的，自然返回NULL。

这里有两种简单有效的修复方法：

方法1：直接从原始XML变量获取根属性 #

因为global_id是所有<file>节点共享的全局属性，直接从原始XML变量里提取就行，不用绕到file节点：

DECLARE @XML XML = ' <FileId global_id="1234"> <file id="12aa"><vd>3</vd> <pl_a type_a ="111" k="111" name="aaa"></pl_a> <period from="2019-04-01" to="2019-06-30"></period> <all>1</all> </file> <file id="12bb"><vd>3</vd> <pl_b type_b ="222" k="222" name="bbb"></pl_b> <period from="2019-04-01" to="2019-06-30"></period> <all>2</all> </file> </FileId>'

SELECT 
    V.X.value(N'/FileId[1]/@global_id','varchar(100)') as id_payment, -- 直接从根节点取属性
    F.[File].value('@id', 'varchar(4)') AS id, 
    ISNULL(F.[File].value('(pl_a/@type_a)[1]', 'int'), F.[File].value('(pl_b/@type_b)[1]', 'int')) AS type_a, 
    ISNULL(F.[File].value('(pl_a/@k)[1]', 'int'), F.[File].value('(pl_b/@k)[1]', 'int')) AS k, 
    ISNULL(F.[File].value('(pl_a/@name)[1]', 'varchar(4)'), F.[File].value('(pl_b/@name)[1]', 'varchar(4)')) AS [name], 
    F.[File].value('(period/@from)[1]', 'date') AS [date_from], 
    F.[File].value('(period/@to)[1]', 'date') AS [date_to], -- 修正了你原来重复的列名
    F.[File].value('(all/text())[1]', 'int') AS [all] 
FROM (VALUES (@XML)) V (X) 
CROSS APPLY V.X.nodes('/FileId/file') F([File]);

方法2：通过XML树向上导航获取根属性 #

如果你需要从<file>节点向上遍历到父节点<FileId>，可以用../语法来定位父节点：

DECLARE @XML XML = ' <FileId global_id="1234"> <file id="12aa"><vd>3</vd> <pl_a type_a ="111" k="111" name="aaa"></pl_a> <period from="2019-04-01" to="2019-06-30"></period> <all>1</all> </file> <file id="12bb"><vd>3</vd> <pl_b type_b ="222" k="222" name="bbb"></pl_b> <period from="2019-04-01" to="2019-06-30"></period> <all>2</all> </file> </FileId>'

SELECT 
    F.[File].value(N'../@global_id','varchar(100)') as id_payment, -- 向上找到父节点FileId取属性
    F.[File].value('@id', 'varchar(4)') AS id, 
    ISNULL(F.[File].value('(pl_a/@type_a)[1]', 'int'), F.[File].value('(pl_b/@type_b)[1]', 'int')) AS type_a, 
    ISNULL(F.[File].value('(pl_a/@k)[1]', 'int'), F.[File].value('(pl_b/@k)[1]', 'int')) AS k, 
    ISNULL(F.[File].value('(pl_a/@name)[1]', 'varchar(4)'), F.[File].value('(pl_b/@name)[1]', 'varchar(4)')) AS [name], 
    F.[File].value('(period/@from)[1]', 'date') AS [date_from], 
    F.[File].value('(period/@to)[1]', 'date') AS [date_to], 
    F.[File].value('(all/text())[1]', 'int') AS [all] 
FROM (VALUES (@XML)) V (X) 
CROSS APPLY V.X.nodes('/FileId/file') F([File]);

另外提个小细节：你原来的查询里把period/@to的结果也命名为date_from，导致列名重复，我已经修正为date_to，避免语法错误。

问题2：添加根元素时遇到异常的通用排查方案 #

虽然你没给出具体的异常信息，但在SQL Server中处理XML根元素常见的坑和解决方法我整理了下：

• XML片段格式错误：如果是把无根的XML片段包装成有根元素，要确保所有标签都闭合，没有语法错误，比如不能有<vd>3这种未闭合的标签。
• 使用FOR XML生成根元素：如果是通过查询结果生成带根元素的XML，用ROOT()参数即可，示例：

  -- 为查询结果添加<FileId>根元素，每个行对应<file>节点
  SELECT id, name
  FROM your_table
  FOR XML PATH('file'), ROOT('FileId')
  

• 手动拼接XML的转义问题：如果是手动拼接XML字符串，要注意特殊字符的转义，比如&要写成&，<要写成<，否则会触发XML解析异常。

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