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

Question

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?

Answer 1

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.

Answer 2

TT. you were right :) But in below case it will not work at all:

SELECT 'Warsaw' AS 'ADR/City',
       'WKPL' AS 'ADR/State',
       'St. Kulakow 12' AS 'ADR/address',
        NULL,
       'P123' AS 'ADR/address',
       '12345' AS 'ADR/Zip'
FOR XML PATH ('ROOT')

You will get:

<ROOT>
  <ADR>
    <City>Warsaw</City>
    <State>WKPL</State>
    <address>St. Kulakow 12</address>
  </ADR>
  <ADR>
    <address>P123</address>
    <Zip>12345</Zip>
  </ADR>
</ROOT>

So we need to add alias to your solution:

SELECT 'Warsaw' AS 'ADR/City',
       'WKPL' AS 'ADR/State',
       'St. Kulakow 12' AS 'ADR/address',
        NULL AS 'ADR',
       'P123' AS 'ADR/address',
       '12345' AS 'ADR/Zip'
FOR XML PATH ('ROOT')

And then we will get:

<ROOT>
  <ADR>
    <City>Warsaw</City>
    <State>WKPL</State>
    <address>St. Kulakow 12</address>
    <address>P123</address>
    <Zip>12345</Zip>
  </ADR>
</ROOT>

Thanks for help :)

Answer 3

Try this out

Table Name: DemoAddress

Id | Address
------------
1  | aaaa
2  | bbbb
3  | cccc  

The query will be like this

select 'address'=Address from DemoAddress FOR XML PATH (''),type,root('root')

And Then we get

<root>
  <address> aaaa</address>
  <address> bbbb</address>
  <address> cccc</address>
</root>