sql-serversql-server-2012sqlxmlfor-xml-path

SQL Server merges elements in XML, but should be separate elements


I have the following query:

SELECT TOP 1 'St. Kulakow 12' AS 'address',
             'P123' AS 'address'
FROM tab
FOR XML PATH ('ROOT')

It returns me merged tag 'address', but it should be separate.

I get:

<ROOT>
  <address>St. Kulakow 12P123</address>
</ROOT>

but should be:

<ROOT>
 <address>St. Kulakow 12</address>
 <address>P123</address>
</ROOT>

Is there any option to get it separate?


Solution

  • This will do:

    SELECT TOP 1 'St. Kulakow 12' AS 'address',
                 NULL,
                 'P123' AS 'address'
    --FROM tab
    FOR XML PATH ('ROOT')
    

    or

    SELECT TOP 1 'St. Kulakow 12' AS 'address',
                 '',
                 'P123' AS 'address'
    --FROM tab
    FOR XML PATH ('ROOT')
    

    The "empty element" serves as a sort of breaker for the elements.