如何从 SQL Server 2008 返回由共享公共父级的多个选择构成的 XML

我尝试过使用“FOR XML PATH”、“FOR XML EXPLICIT”和“FOR XML AUTO”，但数据从未采用正确的层次结构构建。

基本上，我有一张父表（客户）和 3 个子表。每个表都有一个 customerid 列。 Customers 表与 3 个子表中的每一个都存在一对多关系。

作为一个模拟示例，我有一个父“客户”表，还有其他 3 个表 - 产品、爱好和车辆 - 所有这些都通过 customerid 与客户表相关。

实现以下结构的 SQL 代码是什么 -

<Customers>
    <Customer customerid="1" name="Fred">
       <Products>
           <Product productname="table" />
           <Product productname="chair" />
           <Product productname="wardrobe" />
       </Products>
       <Hobbies>
           <Hobby hobbyname="Golf" />
           <Hobby hobbyname="Swimming" />
       </Hobbies>
       <Vehicles>
           <Vehicle name="Car" color="Red" />
           <Vehicle name="Bicycle" color="Blue" />
       </Vehicles>
    </Customer>
    <Customer customerid="2" name="Sue">
       <Products>
           <Product productname="CD player" />
           <Product productname="Picture frame" />
       </Products>
       <Hobbies>
           <Hobby hobbyname="Dancing" />
           <Hobby hobbyname="Reading" />
       </Hobbies>
       <Vehicles>
           <Vehicle name="Car" color="Yellow" />
       </Vehicles>
    </Customer>
</Customers>

尝试这样的东西 - 它使用 FORXML PATH并子选择为给定客户创建“链接”子节点（我将其限制为两个子表 - 但您应该了解它的“要点”并能够将其扩展到任意数量的链接子表):

SELECT
    CustomerID AS '@CustomerID',
    CustName AS '@Name',

    (SELECT ProductName AS '@productname'
     FROM dbo.Products p
     WHERE p.CustomerID = c.CustomerID  
     FOR XML PATH('Product'), TYPE) AS 'Products',

    (SELECT HobbyName AS '@hobbyname'
     FROM dbo.Hobbies h 
     WHERE h.CUstomerID = c.CustomerID
     FOR XML PATH('Hobby'), TYPE) AS 'Hobbies'
FROM
    dbo.Customers c
FOR XML PATH('Customer'), ROOT('Customers')

给我一个类似的输出：

<Customers>
  <Customer CustomerID="1" Name="Fred">
    <Products>
      <Product productname="Table" />
      <Product productname="Wardrobe" />
      <Product productname="Chair" />
    </Products>
    <Hobbies>
      <Hobby hobbyname="Golf" />
      <Hobby hobbyname="Swimming" />
    </Hobbies>
  </Customer>
  <Customer CustomerID="2" Name="Sue">
    <Products>
      <Product productname="CD Player" />
      <Product productname="Picture frame" />
    </Products>
    <Hobbies>
      <Hobby hobbyname="Dancing" />
      <Hobby hobbyname="Gardening" />
      <Hobby hobbyname="Reading" />
    </Hobbies>
  </Customer>
</Customers>