如何用XQuery在SQL Server中生成带序号的动态XML标签名

问题背景 #

这是Yitzhak Khabinsky已解答的《Manipulating tag names and output structure in SQL》的后续问题，目标是从Microsoft SQL Server 2016中提取带行ID序号后缀的XML格式数据，希望用更简洁的XQuery替代现有的STRING_AGG实现方式。

DDL与测试数据 #

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, FirstName VARCHAR(20), MiddleName VARCHAR(20), LastName VARCHAR(20));
INSERT @tbl (FirstName, MiddleName, LastName) VALUES
('Fred', 'A.','Smith'),
('Anna', NULL,'Polack');

原有XQuery代码及输出 #

原有代码仅生成基础结构，未添加ID序号后缀：

;WITH rs(x) AS
(
    SELECT * 
    FROM @tbl
    FOR XML PATH(''), TYPE, ROOT('root')
)
SELECT
 x.query('for $x in /root/*
        return <Answer name="{local-name($x)}">
          <value>{data($x)}</value>
        </Answer>') AS Result 
FROM rs

现有输出：

<Answer name="ID">
  <value>1</value>
</Answer>
<Answer name="FirstName">
  <value>Fred</value>
</Answer>
<Answer name="MiddleName">
  <value>A.</value>
</Answer>
<Answer name="LastName">
  <value>Smith</value>
</Answer>
<Answer name="ID">
  <value>2</value>
</Answer>
<Answer name="FirstName">
  <value>Anna</value>
</Answer>
<Answer name="LastName">
  <value>Polack</value>
</Answer>

期望输出 #

需为每个Answer标签的name属性添加对应行的ID后缀：

<Answer name="ID_1">
  <value>1</value>
</Answer>
<Answer name="FirstName_1">
  <value>Fred</value>
</Answer>
<Answer name="MiddleName_1">
  <value>A.</value>
</Answer>
<Answer name="LastName_1">
  <value>Smith</value>
</Answer>
<Answer name="ID_2">
  <value>2</value>
</Answer>
<Answer name="FirstName_2">
  <value>Anna</value>
</Answer>
<Answer name="LastName_2">
  <value>Polack</value>
</Answer>

XQuery优雅解决方案 #

调整XML生成逻辑，先按行分组，再拼接列名与行ID：

;WITH rs(x) AS
(
    SELECT * 
    FROM @tbl
    FOR XML PATH('row'), TYPE, ROOT('root')
)
SELECT x.query('
    for $row in /root/row
    let $id := $row/ID/text()
    return
        for $col in $row/*
        return
            <Answer name="{local-name($col)}_{$id}">
                <value>{data($col)}</value>
            </Answer>
') AS Result
FROM rs

代码说明 #

• 使用FOR XML PATH('row')将每行数据包装为独立的row节点，实现行级区分
• XQuery中先遍历每个row节点，提取当前行的ID值
• 再遍历行内所有列节点，将列名（local-name($col)）与ID拼接为name属性值
• 保留原有的value节点结构，输出符合要求的XML

参考：已实现的STRING_AGG方法 #

select STRING_AGG('<Answer name="'+ColKey+'"><value>'+ColValue+'</value></Answer>',' ')
from
(
select  tab.* from @tbl
CROSS APPLY (
VALUES  ('FirstName_'+cast(ID as varchar), FirstName),
('LastName_'+cast(ID as varchar), LastName)
) tab(ColKey, ColValue)
)t

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