How do I prevent SQL Server from concatenating XML results with the same name
I am trying to generate an XML document using the XML features in SQL 2008 but I'm running into an issue where the document requires two nodes to have the same name but SQL Server automatically concatenates any nodes with the same name.
For example:
<Location>
<Address>Some Street</Address>
<Address>Some other info</Address>
</Location>
The problem is when I try to do this by executing the SQL below I get:
<Location>
<Addres开发者_如何学运维s>Some StreetSome other info</Address>
</Location>
Here's how I'm selecting the data:
SELECT CustomerStreet1 AS 'location/address',
CustomerStreet2 AS 'location/address'
FROM Customers
FOR XML PATH('Customer')
While having another node selected in between the two address fields does what I'm looking for I need to be able to keep the two address nodes together.
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')
精彩评论