sql-serverxmlt-sqlsql-server-2019for-xml-path

Two elements with the same name for xml path()


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)?


Solution

  • 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;