중첩 FOR XML 쿼리

  • 아티클

SQL Server 2000에서는 SELECT 쿼리의 최상위 수준에서만 FOR XML 절을 지정할 수 있습니다. 결과 XML은 추가 처리를 위해 기본적으로 클라이언트에 반환됩니다. xml 데이터 형식 및 FOR XML 쿼리의 TYPE 지시어가 지원되는 SQL Server 2005에서는 FOR XML 쿼리로 반환된 XML을 서버에서 추가로 처리할 수 있습니다.

  • FOR XML 쿼리 결과를 xml 유형의 변수에 할당하거나 XQuery를 사용하여 결과를 쿼리하고 추가 처리를 위해 이 결과를 xml 유형의 변수에 할당할 수 있습니다.

    DECLARE @x xml
SET @x=(SELECT ProductModelID, Name
        FROM Production.ProductModel
        WHERE ProductModelID=122 or ProductModelID=119
        FOR XML RAW, TYPE)
SELECT @x
-- Result
--<row ProductModelID="122" Name="All-Purpose Bike Stand" />
--<row ProductModelID="119" Name="Bike Wash" />

    또한 xml 데이터 형식 메서드 중 하나를 사용하여 @x 변수에 반환된 XML을 처리할 수 있습니다. 예를 들어 value() 메서드(xml 데이터 형식)를 사용하여 ProductModelID 특성 값을 검색할 수 있습니다.

    DECLARE @i int
SET @i = (SELECT @x.value('/row[1]/@ProductModelID[1]', 'int'))
SELECT @i

    다음 예에서 FOR XML 쿼리 결과는 FOR XML 절에 TYPE 지시어가 지정되어 있기 때문에 xml 유형으로 반환됩니다.

    SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=119 or ProductModelID=122
FOR XML RAW, TYPE,ROOT('myRoot')

    다음은 결과입니다.

    <myRoot>
  <row ProductModelID="122" Name="All-Purpose Bike Stand" />
  <row ProductModelID="119" Name="Bike Wash" />
</myRoot>

    결과가 xml 유형이기 때문에 다음 쿼리와 같이 이 XML에 대해 xml 데이터 형식 메서드 중 하나를 직접 지정할 수 있습니다. 쿼리에서는 query() 메서드(xml 데이터 형식)를 사용하여 <myRoot> 요소의 첫 번째 <row> 요소 자식을 검색합니다.

    SELECT  (SELECT ProductModelID, Name
         FROM Production.ProductModel
         WHERE ProductModelID=119 or ProductModelID=122
         FOR XML RAW, TYPE,ROOT('myRoot')).query('/myRoot[1]/row[1]')

    다음은 결과입니다.

    <row ProductModelID="122" Name="All-Purpose Bike Stand" />

  • 또한 내부 쿼리 결과가 xml 유형으로 외부 쿼리에 반환되는 중첩된 FOR XML 쿼리를 작성할 수 있습니다. 예를 들면 다음과 같습니다.

    SELECT Col1, 
       Col2, 
       ( SELECT Col3, Col4 
        FROM  T2
        WHERE T2.Col = T1.Col
        ...
        FOR XML AUTO, TYPE )
FROM T1
WHERE ...
FOR XML AUTO, TYPE

    이전 쿼리에서 다음을 유의하십시오.

    • 내부 FOR XML 쿼리에서 생성된 XML이 외부 FOR XML에서 생성된 XML에 추가됩니다.
    • 내부 쿼리는 TYPE 지시어를 지정합니다. 따라서 내부 쿼리에서 반환된 XML 데이터는 xml 유형입니다. TYPE 지시어를 지정하지 않으면 내부 FOR XML 쿼리의 결과가 **nvarchar(max)**로 반환되고 XML 데이터가 올바르게 수정됩니다.

    중첩된 FOR XML 쿼리를 사용하면 결과 XML 데이터 형식을 보다 자유롭게 정의할 수 있습니다.

    • SQL Server 2000에서 RAW 및 AUTO 모드 쿼리는 기본적으로 특성 중심 XML을 생성합니다. 예를 들면 다음과 같습니다.

      SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML RAW

      다음은 특성 중심 결과입니다.

      <row ProductModelID="122" Name="All-Purpose Bike Stand" />
<row ProductModelID="119" Name="Bike Wash" />

      ELEMENTS 지시어를 지정하여 모든 XML을 요소 중심으로 검색할 수 있습니다. 예를 들면 다음과 같습니다.

      SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML RAW, ELEMENTS

      다음은 요소 중심 결과입니다.

      <row>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</row>
<row>
  <ProductModelID>119</ProductModelID>
  <Name>Bike Wash</Name>
</row>

      SQL Server 2005의 중첩된 FOR XML 쿼리의 경우 일부는 특성 중심이고 일부는 요소 중심인 XML을 생성할 수 있습니다.

    • SQL Server 2000에서는 EXPLICIT 모드로 쿼리를 작성해야만 형제를 생성할 수 있습니다. 하지만 이 방법은 복잡할 수 있습니다. SQL Server 2005에서는 중첩된 AUTO 모드 FOR XML 쿼리를 지정하여 형제를 포함하는 XML 계층을 생성할 수 있습니다.

    사용하는 모드에 관계없이 중첩된 FOR XML 쿼리를 사용하면 결과 XML 형식을 보다 자유롭게 설명할 수 있습니다. EXPLICIT 모드 쿼리 대신 이러한 쿼리를 사용할 수 있습니다.

예

1. FOR XML 쿼리와 중첩된 FOR XML 쿼리 비교

다음 SELECT 쿼리는 AdventureWorks 데이터베이스에서 제품 범주와 하위 범주 정보를 검색합니다. 쿼리에는 중첩된 FOR XML이 없습니다.

SELECT   ProductCategory.ProductCategoryID, 
         ProductCategory.Name as CategoryName,
         ProductSubCategory.ProductSubCategoryID, 
         ProductSubCategory.Name
FROM     Production.ProductCategory, Production.ProductSubCategory
WHERE    ProductCategory.ProductCategoryID = ProductSubCategory.ProductCategoryID
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE
GO

다음은 결과의 일부입니다.

<ProductCategory ProductCategoryID="1" CategoryName="Bike">
  <ProductSubCategory ProductSubCategoryID="1" Name="Mountain Bike"/>
  <ProductSubCategory ProductSubCategoryID="2" Name="Road Bike"/>
  <ProductSubCategory ProductSubCategoryID="3" Name="Touring Bike"/>
</ProductCategory>
...

쿼리에서 ELEMENTS 지시어를 지정하면 다음 결과 조각에서와 같이 요소 중심 결과를 검색합니다.

<ProductCategory>
  <ProductCategoryID>1</ProductCategoryID>
  <CategoryName>Bike</CategoryName>
  <ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <Name>Mountain Bike</Name>
  </ProductSubCategory>
  <ProductSubCategory>
     ...
  </ProductSubCategory>
</ProductCategory>

그런 후 다음 조각에서와 같이 특성 중심 및 요소 중심 XML이 조합된 XML 계층을 생성한다고 가정하십시오.

<ProductCategory ProductCategoryID="1" CategoryName="Bike">
  <ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory>
  <ProductSubCategory>
     ...
  <ProductSubCategory>
     ...
</ProductCategory>

이전 조각에서 범주 ID 및 범주 이름과 같은 제품 범주 정보는 특성입니다. 하지만 하위 범주 정보는 요소 중심입니다. <ProductCategory> 요소를 생성하기 위해 다음과 같이 FOR XML 쿼리를 작성할 수 있습니다.

SELECT ProductCategoryID, Name as CategoryName
FROM Production.ProductCategory ProdCat
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE

다음은 결과입니다.

< ProdCat ProductCategoryID="1" CategoryName="Bikes" />
< ProdCat ProductCategoryID="2" CategoryName="Components" />
< ProdCat ProductCategoryID="3" CategoryName="Clothing" />
< ProdCat ProductCategoryID="4" CategoryName="Accessories" />

그런 다음 XML에서 원하는 중첩된 <ProductSubCategory> 요소를 생성하려면 다음과 같이 중첩된 FOR XML 쿼리를 추가합니다.

SELECT ProductCategoryID, Name as CategoryName,
       (SELECT ProductSubCategoryID, Name SubCategoryName
        FROM   Production.ProductSubCategory
        WHERE ProductSubCategory.ProductCategoryID = 
              ProductCategory.ProductCategoryID
        FOR XML AUTO, TYPE, ELEMENTS
       )
FROM Production.ProductCategory
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE

이전 쿼리에서 다음을 유의하십시오.

  • 내부 FOR XML 쿼리는 제품 하위 범주 정보를 검색합니다. ELEMENTS 지시어는 외부 쿼리에 의해 생성되는 XML에 추가된 요소 중심 XML을 생성하기 위해 내부 FOR XML에 추가되어 있습니다. 기본적으로 외부 쿼리는 특성 중심 XML을 생성합니다.
  • 내부 쿼리에서 결과가 xml 유형이 되도록 TYPE 지시어를 지정합니다. TYPE을 지정하지 않으면 결과가 nvarchar(max) 유형으로 반환되고 XML 데이터는 엔터티로 반환됩니다.
  • 외부 쿼리도 TYPE 지시어를 지정합니다. 따라서 이 쿼리의 결과는 클라이언트에 xml 유형으로 반환됩니다.

다음은 결과의 일부입니다.

<ProductCategory ProductCategoryID="1" CategoryName="Bike">
  <ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory>
  <ProductSubCategory>
     ...
  <ProductSubCategory>
     ...
</ProductCategory>

다음 쿼리는 이전 쿼리를 확장한 것입니다. 여기에서는 AdventureWorks 데이터베이스의 전체 제품 계층을 보여 줍니다. 여기에는 다음이 포함됩니다.

  • 제품 범주
  • 각 범주의 제품 하위 범주
  • 각 하위 범주의 제품 모델
  • 각 모델의 제품

다음 쿼리는 AdventureWorks 데이터베이스를 이해하는 데 도움이 됩니다.

SELECT ProductCategoryID, Name as CategoryName,
       (SELECT ProductSubCategoryID, Name SubCategoryName,
               (SELECT ProductModel.ProductModelID, 
                       ProductModel.Name as ModelName,
                       (SELECT ProductID, Name as ProductName, Color
                        FROM   Production.Product
                        WHERE  Product.ProductModelID = 
                               ProductModel.ProductModelID
                        FOR XML AUTO, TYPE)
                FROM   (SELECT distinct ProductModel.ProductModelID, 
                               ProductModel.Name
                        FROM   Production.ProductModel, 
                               Production.Product
                        WHERE  ProductModel.ProductModelID = 
                               Product.ProductModelID
                        AND    Product.ProductSubCategoryID = 
                               ProductSubCategory.ProductSubCategoryID) 
                                  ProductModel
                FOR XML AUTO, type
               )
        FROM Production.ProductSubCategory
        WHERE ProductSubCategory.ProductCategoryID = 
              ProductCategory.ProductCategoryID
        FOR XML AUTO, TYPE, ELEMENTS
       )
FROM Production.ProductCategory
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE

다음은 결과의 일부입니다.

<Production.ProductCategory ProductCategoryID="1" CategoryName="Bikes">
  <Production.ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <SubCategoryName>Mountain Bikes</SubCategoryName>
    <ProductModel ProductModelID="19" ModelName="Mountain-100">
      <Production.Product ProductID="771" 
                ProductName="Mountain-100 Silver, 38" Color="Silver" />
      <Production.Product ProductID="772" 
                ProductName="Mountain-100 Silver, 42" Color="Silver" />
      <Production.Product ProductID="773" 
                ProductName="Mountain-100 Silver, 44" Color="Silver" />
        ...     </ProductModel>
     ...

제품 하위 범주를 생성하는 중첩된 FOR XML 쿼리에서 ELEMENTS 지시어를 제거하면 전체 결과가 특성 중심이 됩니다. 그런 다음 중첩 없이 이 쿼리를 작성할 수 있습니다. ELEMENTS를 추가하면 부분적으로 특성 중심이고 요소 중심이기도 한 XML이 생성됩니다. 이 결과는 단일 수준의 FOR XML 쿼리에 의해 생성될 수 없습니다.

2. 중첩된 AUTO 모드 쿼리를 사용하여 형제 생성

다음 예에서는 중첩된 AUTO 모드 쿼리를 사용하여 형제를 생성하는 방법을 보여 줍니다. 이러한 XML을 생성하는 다른 방법은 EXPLICIT 모드를 사용하는 것 뿐입니다. 하지만 이 방법은 복잡할 수 있습니다.

이 쿼리는 판매 주문 정보를 제공하는 XML을 생성합니다. 여기에는 다음이 포함됩니다.

  • 판매 주문 헤더 정보, SalesOrderID, SalesPersonID 및 OrderDate. AdventureWorks는 SalesOrderHeader 테이블에 이 정보를 저장합니다.
  • 판매 주문 세부 정보. 여기에는 주문된 하나 이상의 제품, 단가 및 주문 수량이 포함됩니다. 이 정보는 SalesOrderDetail 테이블에 저장됩니다.
  • 판매 직원 정보. 주문을 접수한 판매 직원입니다. SalesPerson 테이블은 SalesPersonID를 제공합니다. 이 쿼리에서는 판매 직원 이름을 찾기 위해 이 테이블을 Employee 테이블에 조인해야 합니다.

다음과 같은 두 개의 고유 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를 쿼리합니다. 결과는 하나 이상의 <SalesOrder> 요소가 있는 XML입니다.
  • AUTO 모드와 TYPE 지시어를 지정합니다. AUTO 모드는 쿼리 결과를 XML로 변환하고 TYPE 지시어는 결과를 xml 유형으로 반환합니다.
  • 쉼표로 구분된 두 개의 중첩된 SELECT 문을 포함합니다. 첫 번째 중첩된 SELECT는 판매 주문 정보, 헤더 및 세부 정보를 검색하고 두 번째 중첩된 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를 지정합니다. xml 데이터 형식의 query() 메서드는 XQuery를 지정하는 데 사용됩니다. 자세한 내용은 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)

3. 브라우저에서 판매 주문 정보를 검색하기 위해 ASPX 응용 프로그램 생성

다음 예에서 aspx 응용 프로그램은 저장 프로시저를 실행하고 판매 주문 정보를 XML로 반환합니다. 결과는 브라우저에 표시됩니다. 저장 프로시저의 SELECT 문은 결과 XML이 요소 중심인 것을 제외하고는 예 2와 비슷합니다.

CREATE PROC GetSalesOrderInfo AS
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, ELEMENTS)
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
GO

이 코드는 .aspx 응용 프로그램입니다. 이 코드는 저장 프로시저를 실행하고 브라우저에 XML을 반환합니다.

<%@LANGUAGE=C# Debug=true %>
<%@import Namespace="System.Xml"%>
<%@import namespace="System.Data.SqlClient" %><%
Response.Expires = -1;
Response.ContentType = "text/xml";
%>

<%
using(System.Data.SqlClient.SqlConnection c = new System.Data.SqlClient.SqlConnection("Data Source=server;Database=AdventureWorks;Integrated Security=SSPI;"))
using(System.Data.SqlClient.SqlCommand cmd = c.CreateCommand())
{
   cmd.CommandText = "GetSalesOrderInfo";
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Connection.Open();
   System.Xml.XmlReader r = cmd.ExecuteXmlReader();
   System.Xml.XmlTextWriter w = new System.Xml.XmlTextWriter(Response.Output);
   w.WriteStartElement("Root");
   r.MoveToContent();
   while(! r.EOF)
   {
      w.WriteNode(r, true);
   }
   w.WriteEndElement();
   w.Flush();
}
%>
응용 프로그램을 테스트하려면
  1. AdventureWorks 데이터베이스에 저장 프로시저를 만듭니다.
  2. .aspx 응용 프로그램을 c:\inetpub\wwwroot 디렉터리에 GetSalesOrderInfo.aspx로 저장합니다.
  3. 응용 프로그램(https://server/GetSalesOrderInfo.aspx)을 실행합니다.

4. 제품 가격이 포함된 XML 생성

다음 예에서는 Production.Product 테이블을 쿼리하여 특정 제품의 ListPrice 및 StandardCost 값을 검색합니다. 쿼리를 효과적으로 만들기 위해 두 가격이 모두 <Price> 요소에 반환되고 각 <Price> 요소에는 PriceType 특성이 포함됩니다. 다음은 XML의 예상 모양입니다.

<xsd:schema xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet2" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet2" elementFormDefault="qualified">
  <xsd:import namespace="https://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="https://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:element name="Production.Product" type="xsd:anyType" />
</xsd:schema>
<Production.Product xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2" ProductID="520">
  <Price  PriceType="ListPrice">133.34</Price>
  <Price  PriceType="StandardCost">98.77</Price>
</Production.Product>

다음은 중첩된 FOR XML 쿼리입니다.

SELECT Product.ProductID, 
          (SELECT 'ListPrice' as PriceType, 
                   CAST(CAST(ListPrice as NVARCHAR(40)) as XML) 
           FROM    Production.Product Price 
           WHERE   Price.ProductID=Product.ProductID 
           FOR XML AUTO, TYPE),
          (SELECT  'StandardCost' as PriceType, 
                   CAST(CAST(StandardCost as NVARCHAR(40)) as XML) 
           FROM    Production.Product Price 
           WHERE   Price.ProductID=Product.ProductID 
           FOR XML AUTO, TYPE)
FROM Production.Product
WHERE ProductID=520
for XML AUTO, TYPE, XMLSCHEMA

이전 쿼리에서 다음 사항을 유의하십시오.

  • 외부 SELECT 문은 ProductID 특성과 두 개의 <Price> 자식 요소가 있는 <Product> 요소를 생성합니다.
  • 두 개의 내부 SELECT 문은 각각 PriceType 특성과 제품 가격을 반환하는 XML이 포함된 두 개의 <Price> 요소를 생성합니다.
  • 외부 SELECT 문에 있는 XMLSCHEMA 지시어는 결과 XML의 모양을 기술하는 인라인 XSD 스키마를 생성합니다.

쿼리를 효과적으로 만들기 위해 FOR XML 쿼리를 작성한 다음 결과에 대해 XQuery를 작성하여 다음 쿼리에서와 같이 XML 모양을 다시 지정할 수 있습니다.

SELECT ProductID, 
 ( SELECT p2.ListPrice, p2.StandardCost
   FROM Production.Product p2 
   WHERE Product.ProductID = p2.ProductID
   FOR XML AUTO, ELEMENTS XSINIL, type ).query('
                                   for $p in /p2/*
                                   return 
                                    <Price PriceType = "{local-name($p)}">
                                     { data($p) }
                                    </Price>
                                  ')
FROM Production.Product
WHERE ProductID = 520
FOR XML AUTO, TYPE

이전 예에서는 xml 데이터 형식의 query() 메서드를 사용하여 내부 FOR XML 쿼리에 의해 반환된 XML을 쿼리하고 예상된 결과를 생성합니다.

다음은 결과입니다.

<Production.Product ProductID="520">
  <Price PriceType="ListPrice">133.3400</Price>
  <Price PriceType="StandardCost">98.7700</Price>
</Production.Product>