Pages

Thursday, September 30, 2010

Producing XML from a multi-table join in SQL Server

Given a database schema with a parent table and two or more child tables.
--------------------------------------------------------------------------
Is it possible to create a query, using the for xml statement, that outputs the XML:
---------------------------------------------------------------------------
My initial attempt:
---------------------------------------------------------------------------
select person.name, person.age,
address.streetAddress, address.town, address.postcode,
contact.type, contact.value
from Person as person
left join Address as address on person.PersonID = address.PersonID
left join Contact as contact on person.PersonID = contact.PersonID
where person.PersonID = 1
for xml auto, elements

Yielded result in which all the combinations of contact and address are output:

Using a single left join to either the Contact or Address table produces part of what I'm after, but after adding the second join it starts to go wrong. FYI...it works a treat! I believe the "XML PATH" help me by writing below query
--------------------------------------------------------------------------
SELECT person.name, person.age,
(
SELECT address.streetAddress, address.town, address.postcode
FROM Address as address
WHERE person.PersonID = address.PersonID
FOR XML PATH('ADDRESS'), TYPE
),
(
SELECT contact.type, contact.value
FROM Contact as contact
WHERE person.PersonID = contact.PersonID
FOR XML PATH('CONTACT'), TYPE
),
FROM Person as person
WHERE person.PersonID = 1
FOR XML AUTO, ELEMENTS
---------------------------------------------------------------------------

No comments:

Post a Comment