I am writing a T-SQL function to convert XML data into a JSON string. My goal is to automatically group repeated child elements with the same tag name into a JSON array, rather than creating separate key-value pairs.
The built-in FOR JSON PATH in SQL Server handles this automatically for repeated table rows, but my recursive function is based on FOR XML PATH, which doesn't handle the grouping as needed.
Example 1: multiple simple elements
<root>
<Book>Book1</Book>
<TransactionId>abc123</TransactionId>
<TransactionId>abc456</TransactionId>
<Publisher>Amazon</Publisher>
<PublisherId>1</PublisherId>
<UserId>9457</UserId>
</root>
[
{
"OrderRef": "Book1",
"TransactionId": [ "abc123", "abc456" ],
"Publisher": "Amazon",
"PublisherId": 1,
"UserId": 9457
}
]
Example 2: multiple complex elements
<root>
<Book>Book1</Book>
<TransactionId>abc123</TransactionId>
<TransactionId>abc456</TransactionId>
<Publisher>Amazon</Publisher>
<Edition>
<Name>Ed1</Name>
<Color>Red</Color>
<Price>100</Price>
</Edition>
<Edition>
<Name>Ed2</Name>
<Color>Blue</Color>
<Price>200</Price>
</Edition>
<PublisherId>1</PublisherId>
<UserId>1234</UserId>
</root>
[
{
"OrderRef": "Book1",
"TransactionId": [ "abc123", "abc456" ],
"Publisher": "Amazon",
"Edition": [
{
"Name": "Ed1",
"Color": "Red",
"Price": 100
},
{
"Name": "Ed2",
"Color": "Blue",
"Price": 200
}
],
"PublisherId": 1,
"UserId": 1234
}
]
My current function uses recursion with FOR XML PATH, but it incorrectly handles repeated elements by outputting them as separate key-value pairs.
CREATE FUNCTION [dbo].[Func_XmlToJson](@XmlData xml)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @m nvarchar(max)
SELECT @m= '[' + STUFF((SELECT theline
FROM
(SELECT ','+' {' + STUFF((SELECT ',"'+coalesce(b.c.value('local-name(.)', 'NVARCHAR(255)'),'')+'":'+
case when b.c.value('count(*)','int')=0
then '"'+isnull(replace(b.c.value('text()[1]','NVARCHAR(MAX)'),'\','\\'),'')+'"'
else dbo.Func_XmlToJson(b.c.query('.'))
end
FROM x.a.nodes('*') b(c)
FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')
,1,1,'')+'}'
FROM @XmlData.nodes('/*') x(a)
) JSON(theLine)
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')
,1,1,'')+']'
RETURN @m
END
How can I modify this T-SQL function to correctly identify and group repeated elements into JSON arrays, instead of creating duplicate keys?