sql-server - 从 SQL Server 中的多表连接生成 XML

Question

给定一个包含父表和两个或多个子表的数据库模式。例如:

是否可以使用 for xml 语句创建一个输出以下 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>joe@example.com</value>
    </contact>
</person>

我最初的尝试:

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

产生了这个结果，其中输出了联系人和地址的所有组合:

<?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>joe@example.com</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>joe@example.com</value>
        </contact>
    </address>
</person>

对 Contact 或 Address 表使用单个左联接产生了我所追求的部分结果，但在添加第二个联接后它开始出错。是否有另一种技术可用于实现我正在寻找的结果？

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