I'm trying to create an XML using T-SQL (SQL Server 2019) with two elements that have the same name, i already did some research and found somewhat of a workaround but the result still isn't what I'm hoping for.
This is what I've tried so far:
select
'A' as [Test/Route/Client/value],
'',
'B' as [Test/Route/Client/value]
for xml path('test'), type
This is the workaround i've read about (using this you can create a new node with the same names):
'',
But the problem is, the result for this is this one:
<test>
<Test>
<Route>
<Client>
<value>A</value>
</Client>
</Route>
</Test>
<Test>
<Route>
<Client>
<value>B</value>
</Client>
</Route>
</Test>
</test>
and my desired result would be like this:
<test>
<Test>
<Route>
<Client>
<value>A</value>
<value>B</value>
</Client>
</Route>
</Test>
</test>
Is there any way to do this (so basically an array)?
The problem is you are using multiple node names in your alias. As a result ''
with no alias means ''
at the root level; so you are exiting any other nodes (in this case, Client
, Route
, and Test
). To use the method you use, you'd need to tell SQL Server that the blank string belongs in your Client
Node:
SELECT 'A' AS [Test/Route/Client/value],
'' AS [Test/Route/Client],
'B' AS [Test/Route/Client/value]
FOR XML PATH('test'), TYPE;
Otherwise you could build your XML using subqueries. For example:
SELECT (SELECT 'A' AS value, '',
'B' AS value
FOR XML PATH('Client'), ROOT('Route'), TYPE)
FOR XML PATH('Test'), ROOT('test'), TYPE;