예: EXPLICIT 모드를 사용하여 형제 구성

  • 아티클

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

판매 주문 정보를 제공하는 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> 1, 요소의 경우 2, 요소에 <SalesPerson> 대해 3의 <OrderDetail> 값을 지정 Tag 합니다. <SalesPerson><OrderDetail> 형제이므로 쿼리는 요소를 식별하는 동일한 태그 값 1을 지정 Parent 합니다<OrderHeader>.

USE AdventureWorks2022;
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>

참고 항목