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

2017-06-13

次の例は、入れ子になった AUTO モードクエリを使用して兄弟を生成する方法を示しています。このような XML を生成する唯一の方法は、EXPLICIT モードを使用することです。ただし、これは面倒な場合があります。

例

このクエリは、販売注文情報を提供する XML を構築します。次に例を示します。

販売注文ヘッダー情報、 SalesOrderID、 SalesPersonID、および OrderDate。 AdventureWorks2012 は、この情報を SalesOrderHeader テーブルに格納します。
販売注文の詳細情報。これには、注文された 1 つ以上の製品、単価、注文数量が含まれます。この情報は、 SalesOrderDetail テーブルに格納されます。
営業担当者情報。これは、注文を受けた営業担当者です。 SalesPersonテーブルは、SalesPersonIDを提供します。このクエリでは、このテーブルを Employee テーブルに結合して、営業担当者の名前を見つける必要があります。

次の 2 つの異なる SELECT クエリは、図形の小さな違いを持つ XML を生成します。

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

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 mode はクエリ結果を XML に変換し、 TYPE ディレクティブは結果を xml 型として返します。
2 つの入れ子になった SELECT ステートメントをコンマで区切って含めます。 1 つ目の入れ子になった 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)

こちらもご覧ください

入れ子になった FOR XML クエリを使用する

次の方法で共有

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

例

こちらもご覧ください

その他のリソース