How to prevent SQL Server from concatenating XML results with the same name

Question

I am trying to generate an XML document using the XML functions in SQL 2008, but I have a problem when the document requires two nodes to have the same name, but SQL Server automatically merges any nodes with the same name.

For instance:

<Location>
  <Address>Some Street</Address>
  <Address>Some other info</Address>
</Location>

The problem is that I am trying to do this by running SQL below:

<Location>
  <Address>Some StreetSome other info</Address>
</Location>

This is how I select the data:

 SELECT CustomerStreet1 AS 'location/address',
        CustomerStreet2 AS 'location/address'
   FROM Customers
FOR XML PATH('Customer')

If the other node selected between the two address fields does what I'm looking for, I need to be able to hold the two address nodes together.

Answer 1

WITH    customers (id, CustomerStreet1, CustomerStreet2) AS
        (
        SELECT  1, 'Some Street', 'Some other info'
        UNION ALL
        SELECT  2, 'Second Street', NULL
        )
SELECT  id,
        (
        SELECT  *
        FROM    (
                SELECT  CustomerStreet1 AS Address
                UNION ALL
                SELECT  CustomerStreet2
                ) q
        FOR XML PATH(''), TYPE
        )
FROM    customers 
FOR XML PATH('Customers')