• [アーティクル]

入れ子になった AUTO モードのクエリを使用した兄弟の生成

次の例では、入れ子になった AUTO モードのクエリを使用して兄弟を作成する方法を示します。他の方法では、EXPLICIT モードを使用する以外に、このような XML を生成する方法はありません。ただし、この方法は複雑になる場合があります。

例

このクエリでは、販売注文情報を提供する XML が構築されます。これには次の情報が含まれます。

  • 販売注文ヘッダー情報、SalesOrderID、SalesPersonID、および OrderDate。この情報は AdventureWorks の SalesOrderHeader テーブルに格納されています。

  • 販売注文明細情報。注文を受けた 1 つ以上の製品、単価、および受注数が含まれます。この情報は SalesOrderDetail テーブルに格納されています。

  • 販売員情報。これは注文を受けた販売員です。SalesPersonID は、SalesPerson テーブルで提供されています。このクエリでは、販売員の名前を検索するために、このテーブルと Employee テーブルを結合する必要があります。

次に示す 2 つの異なる SELECT クエリは、やや形式の異なる XML を生成します。

最初のクエリで生成される XML では、<SalesPerson> と <SalesOrderHeader> は <SalesOrder> の子の兄弟として示されます。

SELECT 
      (SELECT top 2 SalesOrderID, SalesPersonID, CustomerID,
         (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
           from Sales.SalesOrderDetail
            WHERE  SalesOrderDetail.SalesOrderID = 
                   SalesOrderHeader.SalesOrderID
            FOR XML AUTO, TYPE)
        FROM  Sales.SalesOrderHeader
        WHERE SalesOrderHeader.SalesOrderID = SalesOrder.SalesOrderID
        for xml auto, type),
        (SELECT * 
         FROM  (SELECT SalesPersonID, EmployeeID
              FROM Sales.SalesPerson, HumanResources.Employee
              WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As 
                     SalesPerson
         WHERE  SalesPerson.SalesPersonID = SalesOrder.SalesPersonID
       FOR XML AUTO, TYPE)
FROM (SELECT SalesOrderHeader.SalesOrderID, SalesOrderHeader.SalesPersonID
      FROM Sales.SalesOrderHeader, Sales.SalesPerson
      WHERE SalesOrderHeader.SalesPersonID = SalesPerson.SalesPersonID
     ) as SalesOrder
ORDER BY SalesOrder.SalesOrderID
FOR XML AUTO, TYPE

上のクエリで、最も外側の SELECT ステートメントについて説明します。

  • FROM 句で指定された行セット SalesOrder に対してクエリを実行しています。結果は、1 つ以上の <SalesOrder> 要素を含む XML になります。

  • AUTO モードと TYPE ディレクティブが指定されています。AUTO モードはクエリの結果を XML に変換し、TYPE ディレクティブは結果を xml 型で返します。

  • 2 つの SELECT ステートメントが含まれており、コンマで区切られ、入れ子構造になっています。入れ子の最初の SELECT ステートメントで、販売注文情報、ヘッダー、および明細を取得し、入れ子の 2 番目の SELECT ステートメントで販売員情報を取得しています。

    • SalesOrderID、SalesPersonID、および CustomerID 自体を取得する SELECT ステートメントには、販売注文明細情報を返す別の SELECT ... FOR XML ステートメント (AUTO モードと TYPE ディレクティブを指定) が入れ子として含まれています。

販売員情報を取得する SELECT ステートメントは、FROM 句で作成された行セット SalesPerson に対して照会を行います。FOR XML クエリが機能するためには、FROM 句で生成される匿名の行セットに名前を付ける必要があります。ここで指定されている名前は SalesPerson です。

次に、結果の一部を示します。

<SalesOrder>
  <Sales.SalesOrderHeader SalesOrderID="43659" SalesPersonID="279" CustomerID="676">
    <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="776" OrderQty="1" UnitPrice="2024.9940" />
    <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="777" OrderQty="3" UnitPrice="2024.9940" />
    <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
  </Sales.SalesOrderHeader>
  <SalesPerson SalesPersonID="279" EmployeeID="279" />
</SalesOrder>
...

次のクエリでは、同じ販売注文情報が生成されます。ただし、結果の XML では、<SalesPerson> は <SalesOrderDetail> の兄弟として示されます。

<SalesOrder>
    <SalesOrderHeader ...>
          <SalesOrderDetail .../>
          <SalesOrderDetail .../>
          ...
          <SalesPerson .../>
    </SalesOrderHeader>
    
</SalesOrder>
<SalesOrder>
  ...
</SalesOrder>

クエリは次のとおりです。

SELECT SalesOrderID, SalesPersonID, CustomerID,
             (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
              from Sales.SalesOrderDetail
              WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
              FOR XML AUTO, TYPE),
              (SELECT * 
               FROM  (SELECT SalesPersonID, EmployeeID
                    FROM Sales.SalesPerson, HumanResources.Employee
                    WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As SalesPerson
               WHERE  SalesPerson.SalesPersonID = SalesOrderHeader.SalesPersonID
         FOR XML AUTO, TYPE)
FROM Sales.SalesOrderHeader
WHERE SalesOrderID=43659 or SalesOrderID=43660
FOR XML AUTO, TYPE

結果を次に示します。

<Sales.SalesOrderHeader SalesOrderID="43659" SalesPersonID="279" CustomerID="676">
  <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="776" OrderQty="1" UnitPrice="2024.9940" />
  <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="777" OrderQty="3" UnitPrice="2024.9940" />
  <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
  <SalesPerson SalesPersonID="279" EmployeeID="279" />
</Sales.SalesOrderHeader>
<Sales.SalesOrderHeader SalesOrderID="43660" SalesPersonID="279" CustomerID="117">
  <Sales.SalesOrderDetail SalesOrderID="43660" ProductID="762" OrderQty="1" UnitPrice="419.4589" />
  <Sales.SalesOrderDetail SalesOrderID="43660" ProductID="758" OrderQty="1" UnitPrice="874.7940" />
  <SalesPerson SalesPersonID="279" EmployeeID="279" />
</Sales.SalesOrderHeader>

TYPE ディレクティブによってクエリの結果が xml 型で返されるので、各種の xml データ型メソッドを使用して結果の XML にクエリを実行できます。詳細については、「xml データ型のメソッド」を参照してください。次のクエリでは、次の点に注意してください。

  • 上記のクエリが FROM 句に追加されています。クエリの結果はテーブルで返されます。追加された XmlCol 別名に注意してください。

  • SELECT 句では、FROM 句で返される XmlCol に対して XQuery を指定しています。XQuery の指定では、xml データ型の query() メソッドを使用しています。詳細については、「query() メソッド (xml データ型)」を参照してください。

    SELECT XmlCol.query('<Root> { /* } </Root>')
FROM (
SELECT SalesOrderID, SalesPersonID, CustomerID,
             (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
              from Sales.SalesOrderDetail
              WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
              FOR XML AUTO, TYPE),
              (SELECT * 
               FROM  (SELECT SalesPersonID, EmployeeID
                    FROM Sales.SalesPerson, HumanResources.Employee
                    WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As SalesPerson
               WHERE  SalesPerson.SalesPersonID = SalesOrderHeader.SalesPersonID
         FOR XML AUTO, TYPE)
FROM Sales.SalesOrderHeader
WHERE SalesOrderID='43659' or SalesOrderID='43660'
FOR XML AUTO, TYPE ) as T(XmlCol)