OPENXML(Transact-SQL)

다음 예는 sp_xml_preparedocument를 사용하여 XML 이미지의 내부 표현을 만듭니다. OPENXML 행 집합 공급자를 사용하는 SELECT 문은 XML 문서의 내부 표현에 대해 실행됩니다.

flag 값이 1로 설정됩니다. 1은 특성 중심 매핑을 나타냅니다. 따라서 XML 특성이 행 집합의 열에 매핑됩니다. /ROOT/Customer로 지정된 rowpattern은 처리할 <Customers> 노드를 나타냅니다.

열 이름이 XML 특성 이름과 일치하므로 선택적인 ColPattern(열 패턴)은 지정되지 않았습니다.

OPENXML 행 집합 공급자는 SELECT 문이 필수 열(이 경우에는 모든 열)을 검색하는 두 열(CustomerID와 ContactName)로 된 행 집합을 만듭니다.

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/ROOT/Customer',1)
            WITH (CustomerID  varchar(10),
                  ContactName varchar(20))

결과 집합은 다음과 같습니다.

CustomerID ContactName          
---------- -------------------- 
VINET      Paul Henriot
LILAS      Carlos Gonzlez

요소 중심 매핑을 나타내는 2로 flags를 설정하고 동일한 SELECT 문을 실행하면 <Customers> 요소에 하위 요소가 없으므로 XML 문서의 두 고객 모두에 대한 CustomerID 값과 ContactName 값이 NULL로 반환됩니다.

결과 집합은 다음과 같습니다.

CustomerID ContactName
---------- -----------
NULL       NULL
NULL       NULL

다음 쿼리는 XML 문서에서 고객 ID, 주문 날짜, 제품 ID, 수량 특성을 반환합니다. rowpattern은 <OrderDetails> 요소를 나타냅니다. ProductID와 Quantity는 <OrderDetails> 요소의 특성입니다. 그러나 OrderID, CustomerID 및 OrderDate는 부모 요소 <Orders>의 특성입니다.

선택적인 ColPattern이 지정되었습니다. 이에 따라 다음과 같은 결과가 나타납니다.

• 행 집합의 OrderID, CustomerID 및 OrderDate는 XML 문서에서 rowpattern이 나타내는 노드의 부모 특성에 매핑됩니다.

• 행 집합의 ProdID 열은 ProductID 특성에 매핑되며 행 집합의 Qty 열은 rowpattern이 나타내는 노드의 Quantity 특성에 매핑됩니다.

flags 매개 변수를 통해 요소 중심 매핑을 지정하더라도 ColPattern에 지정된 매핑이 이 매핑을 덮어씁니다.

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5" 
           OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" 
           OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM   OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
         WITH (OrderID       int         '../@OrderID',
               CustomerID  varchar(10) '../@CustomerID',
               OrderDate   datetime    '../@OrderDate',
               ProdID      int         '@ProductID',
               Qty         int         '@Quantity')

결과 집합은 다음과 같습니다.

OrderID CustomerID           OrderDate                 ProdID    Qty
------------------------------------------------------------------------
10248      VINET       1996-07-04 00:00:00.000   11      12
10248      VINET       1996-07-04 00:00:00.000   42      10
10283      LILAS       1996-08-16 00:00:00.000   72      3

다음 예의 예제 XML 문서는 <Customers>, <Orders>, <Order_0020_Details> 요소로 구성됩니다. 먼저 문서 핸들을 얻기 위해 sp_xml_preparedocument를 호출합니다. 이 문서 핸들은 OPENXML에 전달됩니다.

OPENXML 문에서 rowpattern(/ROOT/Customers)은 처리할 <Customers> 노드를 나타냅니다. WITH 절을 지정하지 않았으므로 OPENXML이 edge 테이블 형식으로 행 집합을 반환합니다.

마지막으로 SELECT 문은 edge 테이블의 모든 열을 검색합니다.

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customers CustomerID="VINET" ContactName="Paul Henriot">
   <Orders CustomerID="VINET" EmployeeID="5" OrderDate=
           "1996-07-04T00:00:00">
      <Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
      <Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
   </Orders>
</Customers>
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
           "1996-08-16T00:00:00">
      <Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
   </Orders>
</Customers>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/ROOT/Customers')
EXEC sp_xml_removedocument @idoc