(This is a follow on to Manipulating tag names and output structure in SQL which was well answered by Yitzhak Khabinsky)
I am trying to extract data from Microsoft SQL Server 2016 in specific XML formats. I can get close with standard FOR XML statements, but do not have any experience using XQuery - which I suspect is at the heart of an elegant solution.
-- 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
Produces the following:
<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>
I would like to get more explicit enumeration of the output like the following:
<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>
I can hack it by using:
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
But I'm sure there is a more elegant way using XQuery
2条答案
按热度按时间t8e9dugd1#
No need for string_agg()
Example
Results
UPDATE: For Many columns. Since you have
string_agg()
... I can assume you've gotOPENJSON
.Example
7cwmlq892#
You can use
concat
in the XQuery to add theID
value onto it.You just need to break out the first generated XML in two levels
db<>fiddle