Producing XML from a multi-table join in SQL Server

Question

Given a database schema with a parent table and two or more child tables. For example:

Is it possible to create a query, using the for xml statement, that outputs the following XML:

<?xml version="1.0"?>
<person>
    <name>Joe Bloggs</name>
    <age>25</age>
    <address>
        <streetAddress>123 Test Street</streetAddress>
        <town>Example Town</town>
        <postcode>A12 3BC</postcode>
    </address>
    <address>
        <streetAddress>124 Test Street</streetAddress>
        <town>Example Town</town>
        <postcode>A12 3BC</postcode>
    </address>
    <contact>
        <type>Home Phone</type>
        <value>01234 567890</value>
    </contact>
    <contact>
        <type>Work Phone</type>
        <value>01987 654321</value>
    </contact>
    <contact>
        <type>Email Address</type>
        <value>[email protected]</value>
    </contact>
</person>

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 this result in which all the combinations of contact and address are output:

<?xml version="1.0" encoding="utf-8"?>
<person>
    <name>Joe Bloggs</name>
    <age>25</age>
    <address>
        <streetAddress>123 Test Street</streetAddress>
        <town>Example Town</town>
        <postcode>A12 3BC</postcode>
        <contact>
            <type>Home Phone</type>
            <value>01234 567890</value>
        </contact>
        <contact>
            <type>Work Phone</type>
            <value>01987 654321</value>
        </contact>
        <contact>
            <type>Email Address</type>
            <value>[email protected]</value>
        </contact>
    </address>
    <address>
        <streetAddress>124 Test Street</streetAddress>
        <town>Example Town</town>
        <postcode>A32 1BC</postcode>
        <contact>
            <type>Home Phone</type>
            <value>01234 567890</value>
        </contact>
        <contact>
            <type>Work Phone</type>
            <value>01987 654321</value>
        </contact>
        <contact>
            <type>Email Address</type>
            <value>[email protected]</value>
        </contact>
    </address>
</person>

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. Is there another technique that could be used to achieve the result I'm looking for?

Answer 1

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