範例：使用 EXPLICIT 模式建構同層級

假設您想要建構提供銷售訂單資訊的 XML。請注意，<SalesPerson> 與 <OrderDetail> 元素為同層級。每個 Order 都有一個 <OrderHeader> 元素、一個 <SalesPerson> 元素，以及一或多個 <OrderDetail> 元素。

<OrderHeader SalesOrderID=... OrderDate=... CustomerID=... >
  <SalesPerson SalesPersonID=... />
  <OrderDetail SalesOrderID=... LineTotal=... ProductID=... OrderQty=... />
  <OrderDetail SalesOrderID=... LineTotal=... ProductID=... OrderQty=.../>
      ...
</OrderHeader>
<OrderHeader ...</OrderHeader>

以下 EXPLICIT 模式查詢會建構此 XML。請注意，查詢會指定 <OrderHeader> 元素的 Tag 值為 1，<SalesPerson> 元素為 2，而 <OrderDetail> 元素為 3。因為 <SalesPerson> 及 <OrderDetail> 為同層級，所以查詢會指定同樣可識別 <OrderHeader> 元素的 Parent 標記值 1。

USE AdventureWorks2008R2;
GO
SELECT  1 as Tag,
        0 as Parent,
        SalesOrderID  as [OrderHeader!1!SalesOrderID],
        OrderDate     as [OrderHeader!1!OrderDate],
        CustomerID    as [OrderHeader!1!CustomerID],
        NULL          as [SalesPerson!2!SalesPersonID],
        NULL          as [OrderDetail!3!SalesOrderID],
        NULL          as [OrderDetail!3!LineTotal],
        NULL          as [OrderDetail!3!ProductID],
        NULL          as [OrderDetail!3!OrderQty]
FROM   Sales.SalesOrderHeader
WHERE     SalesOrderID=43659 or SalesOrderID=43661
UNION ALL 
SELECT 2 as Tag,
       1 as Parent,
        SalesOrderID,
        NULL,
        NULL,
        SalesPersonID,  
        NULL,         
        NULL,         
        NULL,
        NULL         
FROM   Sales.SalesOrderHeader
WHERE     SalesOrderID=43659 or SalesOrderID=43661
UNION ALL
SELECT 3 as Tag,
       1 as Parent,
        SOD.SalesOrderID,
        NULL,
        NULL,
        SalesPersonID,
        SOH.SalesOrderID,
        LineTotal,
        ProductID,
        OrderQty   
FROM    Sales.SalesOrderHeader SOH,Sales.SalesOrderDetail SOD
WHERE   SOH.SalesOrderID = SOD.SalesOrderID
AND     (SOH.SalesOrderID=43659 or SOH.SalesOrderID=43661)
ORDER BY [OrderHeader!1!SalesOrderID], [SalesPerson!2!SalesPersonID],
         [OrderDetail!3!SalesOrderID],[OrderDetail!3!LineTotal]
FOR XML EXPLICIT;

以下是部分結果：

<OrderHeader SalesOrderID="43659" OrderDate="2005-07-01T00:00:00" CustomerID="676">

<SalesPerson SalesPersonID="279" />

<OrderDetail SalesOrderID="43659" LineTotal="10.373000" ProductID="712" OrderQty="2" />

<OrderDetail SalesOrderID="43659" LineTotal="28.840400" ProductID="716" OrderQty="1" />

<OrderDetail SalesOrderID="43659" LineTotal="34.200000" ProductID="709" OrderQty="6" />

...

</OrderHeader>

<OrderHeader SalesOrderID="43661" OrderDate="2005-07-01T00:00:00" CustomerID="442">

<SalesPerson SalesPersonID="282" />

<OrderDetail SalesOrderID="43661" LineTotal="20.746000" ProductID="712" OrderQty="4" />

<OrderDetail SalesOrderID="43661" LineTotal="40.373000" ProductID="711" OrderQty="2" />

...

</OrderHeader>

請參閱

參考

使用 EXPLICIT 模式