問題描述

我嘗試過使用FOR XML PATH"、FOR XML EXPLICIT"和FOR XML AUTO"，但數(shù)據(jù)的結(jié)構(gòu)從來沒有使用正確的層次結(jié)構(gòu).

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

基本上，我有一個(gè)父表(客戶)和 3 個(gè)子表.每個(gè)表都有一個(gè) customerid 列.從客戶表到 3 個(gè)子表中的每一個(gè)都存在一對(duì)多關(guān)系.

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.

作為一個(gè)模擬示例，我有一個(gè)父Customers"表，還有另外 3 個(gè)表 - Products、Hobbies 和 Vehicles - 所有表都通過 customerid 與 Customers 表相關(guān).

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.

實(shí)現(xiàn)以下結(jié)構(gòu)的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 為給定客戶創(chuàng)建鏈接"子節(jié)點(diǎn)(我將其限制為兩個(gè)子表 - 但您應(yīng)該了解它的要點(diǎn)"并能夠?qū)⑵鋽U(kuò)展到任意數(shù)量的鏈接子表):

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

給我一??個(gè)類似的輸出:

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>

這篇關(guān)于如何從 SQL Server 2008 返回 XML，該 XML 結(jié)構(gòu)為多個(gè)選擇共享一個(gè)公共父級(jí)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！