为SQL的输出创建动态XML标记名

Question

(这是在SQL中操作标签名称和输出结构的后续内容，由Yitzhak Khabinsky很好地回答)

我试图以特定的XML格式从Microsoft SQL Server 2016中提取数据。我可以接近标准的FOR XML语句，但没有任何使用XQuery的经验——我怀疑这是一个优雅解决方案的核心。

-- DDL and sample data population, start
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');
-- DDL and sample data population, end
select * from @tbl
;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_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>

我可以使用:

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

但我相信有一个更优雅的方式使用XQuery

Answer 1

不需要string_agg()

Select  [Answer/@name] = concat('ID','_',id)
,[Answer/value] = id
,null
,[Answer/@name] = concat('FirstName','_',id)
,[Answer/value] = FirstName
,null
,[Answer/@name] = concat('MiddleName','_',id)
,[Answer/value] = MiddleName
,null
,[Answer/@name] = concat('LastName','_',id)
,[Answer/value] = LastName
From   @tbl
For xml path('')

结果

<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="MiddleName_2" />
<Answer name="LastName_2">
<value>Polack</value>
</Answer>

更新:对于许多专栏。既然你有string_agg()…我可以假设你有OPENJSON。

Select [Answer/@name] = concat([key],'_',A.ID)
,[Answer/value] = B.value
From  @tbl A
Cross Apply  (
Select [Key]
,Value
From OpenJson(  (Select A.* For JSON Path,Without_Array_Wrapper )  ) 
) B
For xml path('')

Answer 2

您可以在XQuery中使用concat来添加ID值。

您只需要将第一个生成的XML分成两层

WITH rs(x) AS
(
SELECT *
FROM @tbl
FOR XML PATH('row'), TYPE
)
SELECT
x.query('for $x in /row
for $y in $x/*
return <Answer name="{concat(local-name($y), "_", ($x/ID/text())[1])}">
<value>{data($y)}</value>
</Answer>') AS Result 
FROM rs;

,db&lt的在小提琴