問題描述

我嘗試過使用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>

這篇關于如何從 SQL Server 2008 返回 XML，該 XML 結構為多個選擇共享一個公共父級的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！