如何从 SQL Server 2008 返回 XML，该 XML 结构为多个选择共享一个公共父级

2021-10-01 00:00:00 xml sql sql-server-2008 sql-server sqlxml

我尝试过使用FOR XML PATH"、FOR XML EXPLICIT"和FOR XML AUTO"，但数据的结构从来没有使用正确的层次结构.

I've tried using "FOR XML PATH", "FOR XML EXPLICIT" and "FOR XML AUTO" but the data is never structured with the correct heirarchy.

基本上，我有一个父表(客户)和 3 个子表.每个表都有一个 customerid 列.从客户表到 3 个子表中的每一个都存在一对多关系.

Basically, I have one parent table (Customers) and 3 child tables. Each table has a customerid column. There is a one-to-many relationship from the Customers table to each of the 3 child tables.

作为一个模拟示例，我有一个父Customers"表，还有另外 3 个表 - Products、Hobbies 和 Vehicles - 所有表都通过 customerid 与 Customers 表相关.

As a mock example, I have a parent "Customers" table, and I have 3 other tables - Products, Hobbies and Vehicles - all related to the Customers table by a customerid.

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

What is the SQL code to achieve the following kind of structure -

<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>

推荐答案

尝试类似的方法 - 它使用 FOR XML PATH 和 subselects 为给定客户创建链接"子节点(我将其限制为两个子表 - 但您应该了解它的要点"并能够将其扩展到任意数量的链接子表):

Try something like this - it uses FOR XML PATH and subselects to create the "linked" sub-nodes for a given customer (I limited this to two sub tables - but you should get the "gist" of it and be able to extend it to any number of linked subtables):

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')

给我一​​个类似的输出:

Gives me an output something like:

<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>

相关文章