다음을 통해 공유

예제: OPENXML 사용

  • 아티클

적용 대상: SQL Server Azure SQL Database Azure SQL Managed Instance

이 문서의 예제는 XML 문서의 행 집합 뷰를 만들 때 OPENXML을 사용하는 방법을 설명합니다. OPENXML 구문에 대한 자세한 내용은 OPENXML(Transact-SQL)을 참조하세요. 다음 예제는 OPENXML의 메타 속성 지정을 제외한 OPENXML의 모든 측면을 보여 줍니다. OPENXML에서 메타 속성을 지정하는 방법은 OPENXML에 메타 속성 지정을 참조하세요.

예제

데이터를 검색할 때 rowpattern 은 행을 결정하는 XML 문서의 노드를 식별하는 데 사용됩니다. 또한, rowpattern은 MSXML XPath 구현에 사용되는 XPath 패턴 언어로 표현됩니다. 예를 들어 패턴이 요소나 특성으로 끝나는 경우에는 rowpattern에 의해 지정된 각 요소 또는 특성 노드에 대해 한 개의 행이 생성됩니다.

flags 값은 기본 매핑을 제공합니다. SchemaDeclaration에서 ColPattern이 지정되지 않은 경우 flags에서 지정된 매핑을 가정합니다. SchemaDeclaration에서 ColPattern이 지정된 경우 flags 값은 무시됩니다. 지정된 ColPattern 은 매핑(특성 중심 또는 요소 중심)은 물론, 오버플로와 소비되지 않은 데이터를 처리할 때의 동작도 결정합니다.

A. OPENXML을 사용하여 SELECT 문 실행

이 예제의 XML 문서는 <Customer>, <Order> 및 <OrderDetail> 요소로 이루어져 있습니다. OPENXML 문은 XML 문서에서 CustomerID 및 ContactName이라는 2열 행 집합의 고객 정보를 검색합니다.

먼저 sp_xml_preparedocument 저장 프로시저가 문서 핸들을 얻기 위해 호출됩니다. 이 문서 핸들은 OPENXML에 전달됩니다.

OPENXML 문은 다음을 보여 줍니다.

  • rowpattern(/ROOT/Customer)은 처리할 <Customer> 노드를 식별합니다.

  • flags 매개 변수 값은 1 로 설정되어 특성 중심의 매핑을 나타냅니다. 결과적으로 XML 특성은 SchemaDeclaration에 정의된 행 집합의 열에 매핑됩니다.

  • WITH 절에 있는 SchemaDeclaration에서는 지정된 ColName 값은 해당하는 XML 특성 이름과 일치합니다. 따라서 ColPattern 매개 변수는 SchemaDeclaration에 지정되지 않습니다.

그런 다음 SELECT 문은 OPENXML에서 제공하는 행 집합의 모든 열을 검색합니다.

DECLARE @DocHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<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 Gonzalez">
   <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 @DocHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@DocHandle, '/ROOT/Customer',1)
      WITH (CustomerID  varchar(10),
            ContactName varchar(20));
EXEC sp_xml_removedocument @DocHandle;

다음은 결과입니다.

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

요소에는 <Customer> 요소는 하위 요소가 없기 때문에 요소 중심 매핑을 나타내기 위해 flags가 2로 설정되어 동일한 SELECT 문을 실행하면 두 고객의 CustomerID 및 ContactName 값은 NULL로 반환됩니다.

@xmlDocument xml은 xml 형식 또는 (n)varchar(max) 형식일 수 있습니다.

XML 문서의 <CustomerID> 및 <ContactName>가 하위 요소인 경우 요소 중심 매핑은 값을 검색합니다.

DECLARE @XmlDocumentHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer>
   <CustomerID>VINET</CustomerID>
   <ContactName>Paul Henriot</ContactName>
   <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</CustomerID>
   <ContactName>Carlos Gonzalez</ContactName>
   <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 @XmlDocumentHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT    *
FROM      OPENXML (@XmlDocumentHandle, '/ROOT/Customer',2)
           WITH (CustomerID  varchar(10),
                 ContactName varchar(20));
EXEC sp_xml_removedocument @XmlDocumentHandle;

다음은 결과입니다.

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

sp_xml_preparedocument에서 반환된 문서 핸들은 세션이 아닌 일괄 처리 중에 유효합니다.

B. 행 집합 열과 XML 특성 및 요소 간 매핑을 위해 ColPattern 지정

이 예제는 행 집합 열과 XML 특성 및 요소 간 매핑을 제공하기 위해 선택적 ColPattern 매개 변수에 XPath 패턴을 지정하는 방법을 보여 줍니다.

이 예제의 XML 문서는 <Customer>, <Order> 및 <OrderDetail> 요소로 이루어져 있습니다. OPENXML 문은 XML 문서에서 고객 및 주문 정보를 행 집합(CustomerID, OrderDate, ProdID 및 Qty)으로 검색합니다.

먼저 sp_xml_preparedocument 저장 프로시저가 문서 핸들을 얻기 위해 호출됩니다. 이 문서 핸들은 OPENXML에 전달됩니다.

OPENXML 문은 다음을 보여 줍니다.

  • rowpattern (/ROOT/Customer/Order/OrderDetail)은 처리할 <OrderDetail> 노드를 식별합니다.

flags 매개 변수 값은 2 로 설정되어 요소 중심의 매핑을 나타냅니다. 하지만, ColPattern에 지정된 매핑은 이 매핑을 덮어씁니다. 즉, ColPattern에 지정된 XPath 패턴은 행 집합의 열을 특성에 매핑합니다. 그러면 특성 중심 매핑이 됩니다.

WITH 절에 있는 SchemaDeclaration에서는 ColPattern 도 ColName 및 ColType 매개 변수로 지정됩니다. 선택적 ColPattern은 지정된 XPath 패턴이며 다음을 나타냅니다.

  • 행 집합의 OrderID, CustomerID 및 OrderDate 열은 rowpattern에 의해 식별되는 노드의 부모 노드 특성으로 매핑되고 rowpattern은 <OrderDetail> 노드를 식별합니다. 따라서 CustomerID 및 OrderDate 열은 <Order> 요소의 CustomerID 및 OrderDate 특성에 매핑됩니다.

  • 행 집합의 ProdID 및 Qty 열은 rowpattern에서 식별된 노드의 ProductID 및 Quantity 특성에 매핑됩니다.

그런 다음 SELECT 문은 OPENXML에서 제공하는 행 집합의 모든 열을 검색합니다.

DECLARE @XmlDocumentHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<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 Gonzalez">
   <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 @XmlDocumentHandle OUTPUT, @XmlDocument;
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID     int         '../@OrderID',
      CustomerID  varchar(10) '../@CustomerID',
      OrderDate   datetime    '../@OrderDate',
      ProdID      int         '@ProductID',
      Qty         int         '@Quantity');
EXEC sp_xml_removedocument @XmlDocumentHandle;

다음은 결과입니다.

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

ColPattern으로 지정된 XPath 패턴은 XML 요소를 행 집합 열에 매핑하여 지정할 수도 있습니다. 그러면 요소 중심 매핑이 됩니다. 다음 예제에서는 XML 문서 <CustomerID> 및 <OrderDate>는 <Orders> 요소의 하위 요소입니다. ColPattern은 flags 매개 변수에 지정된 매핑을 덮어쓰기 때문에 flages 매개 변수는 OPENXML에서 지정되지 않습니다.

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

C. 요소 중심 매핑과 특성 중심 매핑 결합

이 예제에서 flags 매개 변수는 3으로 설정되며 이는 특성 중심 및 요소 중심 매핑이 모두 적용됨을 나타냅니다. 이 경우, 특성 중심의 매핑이 먼저 적용된 다음 아직 처리되지 않은 모든 열에 대해 요소 중심의 매핑이 적용됩니다.

DECLARE @docHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument =N'<ROOT>
<Customer CustomerID="VINET"  >
     <ContactName>Paul Henriot</ContactName>
   <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 Gonzalez</ContactName>
   <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 @docHandle OUTPUT, @XmlDocument;

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer',3)
      WITH (CustomerID  varchar(10),
            ContactName varchar(20));
EXEC sp_xml_removedocument @docHandle;

다음은 결과입니다.

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

CustomerID에 특성 중심 매핑이 적용됩니다. <Customer> 요소에는 ContactName 특성이 없습니다. 따라서 요소 중심 매핑이 적용됩니다.

D. text() XPath 함수를 ColPattern으로 지정

이 예제의 XML 문서는 <Customer> 및 <Order> 요소로 이루어져 있습니다. OPENXML 문은 <Order> 요소의 oid 특성, rowpattern으로 식별된 노드에 대한 부모 노드의 ID 및 요소 콘텐츠의 리프 값 문자열로 구성된 행 집합을 검색합니다.

먼저 sp_xml_preparedocument 저장 프로시저가 문서 핸들을 얻기 위해 호출됩니다. 이 문서 핸들은 OPENXML에 전달됩니다.

OPENXML 문은 다음을 보여 줍니다.

  • rowpattern(/root/Customer/Order)은 처리할 <Order> 노드를 식별합니다.

  • flags 매개 변수 값은 1 로 설정되어 특성 중심의 매핑을 나타냅니다. 결과적으로 XML 특성은 SchemaDeclaration에 정의된 행 집합 열에 매핑됩니다.

  • WITH 절에 있는 SchemaDeclaration에서는 oid 및 amount 행 집합 열 이름은 해당하는 XML 특성 이름과 일치합니다. 따라서 ColPattern 매개 변수는 지정되지 않습니다. 행 집합의 comment 열에 대해서는 XPath 함수 text()가 ColPattern으로 지정됩니다. 이것은 flags에 지정된 특성 중심의 매핑을 덮어쓰며 열에는 요소 내용의 리프 값 문자열이 포함됩니다.

그런 다음 SELECT 문은 OPENXML에서 제공하는 행 집합의 모든 열을 검색합니다.

DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
--sample XML document
SET @xmlDocument =N'<root>
  <Customer cid= "C1" name="Janine" city="Issaquah">
      <Order oid="O1" date="1/20/1996" amount="3.5" />
      <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
      </Order>
   </Customer>
   <Customer cid="C2" name="Ursula" city="Oelde" >
      <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
             white red">
            <Urgency>Important</Urgency>
            Happy Customer.
      </Order>
      <Order oid="O4" date="1/20/1996" amount="10000"/>
   </Customer>
</root>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)
     WITH (oid     char(5),
           amount  float,
           comment ntext 'text()');
EXEC sp_xml_removedocument @docHandle;

다음은 결과입니다.

oid   amount        comment
----- -----------   -----------------------------
O1    3.5           NULL
O2    13.4          Customer was very satisfied
O3    100.0         Happy Customer.
O4    10000.0       NULL

E. WITH 절에서 TableName 지정

이 예에서는 WITH 절에서 SchemaDeclaration 대신 TableName을 지정합니다. 이는 원하는 구조가 있는 테이블이 있고 열 패턴인 ColPattern 매개 변수가 필요하지 않은 경우에 유용합니다.

이 예제의 XML 문서는 <Customer> 및 <Order> 요소로 이루어져 있습니다. OPENXML 문은 XML 문서에서 3열의 행 집합(oid, date 및 amount)에서 주문 정보를 검색합니다.

먼저 sp_xml_preparedocument 저장 프로시저가 문서 핸들을 얻기 위해 호출됩니다. 이 문서 핸들은 OPENXML에 전달됩니다.

OPENXML 문은 다음을 보여 줍니다.

  • rowpattern(/root/Customer/Order)은 처리할 <Order> 노드를 식별합니다.

  • WITH 절에는 SchemaDeclaration 이 없습니다. 대신 테이블 이름이 지정됩니다. 따라서 테이블 스키마는 행 집합 스키마로 사용됩니다.

  • flags 매개 변수 값은 1 로 설정되어 특성 중심의 매핑을 나타냅니다. 따라서 rowpattern으로 식별되는 요소의 특성은 이름이 같은 행 집합 열에 매핑됩니다.

그런 다음 SELECT 문은 OPENXML에서 제공하는 행 집합의 모든 열을 검색합니다.

-- Create a test table. This table schema is used by OPENXML as the
-- rowset schema.
CREATE TABLE T1(oid char(5), date datetime, amount float);
GO
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
-- Sample XML document
SET @xmlDocument =N'<root>
  <Customer cid= "C1" name="Janine" city="Issaquah">
      <Order oid="O1" date="1/20/1996" amount="3.5" />
      <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very
             satisfied</Order>
   </Customer>
   <Customer cid="C2" name="Ursula" city="Oelde" >
      <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
             white red">
          <Urgency>Important</Urgency>
      </Order>
      <Order oid="O4" date="1/20/1996" amount="10000"/>
   </Customer>
</root>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)
     WITH T1;
EXEC sp_xml_removedocument @docHandle;

다음은 결과입니다.

oid   date                        amount
----- --------------------------- ----------
O1    1996-01-20 00:00:00.000     3.5
O2    1997-04-30 00:00:00.000     13.4
O3    1999-07-14 00:00:00.000     100.0
O4    1996-01-20 00:00:00.000     10000.0

F. edge 테이블 형식으로 결과 얻기

이 예제에서는 OPENXML 문에서 WITH 절이 지정되지 않습니다. 따라서 OPENXML에서 생성된 행 집합에는 Edge 테이블 형식이 있습니다. SELECT 문은 Edge 테이블의 열을 모두 반환합니다.

이 XML 문서 예시는 <Customer>, <Order> 및 <OrderDetail> 요소로 이루어져 있습니다.

먼저 sp_xml_preparedocument 저장 프로시저가 문서 핸들을 얻기 위해 호출됩니다. 이 문서 핸들은 OPENXML에 전달됩니다.

OPENXML 문은 다음을 보여 줍니다.

  • rowpattern(/ROOT/Customer)은 처리할 <Customer> 노드를 식별합니다.

  • WITH 절이 제공되지 않습니다. 따라서 OPENXML은 Edge 테이블 형식으로 행 집합을 반환합니다.

그런 다음 SELECT 문은 Edge 테이블의 모든 열을 검색합니다.

DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
SET @xmlDocument = N'<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 Gonzalez">
   <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 @docHandle OUTPUT, @xmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer');

EXEC sp_xml_removedocument @docHandle;

결과는 Edge 테이블로 반환됩니다. Edge 테이블에 대한 쿼리를 작성하여 정보를 얻을 수 있습니다. 예시:

  • 다음은 문서의 Customer 노드 수를 반환하는 쿼리입니다. WITH 절이 지정되지 않았으므로 OPENXML은 Edge 테이블을 반환합니다. SELECT 문은 Edge 테이블을 쿼리합니다.

    SELECT count(*)
FROM OPENXML(@docHandle, '/')
WHERE localname = 'Customer';

  • 다음 쿼리는 요소 형식의 XML 노드의 로컬 이름을 반환합니다.

    SELECT distinct localname
FROM OPENXML(@docHandle, '/')
WHERE nodetype = 1
ORDER BY localname;

G. 특성으로 끝나는 rowpattern 지정

이 예제의 XML 문서는 <Customer>, <Order> 및 <OrderDetail> 요소로 이루어져 있습니다. OPENXML 문은 XML 문서에서 3열의 행 집합(ProductID, Quantity 및 OrderID)의 주문 세부 정보에 대한 정보를 검색합니다.

먼저 문서 핸들을 얻기 위해 sp_xml_preparedocument를 호출합니다. 이 문서 핸들은 OPENXML에 전달됩니다.

OPENXML 문은 다음을 보여 줍니다.

  • rowpattern(/ROOT/Customer/Order/OrderDetail/@ProductID)은 XML 특성 ProductID로 끝납니다. 결과 행 집합에서 XML 문서에서 선택한 각 특성 노드에 대해 행이 생성됩니다.

  • 이 예제에서는 flags 매개 변수가 지정되지 않습니다. 그 대신 ColPattern 매개 변수에 의해 매핑이 지정됩니다.

WITH 절에 있는 SchemaDeclaration에서는 ColPattern도 ColName 및 ColType 매개 변수로 지정됩니다. 선택적 ColPattern 은 다음을 나타내기 위해 지정된 XPath 패턴입니다.

  • 행 집합의 ProdID 열에 대해 ColPattern으로 지정된 XPath 패턴(.)은 현재의 노드인 컨텍스트 노드를 식별합니다. 지정된 rowpattern에 따라 <OrderDetail> 요소의 ProductID 특성입니다.

  • 행 집합에 있는 Qty 열에 대해 지정된 ColPattern, ../@Quantity는 컨텍스트 노드 <OrderDetail>ProductID<의 노드인 부모 >의 Quantity 특성을 식별합니다.

  • 이와 비슷하게 행 집합에 있는 OID 열에 대해 지정된 ColPattern, ../../@OrderID는 컨텍스트 노드에 대한 부모 노드의 부모 <Order>의 OrderID 특성을 식별합니다. 부모 노드는 <OrderDetail>이며, 컨텍스트 노드는 <ProductID>입니다.

그런 다음 SELECT 문은 OPENXML에서 제공하는 행 집합의 모든 열을 검색합니다.

DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
--Sample XML document
SET @xmlDocument =N'<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 Gonzalez">
   <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 @docHandle OUTPUT, @xmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail/@ProductID')
       WITH ( ProdID  int '.',
              Qty     int '../@Quantity',
              OID     int '../../@OrderID');
EXEC sp_xml_removedocument @docHandle;

다음은 결과입니다.

ProdID      Qty         OID
----------- ----------- -------
11          12          10248
42          10          10248
72          3           10283

H. 여러 개의 텍스트 노드가 있는 XML 문서 지정

XML 문서에 텍스트 노드가 여러 개 있는 경우 ColPattern, text()을 포함한 SELECT 문은 전체가 아닌 첫 번째 텍스트 노드만 반환합니다. 예시:

DECLARE @h int;
EXEC sp_xml_preparedocument @h OUTPUT,
         N'<root xmlns:a="urn:1">
           <a:Elem abar="asdf">
             T<a>a</a>U
           </a:Elem>
         </root>',
         '<ns xmlns:b="urn:1" />';

SELECT * FROM openxml(@h, '/root/b:Elem')
      WITH (Col1 varchar(20) 'text()');
EXEC sp_xml_removedocument @h;

SELECT 문은 결과로 TaU가 아닌 T를 반환합니다.

I. WITH 절에서 XML 데이터 형식 지정

WITH 절에서 형식화 여부에 관계없이 xml 데이터 형식 열에 매핑되는 열 패턴은 빈 시퀀스 또는 요소 시퀀스, 처리 명령, 텍스트 노드 및 주석을 반환해야 합니다. 데이터는 xml 데이터 형식으로 캐스팅됩니다.

다음 예에서 WITH 절에 있는 테이블 스키마 선언에는 xml 유형의 열이 포함됩니다.

DECLARE @h int;
DECLARE @x xml;
set @x = '<Root>
  <row id="1"><lname>Duffy</lname>
   <Address>
            <Street>111 Maple</Street>
            <City>Seattle</City>
   </Address>
  </row>
  <row id="2"><lname>Wang</lname>
   <Address>
            <Street>222 Pine</Street>
            <City>Bothell</City>
   </Address>
  </row>
</Root>';

EXEC sp_xml_preparedocument @h output, @x;
SELECT *
FROM   OPENXML (@h, '/Root/row', 10)
      WITH (id int '@id',

            lname    varchar(30),
            xmlname  xml 'lname',
            OverFlow xml '@mp:xmltext');
EXEC sp_xml_removedocument @h;

특히 xml 형식의 변수(@x)를 sp_xml_preparedocument() 함수로 전달합니다.

다음은 결과입니다.

id  lname   xmlname                   OverFlow
--- ------- ------------------------------ -------------------------------
1   Duffy   <lname>Duffy</lname>  <row><Address>
                                   <Street>111 Maple</Street>
                                   <City>Seattle</City>
                                  </Address></row>
2   Wang    <lname>Wang</lname>   <row><Address>
                                    <Street>222 Pine</Street>
                                    <City>Bothell</City>
                                   </Address></row>

결과에서 다음을 확인합니다.

  • varchar(30) 유형의 lname 열에 대해 값이 해당하는 <lname> 요소에서 검색됩니다.

  • xml 형식의 xmlname 열에 대해 동일한 이름 요소가 해당 값으로 반환됩니다.

  • 플래그는 10으로 설정되어 있습니다. 10은 2+8을 의미하며, 여기서 2는 요소 중심 매핑을 나타내고 8은 사용되지 않은 XML 데이터만 WITH 절에 정의된 OverFlow 열에 추가되어야 한다는 것을 나타냅니다. 플래그를 2로 설정하면 전체 XML 문서가 WITH 절에 지정된 OverFlow 열에 복사됩니다.

  • WITH 절에 있는 열이 형식화된 XML 열이고 XML 인스턴스가 스키마에 맞지 않는 경우 오류가 반환됩니다.

J. 다중 값 특성에서 개별 값 검색

XML 문서는 다중 값 특성을 가질 수 있습니다. 예를 들어 IDREFS 특성은 다중 값일 수 있습니다. XML 문서에서 다중 값을 갖는 특성 값은 공백으로 구분된 값을 갖는 문자열로 지정됩니다. 다음의 XML 문서에서 < Student > 요소의 attends 특성과 <Class>의 attendedBy 특성은 다중 값입니다. 다중 값 XML 특성에서 개별 값을 검색하고 각 값을 데이터베이스의 별도 행에 저장하는 것은 추가적인 작업이 필요합니다. 이 예제는 해당 프로세스를 보여줍니다.

이 샘플 XML 문서는 다음의 요소로 구성됩니다.

  • <Student>

    id(student ID), name및 attendedBy 특성입니다. attends 특성은 다중 값 특성입니다.

  • <클래스>

    id(class ID), name및 attendedBy 특성입니다. attendedBy 특성은 다중 값 특성입니다.

<Student>의 attends 특성과 <Class>의 attendedBy 특성은 Student 및 클래스 테이블 간 m:n 관계를 나타냅니다. 학생은 여러 수업을 수강할 수 있으며 수업에는 여러 학생이 있을 수 있습니다.

이 문서를 조각으로 나눈 후 다음과 같이 데이터베이스에 저장한다고 가정하십시오.

  • 학생 테이블에 <Student> 데이터를 저장합니다.

  • Courses 테이블에 <Class> 데이터를 저장합니다.

  • CourseAttendence 테이블에 학생과 수업 간의 m:n 관계 데이터를 저장합니다. 값을 추출하려면 더 많은 작업이 필요합니다. 이 정보를 검색하고 테이블에 저장하려면 다음 저장 프로시저를 사용합니다.

    • Insert_Idrefs_Values

      CourseAttendence 테이블에 과정 ID 및 학생 ID 값을 삽입합니다.

    • Extract_idrefs_values

      <Course> 요소별로 각각의 학생 ID를 추출합니다. Edge 테이블은 이러한 값을 검색하는 데 사용됩니다.

실행할 단계는 다음과 같습니다.

-- Create these tables:
DROP TABLE CourseAttendance;
DROP TABLE Students;
DROP TABLE Courses;
GO
CREATE TABLE Students(
                id   varchar(5) primary key,
                name varchar(30)
                );
GO
CREATE TABLE Courses(
               id       varchar(5) primary key,
               name     varchar(30),
               taughtBy varchar(5)
);
GO
CREATE TABLE CourseAttendance(
             id         varchar(5) references Courses(id),
             attendedBy varchar(5) references Students(id),
             constraint CourseAttendance_PK primary key (id, attendedBy)
);
GO
-- Create these stored procedures:
DROP PROCEDURE f_idrefs;
GO
CREATE PROCEDURE f_idrefs
    @t      varchar(500),
    @idtab  varchar(50),
    @id     varchar(5)
AS
DECLARE @sp int;
DECLARE @att varchar(5);
SET @sp = 0;
WHILE (LEN(@t) > 0)
BEGIN
    SET @sp = CHARINDEX(' ', @t+ ' ');
    SET @att = LEFT(@t, @sp-1);
    EXEC('INSERT INTO '+@idtab+' VALUES ('''+@id+''', '''+@att+''')');
    SET @t = SUBSTRING(@t+ ' ', @sp+1, LEN(@t)+1-@sp);
END;
GO

DROP PROCEDURE fill_idrefs
GO
CREATE PROCEDURE fill_idrefs
    @xmldoc     int,
    @xpath      varchar(100),
    @from       varchar(50),
    @to         varchar(50),
    @idtable    varchar(100)
AS
DECLARE @t varchar(500);
DECLARE @id varchar(5);

/* Temporary Edge table */
SELECT *
INTO #TempEdge
FROM OPENXML(@xmldoc, @xpath);

DECLARE fillidrefs_cursor CURSOR FOR
    SELECT CAST(iv.text AS nvarchar(200)) AS id,
           CAST(av.text AS nvarchar(4000)) AS refs
    FROM   #TempEdge c, #TempEdge i,
           #TempEdge iv, #TempEdge a, #TempEdge av
    WHERE  c.id = i.parentid
    AND    UPPER(i.localname) = UPPER(@from)
    AND    i.id = iv.parentid
    AND    c.id = a.parentid
    AND    UPPER(a.localname) = UPPER(@to)
    AND    a.id = av.parentid;

OPEN fillidrefs_cursor
FETCH NEXT FROM fillidrefs_cursor INTO @id, @t;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
        execute f_idrefs @t, @idtable, @id;
    END
    FETCH NEXT FROM fillidrefs_cursor INTO @id, @t
END;
CLOSE fillidrefs_cursor;
DEALLOCATE fillidrefs_cursor;
Go
-- This is the sample document that is shredded and the data is stored in the preceding tables.
DECLARE @h int;
EXECUTE sp_xml_preparedocument @h OUTPUT, N'<Data>
  <Student id = "s1" name = "Student1"  attends = "c1 c3 c6"  />
  <Student id = "s2" name = "Student2"  attends = "c2 c4" />
  <Student id = "s3" name = "Student3"  attends = "c2 c4 c6" />
  <Student id = "s4" name = "Student4"  attends = "c1 c3 c5" />
  <Student id = "s5" name = "Student5"  attends = "c1 c3 c5 c6" />
  <Student id = "s6" name = "Student6" />

  <Class id = "c1" name = "Intro to Programming"
         attendedBy = "s1 s4 s5" />
  <Class id = "c2" name = "Databases"
         attendedBy = "s2 s3" />
  <Class id = "c3" name = "Operating Systems"
         attendedBy = "s1 s4 s5" />
  <Class id = "c4" name = "Networks" attendedBy = "s2 s3" />
  <Class id = "c5" name = "Algorithms and Graphs"
         attendedBy =  "s4 s5"/>
  <Class id = "c6" name = "Power and Pragmatism"
         attendedBy = "s1 s3 s5" />
</Data>';

INSERT INTO Students SELECT * FROM OPENXML(@h, '//Student') WITH Students

INSERT INTO Courses SELECT * FROM OPENXML(@h, '//Class') WITH Courses
/* Using the edge table */
EXECUTE fill_idrefs @h, '//Class', 'id', 'attendedby', 'CourseAttendance';

SELECT * FROM Students;
SELECT * FROM Courses;
SELECT * FROM CourseAttendance;

EXECUTE sp_xml_removedocument @h;

K. XML에서 base64로 인코딩된 데이터에서 이진 검색

이진 데이터는 통상적으로 base64 인코딩을 사용하여 XML에 포함됩니다. OPENXML을 사용하여 이 XML을 조각화하면 base64로 인코딩된 데이터를 수신합니다. 이 예제는 base64로 인코딩된 데이터를 다시 이진 파일로 변환하는 방법을 보여줍니다.

  • 예제 이진 데이터가 포함된 테이블을 만듭니다.

  • FOR XML 쿼리 및 BINARY BASE64 옵션을 사용하여 base64로 인코딩된 이진 데이터가 있는 XML을 생성합니다.

  • OPENXML을 사용하여 XML을 조각화합니다. OPENXML에서 반환되는 데이터는 base64로 인코딩된 데이터입니다. 그런 다음 ..value 함수를 호출하여 이를 다시 이진으로 변환합니다.

CREATE TABLE T (Col1 int primary key, Col2 varbinary(100));
GO
-- Insert sample binary data
INSERT T VALUES(1, 0x1234567890);
GO
-- Create test XML document that has base64 encoded binary data (use FOR XML query and specify BINARY BASE64 option)
SELECT * FROM T
FOR XML AUTO, BINARY BASE64;
GO
-- result
-- <T Col1="1" Col2="EjRWeJA="/>

-- Now shredd the sample XML using OPENXML.
-- Call the .value function to convert
-- the base64 encoded data returned by OPENXML to binary.
DECLARE @h int;
EXEC sp_xml_preparedocument @h OUTPUT, '<T Col1="1" Col2="EjRWeJA="/>';
SELECT Col1,
CAST('<binary>' + Col2 + '</binary>' AS XML).value('.', 'varbinary(max)') AS BinaryCol
FROM openxml(@h, '/T')
WITH (Col1 integer, Col2 varchar(max)) ;
EXEC sp_xml_removedocument @h;
GO

다음은 결과입니다. 반환된 이진 데이터는 테이블 T에 있는 원래 이진 데이터입니다.

Col1        BinaryCol
----------- ---------------------
1           0x1234567890

참고 항목