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.