예제: OPENXML 사용Examples: Using OPENXML

이 항목의 예제에서는 XML 문서의 행 집합 뷰를 만들 때 OPENXML을 사용하는 방법을 설명합니다.The examples in this topic show how OPENXML is used to create a rowset view of an XML document. OPENXML 구문에 대한 자세한 내용은 OPENXML(Transact-SQL)을 참조하세요.For information about the syntax of OPENXML, see OPENXML (Transact-SQL). 다음 예에서는 OPENXML의 메타 속성 지정을 제외한 OPENXML의 모든 측면을 보여 줍니다.The examples show all aspects of OPENXML, but do not specify metaproperties in OPENXML. OPENXML에서 메타 속성을 지정하는 방법은 OPENXML에 메타 속성 지정을 참조하세요.For more information about how to specify metaproperties in OPENXML, see Specify Metaproperties in OPENXML.

Examples

데이터를 검색할 때 XML 문서에서 행을 결정하는 노드를 식별하는 데 rowpattern 이 사용됩니다.In retrieving the data, rowpattern is used to identify the nodes in the XML document that determine the rows. 또한 MSXML XPath 구현에 사용된 XPath 패턴 언어에 rowpattern 이 표현됩니다.Additionally, rowpattern is expressed in the XPath pattern language that is used in the MSXML XPath implementation. 예를 들어 패턴이 요소나 특성으로 끝나는 경우에는 rowpattern에 의해 지정된 각 요소 또는 특성 노드에 대해 한 개의 행이 생성됩니다.For example, if the pattern ends in an element or an attribute, a row is created for each element or attribute node that is selected by rowpattern.

flags 값은 기본 매핑을 제공합니다.The flags value provides default mapping. ColPatternSchemaDeclaration에 지정되지 않은 경우 flags 에 지정된 매핑이 간주됩니다.If no ColPattern is specified in the SchemaDeclaration, the mapping specified in flags is assumed. ColPatternSchemaDeclaration 에 지정된 경우에는 flags값이 무시됩니다.The flags value is ignored if ColPattern is specified in SchemaDeclaration. 지정된 ColPattern 은 매핑(특성 중심 또는 요소 중심)은 물론, 오버플로와 소비되지 않은 데이터를 처리할 때의 동작도 결정합니다.The specified ColPattern determines the mapping, attribute-centric or element-centric, and also the behavior in dealing with overflow and unconsumed data.

1.A. OPENXML에서 단순 SELECT 문 실행Executing a simple SELECT statement with OPENXML

이 예의 XML 문서는 <Customer>, <Order> 및 <OrderDetail> 요소로 이루어져 있습니다.The XML document in this example is made up of the <Customer>, <Order>, and <OrderDetail> elements. OPENXML 문은 XML 문서로부터 두 열(CustomerIDContactName)로 구성된 행 집합의 고객 정보를 검색합니다.The OPENXML statement retrieves customer information in a two-column rowset, CustomerID and ContactName, from the XML document.

먼저 sp_xml_preparedocument 저장 프로시저가 문서 핸들을 얻기 위해 호출됩니다.First, the sp_xml_preparedocument stored procedure is called to obtain a document handle. 이 문서 핸들은 OPENXML에 전달됩니다.This document handle is passed to OPENXML.

OPENXML 문에서는 다음을 보여 줍니다.The OPENXML statement illustrates the following:

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

  • flags 매개 변수 값은 1로 설정되어 특성 중심의 매핑을 나타냅니다.The flags parameter value is set to 1 and indicates attribute-centric mapping. 결과적으로 XML 특성은 SchemaDeclaration에 정의된 행 집합의 열에 매핑됩니다.As a result, the XML attributes map to the columns in the rowset defined in SchemaDeclaration.

  • WITH 절의 SchemaDeclaration에서는 지정된 ColName 값이 해당 XML 특성 이름과 일치합니다.In SchemaDeclaration, in the WITH clause, the specified ColName values match the corresponding XML attribute names. 따라서 ColPattern 매개 변수는 SchemaDeclaration에 지정되지 않습니다.Therefore, the ColPattern parameter is not specified in SchemaDeclaration.

    그런 다음 SELECT 문은 OPENXML이 제공하는 행 집합의 모든 열을 검색합니다.The SELECT statement then retrieves all the columns in the rowset provided by 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 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 @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  

다음은 결과입니다.This is the result:

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

<Customer> 요소에는 하위 요소가 없기 때문에 flags2로 설정되어 요소 중심 매핑을 나타내도록 같은 SELECT 문이 실행되는 경우 두 고객에 대한 CustomerIDContactName의 값은 NULL로 반환됩니다.Because the <Customer> elements do not have any subelements, if the same SELECT statement is executed with flags set to 2 to indicate element-centric mapping, the values of CustomerID and ContactName for both the customers are returned as NULL.

@xmlDocument는 xml 유형 또는 (n)varchar(max) 유형일 수 있습니다.The @xmlDocument can also be of xml type or of (n)varchar(max) type.

XML 문서에서 <CustomerID>와 <ContactName>이 하위 요소인 경우에는 요소 중심의 매핑이 값을 검색합니다.If <CustomerID> and <ContactName> in the XML document are subelements, the element-centric mapping retrieves the values.

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 Gonzlez</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  

다음은 결과입니다.This is the result:

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

sp_xml_preparedocument 에 의해 반환되는 문서 핸들은 세션이 아닌 일괄 처리 기간 동안 유효합니다.Note that the document handle returned by sp_xml_preparedocument is valid for the duration of the batch and not the session.

2.B. 행 집합 열과 XML 특성 및 요소 간의 매핑을 위해 ColPattern 지정Specifying ColPattern for mapping between rowset columns and the XML attributes and elements

이 예제에서는 행 집합 열과 XML 특성 및 요소 간에 매핑을 제공하기 위해 선택 사항인 ColPattern 매개 변수에 XPath 패턴을 지정하는 방법을 보여 줍니다.This example shows how the XPath pattern is specified in the optional ColPattern parameter to provide mapping between rowset columns and the XML attributes and elements.

이 예의 XML 문서는 <Customer>, <Order> 및 <OrderDetail> 요소로 이루어져 있습니다.The XML document in this example is made up of the <Customer>, <Order>, and <OrderDetail> elements. OPENXML 문은 고객과 주문 정보를 XML 문서로부터 행 집합(CustomerID, OrderDate, ProdIDQty)으로 검색합니다.The OPENXML statement retrieves customer and order information as a rowset (CustomerID, OrderDate, ProdID, and Qty) from the XML document.

먼저 sp_xml_preparedocument 저장 프로시저가 문서 핸들을 얻기 위해 호출됩니다.First, the sp_xml_preparedocument stored procedure is called to obtain a document handle. 이 문서 핸들은 OPENXML에 전달됩니다.This document handle is passed to OPENXML.

OPENXML 문에서는 다음을 보여 줍니다.The OPENXML statement illustrates the following:

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

    이해를 돕기 위해 flags 매개 변수 값은 2로 설정되어 특성 중심의 매핑을 나타냅니다.For illustration, the flags parameter value is set to 2 and indicates element-centric mapping. 하지만 ColPattern 에 지정된 매핑은 이 매핑을 덮어 씁니다.However, the mapping specified in ColPattern overwrites this mapping. 즉, ColPattern 에 지정된 XPath 패턴은 행 집합의 열을 특성으로 매핑합니다.That is, the XPath pattern specified in ColPattern maps the columns in the rowset to attributes. 그러면 특성 중심 매핑이 됩니다.This results in attribute-centric mapping.

    WITH 절에 있는 SchemaDeclaration에서는 ColPatternColNameColType 매개 변수로 지정됩니다.In SchemaDeclaration, in the WITH clause, ColPattern is also specified with the ColName and ColType parameters. 선택 항목인 ColPattern 은 지정된 XPath 패턴이며 다음을 나타냅니다.The optional ColPattern is the XPath pattern specified and indicates the following:

  • 행 집합의 OrderID, CustomerIDOrderDate 열은 rowpattern에 의해 식별되는 노드의 부모 노드 특성으로 매핑되고 rowpattern은 <OrderDetail> 노드를 식별합니다.The OrderID, CustomerID, and OrderDate columns in the rowset map to the attributes of the parent of the nodes identified by rowpattern, and rowpattern identifies the <OrderDetail> nodes. 따라서 CustomerIDOrderDate 열은 <Order> 요소의 CustomerIDOrderDate 특성으로 매핑됩니다.Therefore, the CustomerID and OrderDate columns map to CustomerID and OrderDate attributes of the <Order> element.

  • 행 집합의 ProdIDQty 열은 rowpattern 에서 식별된 노드의 ProductIDQuantity특성으로 매핑됩니다.The ProdID and Qty columns in the rowset map to the ProductID and Quantity attributes of the nodes identified in rowpattern.

    그런 다음 SELECT 문은 OPENXML이 제공하는 행 집합의 모든 열을 검색합니다.The SELECT statement then retrieves all the columns in the rowset provided by 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 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 @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  

다음은 결과입니다.This is the result:

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 요소를 매핑하도록 지정될 수 있습니다.The XPath pattern specified as ColPattern can also be specified to map the XML elements to the rowset columns. 그러면 요소 중심 매핑이 됩니다.This results in element-centric mapping. 다음 예에서 XML 문서 <CustomerID> 및 <OrderDate>는 <Orders> 요소의 하위 요소입니다.In the following example, the XML document <CustomerID> and <OrderDate> are subelements of the <Orders> element. ColPatternflags 매개 변수에 지정된 매핑을 덮어쓰므로 OPENXML에는 flags 매개 변수가 지정되지 않습니다.Because ColPattern overwrites the mapping specified in the flags parameter, the flags parameter is not specified in 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 Gonzlez">  
   <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  

3.C. 특성 중심 및 요소 중심의 매핑 결합Combining attribute-centric and element-centric mapping

이 예에서 flags 매개 변수는 3 으로 설정되며 특성 중심 및 요소 중심 매핑이 적용됨을 나타냅니다.In this example, the flags parameter is set to 3 and indicates that both attribute-centric and element-centric mapping will be applied. 이 경우, 특성 중심의 매핑이 먼저 적용된 다음 아직 처리되지 않은 모든 열에 대해 요소 중심의 매핑이 적용됩니다.In this case, the attribute-centric mapping is applied first, and then element-centric mapping is applied for all the columns not yet dealt with.

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 Gonzlez</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  

다음은 결과입니다.This is the result

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

특성 중심의 매핑은 CustomerID에 대해 적용됩니다.The attribute-centric mapping is applied for CustomerID. <Customer> 요소에는 ContactName 특성이 없습니다.There is no ContactName attribute in the <Customer> element. 따라서 요소 중심 매핑이 적용됩니다.Therefore, element-centric mapping is applied.

4.D. text() XPath 함수를 ColPattern으로 지정Specifying the text() XPath function as ColPattern

이 예의 XML 문서는 <Customer> 및 <Order> 요소로 이루어져 있습니다.The XML document in this example is made up of the <Customer> and <Order> elements. OPENXML 문은 <Order> 요소의 oid 특성, rowpattern으로 식별된 노드에 대한 부모 노드의 ID 및 요소 콘텐츠의 리프 값 문자열로 구성된 행 집합을 검색합니다.The OPENXML statement retrieves a rowset that is made up of the oid attribute from the <Order> element, the ID of the parent of the node identified by rowpattern, and the leaf-value string of the element content.

먼저 sp_xml_preparedocument 저장 프로시저가 문서 핸들을 얻기 위해 호출됩니다.First, the sp_xml_preparedocument stored procedure is called to obtain a document handle. 이 문서 핸들은 OPENXML에 전달됩니다.This document handle is passed to OPENXML.

OPENXML 문에서는 다음을 보여 줍니다.The OPENXML statement illustrates the following:

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

  • flags 매개 변수 값은 1로 설정되어 특성 중심의 매핑을 나타냅니다.The flags parameter value is set to 1 and indicates attribute-centric mapping. 결과적으로 XML 특성은 SchemaDeclaration에 정의된 행 집합 열에 매핑됩니다.As a result, the XML attributes map to the rowset columns defined in SchemaDeclaration.

  • WITH 절에 있는 SchemaDeclaration 에서는 행 집합 열 이름 oidamount 가 해당 XML 특성 이름과 일치합니다.In SchemaDeclaration in the WITH clause, the oid and amount rowset column names match the corresponding XML attribute names. 따라서 ColPattern 매개 변수는 지정되지 않습니다.Therefore, the ColPattern parameter is not specified. 행 집합의 comment 열에 대해서는 XPath 함수 text()ColPattern으로 지정됩니다.For the comment column in the rowset, the XPath function, text(), is specified as ColPattern. 이것은 flags에 지정된 특성 중심의 매핑을 덮어쓰며 열에는 요소 내용의 리프 값 문자열이 포함됩니다.This overwrites the attribute-centric mapping specified in flags, and the column contains the leaf-value string of the element content.

    그런 다음 SELECT 문은 OPENXML이 제공하는 행 집합의 모든 열을 검색합니다.The SELECT statement then retrieves all the columns in the rowset provided by 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  

다음은 결과입니다.This is the result:

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

5.E. WITH 절에 TableName 지정Specifying TableName in the WITH clause

이 예에서는 WITH 절에서 SchemaDeclaration 대신 TableName을 지정합니다.This example specifies TableName in the WITH clause instead of SchemaDeclaration. 이것은 원하는 구조를 가지고 있고 열 패턴 ColPattern 매개 변수는 필요하지 않은 테이블을 사용하는 경우에 유용합니다.This is useful if you have a table that has the structure you want and no column patterns, ColPattern parameter, are required.

이 예의 XML 문서는 <Customer> 및 <Order> 요소로 이루어져 있습니다.The XML document in this example is made up of the <Customer> and <Order> elements. OPENXML 문은 XML 문서로부터 세 열로 구성된 행 집합(oid, dateamount)의 주문 정보를 검색합니다.The OPENXML statement retrieves order information in a three-column rowset (oid, date, and amount) from the XML document.

먼저 sp_xml_preparedocument 저장 프로시저가 문서 핸들을 얻기 위해 호출됩니다.First, the sp_xml_preparedocument stored procedure is called to obtain a document handle. 이 문서 핸들은 OPENXML에 전달됩니다.This document handle is passed to OPENXML.

OPENXML 문에서는 다음을 보여 줍니다.The OPENXML statement illustrates the following:

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

  • WITH 절에는 SchemaDeclaration 이 없습니다.There is no SchemaDeclaration in the WITH clause. 그 대신 테이블 이름이 지정됩니다.Instead, a table name is specified. 따라서 테이블 스키마가 행 집합 스키마로 사용됩니다.Therefore, the table schema is used as the rowset schema.

  • flags 매개 변수 값은 1 로 설정되어 특성 중심의 매핑을 나타냅니다.The flags parameter value is set to 1 and indicates attribute-centric mapping. 따라서 rowpattern으로 식별되는 요소의 특성은 동일한 이름의 행 집합 열에 매핑됩니다.Therefore, attributes of the elements, identified by rowpattern, map to the rowset columns with the same name.

    그런 다음 SELECT 문은 OPENXML이 제공하는 행 집합의 모든 열을 검색합니다.The SELECT statement then retrieves all the columns in the rowset provided by 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  

다음은 결과입니다.This is the result:

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  

6.F. Edge 테이블 형식으로 결과 얻기Obtaining the result in an edge table format

이 예제에서는 OPENXML 문에서 WITH 절이 지정되지 않습니다.In this example, the WITH clause is not specified in the OPENXML statement. 따라서 OPENXML이 생성하는 행 집합은 Edge 테이블 형식이 됩니다.As a result, the rowset generated by OPENXML has an edge table format. SELECT 문은 Edge 테이블의 열을 모두 반환합니다.The SELECT statement returns all the columns in the edge table.

이 예의 예제 XML 문서는 <Customer>, <Order> 및 <OrderDetail> 요소로 이루어져 있습니다.The sample XML document in the example is made up of the <Customer>, <Order>, and <OrderDetail> elements.

먼저 sp_xml_preparedocument 저장 프로시저가 문서 핸들을 얻기 위해 호출됩니다.First, the sp_xml_preparedocument stored procedure is called to obtain a document handle. 이 문서 핸들은 OPENXML에 전달됩니다.This document handle is passed to OPENXML.

OPENXML 문에서는 다음을 보여 줍니다.The OPENXML statement illustrates the following:

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

  • WITH 절은 제공되지 않습니다.The WITH clause is not provided. 따라서 OPENXML은 행 집합을 Edge 테이블 형식으로 반환합니다.Therefore, OPENXML returns the rowset in an edge table format.

    그런 다음 SELECT 문은 Edge 테이블의 모든 열을 검색합니다.The SELECT statement then retrieves all the columns in the edge table.

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 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 @docHandle OUTPUT, @xmlDocument  
-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@docHandle, '/ROOT/Customer')  

EXEC sp_xml_removedocument @docHandle  

결과는 Edge 테이블로 반환됩니다.The result is returned as an edge table. Edge 테이블에 대해 쿼리를 작성하여 다음과 같은 정보를 얻을 수 있습니다.You can write queries against the edge table to obtain information. 예를 들어For example:

  • 다음은 문서의 Customer 노드 수를 반환하는 쿼리입니다.The following query returns the number of Customer nodes in the document. WITH 절이 지정되지 않았으므로 OPENXML은 Edge 테이블을 반환합니다.Because the WITH clause is not specified, OPENXML returns an edge table. SELECT 문은 Edge 테이블을 쿼리합니다.The SELECT statement queries the edge table.

    SELECT count(*)  
    FROM OPENXML(@docHandle, '/')  
    WHERE localname = 'Customer'  
    
  • 다음 쿼리는 요소 유형의 XML 노드에 대한 로컬 이름을 반환합니다.The following query returns the local names of XML nodes of element type.

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

7.G. 특성으로 끝나는 rowpattern 지정Specifying rowpattern ending with an attribute

이 예의 XML 문서는 <Customer>, <Order> 및 <OrderDetail> 요소로 이루어져 있습니다.The XML document in this example is made up of the <Customer>, <Order>, and <OrderDetail> elements. OPENXML 문은 XML 문서로부터 세 열로 구성된 행 집합(ProductID, QuantityOrderID)의 주문 정보를 검색합니다.The OPENXML statement retrieves information about the order details in a three-column rowset (ProductID, Quantity, and OrderID) from the XML document.

먼저 sp_xml_preparedocument 가 문서 핸들을 얻기 위해 호출됩니다.First, the sp_xml_preparedocument is called to obtain a document handle. 이 문서 핸들은 OPENXML에 전달됩니다.This document handle is passed to OPENXML.

OPENXML 문에서는 다음을 보여 줍니다.The OPENXML statement illustrates the following:

  • rowpattern(/ROOT/Customer/Order/OrderDetail/@ProductID)은 XML 특성, ProductID로 끝납니다.rowpattern (/ROOT/Customer/Order/OrderDetail/@ProductID) ends with an XML attribute, ProductID. 결과 행 집합에서는 XML 문서에서 선택된 각 특성 노드에 대해 행이 만들어집니다.In the resulting rowset, a row is created for each attribute node selected in the XML document.

  • 이 예제에는 flags 매개 변수가 지정되지 않습니다.In this example, the flags parameter is not specified. 그 대신 ColPattern 매개 변수에 의해 매핑이 지정됩니다.Instead, the mappings are specified by the ColPattern parameter.

    WITH 절에 있는 SchemaDeclaration 에서는 ColPatternColNameColType 매개 변수로 지정됩니다.In SchemaDeclaration in the WITH clause, ColPattern is also specified with the ColName and ColType parameters. 선택 사항인 ColPattern 은 다음을 나타내기 위해 지정된 XPath 패턴입니다.The optional ColPattern is the XPath pattern specified to indicate the following:

  • 행 집합의 ProdID 열에 대해 ColPattern으로 지정된 XPath 패턴(.)은 컨텍스트 노드인 현재 노드를 식별합니다.The XPath pattern (.) specified as ColPattern for the ProdID column in the rowset identifies the context node, current node. 지정된 rowpattern에 따른 <OrderDetail> 요소의 ProductID 특성입니다.As per the rowpattern specified, it is the ProductID attribute of the <OrderDetail> element.

  • 행 집합에 있는 Qty 열에 대해 지정된 ColPattern, ../@Quantity는 컨텍스트 노드 <ProductID>의 노드인 부모 <OrderDetail>의 Quantity 특성을 식별합니다.The ColPattern, ../@Quantity, specified for the Qty column in the rowset identifies the Quantity attribute of the parent, <OrderDetail>, node of the context node, <ProductID>.

  • 이와 비슷하게 행 집합에 있는 OID 열에 대해 지정된 ColPattern, ../../@OrderID는 컨텍스트 노드에 대한 부모 노드의 부모 <Order>의 OrderID 특성을 식별합니다.Similarly, the ColPattern, ../../@OrderID, specified for the OID column in the rowset identifies the OrderID attribute of the parent, <Order>, of the parent node of the context node. 부모 노드는 <OrderDetail>이고 컨텍스트 노드는 <ProductID>입니다.The parent node is <OrderDetail>, and the context node is <ProductID>.

    그런 다음 SELECT 문은 OPENXML이 제공하는 행 집합의 모든 열을 검색합니다.The SELECT statement then retrieves all the columns in the rowset provided by 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 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 @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  

다음은 결과입니다.This is the result:

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

8.H. 여러 개의 텍스트 노드가 있는 XML 문서 지정Specifying an XML document that has multiple text nodes

XML 문서에 텍스트 노드가 여러 개 있는 경우 text() ColPattern을 포함한 SELECT 문은 전체가 아닌 첫 번째 텍스트 노드만 반환합니다.If you have multiple text nodes in an XML document, a SELECT statement with a ColPattern, text(), returns only the first text node, instead of all of them. 예를 들어For example:

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를 결과로 반환합니다.The SELECT statement returns T as the result, and not TaU.

9.I. WITH 절에서 xml 데이터 형식 지정Specifying the xml data type in the WITH clause

WITH 절에서 형식화된 열 및 형식화되지 않은 열을 모두 포함하여 xml 데이터 형식 열로 매핑된 열 패턴은 빈 시퀀스나 요소 시퀀스, 처리 명령, 텍스트 노드 및 주석을 반환해야 합니다.In the WITH clause, a column pattern that is mapped to the xml data type column, whether typed or untyped, must return either an empty sequence or a sequence of elements, processing instructions, text nodes, and comments. 데이터는 xml 데이터 형식으로 캐스팅됩니다.The data is cast to an xml data type.

다음 예에서 WITH 절에 있는 테이블 스키마 선언에는 xml 유형의 열이 포함됩니다.In the following example, the table schema declaration in the WITH clause includes xml type columns.

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() 함수로 전달합니다.Specifically, you are passing an xml type variable (@x) to the sp_xml_preparedocument() function.

다음은 결과입니다.This is the result:

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>  

결과에서 다음을 확인하십시오.Note the following from the result:

  • varchar(30) 유형의 lname 열에 대해 값이 해당 <lname> 요소로부터 검색됩니다.For the lname column of varchar(30) type, its value is retrieved from the corresponding <lname> element.

  • xml 유형의 xmlname 열에 대해 같은 이름의 요소가 해당 값으로 반환됩니다.For the xmlname column of xml type, the same name element is returned as its value.

  • 플래그는 10으로 설정되어 있습니다.The flag is set to 10. 10은 2 + 8을 의미하며, 여기서 2는 요소 중심 매핑이고 8은 사용되지 않은 XML 데이터만 WITH 절에 정의된 OverFlow 열에 추가되어야 함을 나타냅니다.The 10 means 2 + 8, where 2 indicates element-centric mapping and 8 indicates that only unconsumed XML data should be added to the OverFlow column defined in the WITH clause. 플래그를 2로 설정한 경우 전체 XML 문서가 WITH 절에 지정된 OverFlow 열에 복사됩니다.If you set the flag to 2, the whole XML document is copied to the OverFlow column that is specified in the WITH clause.

  • WITH 절에 있는 열이 형식화된 XML 열이고 XML 인스턴스가 스키마에 맞지 않는 경우 오류가 반환됩니다.In case the column in the WITH clause is a typed XML column and the XML instance does not confirm to the schema, an error is returned.

10.J. 다중 값 특성에서 개별 값 검색Retrieving individual values from multivalued attributes

XML 문서는 다중 값 특성을 가질 수 있습니다.An XML document can have attributes that are multivalued. 예를 들어 IDREFS 특성은 다중 값일 수 있습니다.For example, the IDREFS attribute can be multivalued. XML 문서에서 다중 값 특성 값은 공간으로 값이 구별되는 문자열로 지정됩니다.In an XML document, the multivalued attribute values are specified as a string, with the values separated by a space. 다음 XML 문서에서 <Student> 요소의 attends 특성과 <Class>의 attendedBy 특성은 다중 값을 갖고 있습니다.In the following XML document, the attends attribute of the <Student> element and the attendedBy attribute of <Class> are multivalued. 다중 값 XML 특성에서 개별 값을 검색하고 데이터베이스의 독립된 행에서 각각의 값을 저장하려면 추가 작업이 필요합니다.Retrieving individual values from a multivalued XML attribute and storing each value in a separate row in the database requires additional work. 다음 예제에서는 처리 과정을 보여 줍니다.This example shows the process.

이 예제 XML 문서는 다음 요소로 구성됩니다.This sample XML document is made up of the following elements:

  • <Student><Student>

    id (학생 ID), nameattends 특성입니다.The id (student ID), name, and attends attributes. attends 특성은 다중 값 특성입니다.The attends attribute is a multivalued attribute.

  • <Class><Class>

    id (학생 ID), nameattendedBy 특성입니다.The id (class ID), name, and attendedBy attributes. attendedBy 특성은 다중 값 특성입니다.The attendedBy attribute is a multivalued attribute.

    <Student>에 있는 attends 특성과 <Class>에 있는 attendedBy 특성은 Student 및 Class 테이블 간의 m:n 관계를 나타냅니다.The attends attribute in <Student> and the attendedBy attribute in <Class> represent a m:n relationship between the Student and Class tables. 학생은 여러 개의 수업을 받을 수 있고 한 수업에는 여러 학생이 있을 수 있습니다.A student can take many classes and a class can have many students.

    이 문서를 조각으로 나눈 후 다음과 같이 데이터베이스에 저장한다고 가정하십시오.Assume that you want to shred this document and save it in the database as shown in the following:

  • Students 테이블에 <Student> 데이터를 저장합니다.Save the <Student> data in the Students table.

  • Courses 테이블에 <Class> 데이터를 저장합니다.Save the <Class> data in the Courses table.

  • CourseAttendence 테이블에 학생과 수업 간의 m:n 관계 데이터를 저장합니다.Save the m:n relationship data, between Student and Class, in the CourseAttendence table. 값을 추출하려면 추가 작업이 필요합니다.Additional work is required to extract the values. 이 정보를 검색하고 테이블에 저장하려면 다음 저장 프로시저를 사용하십시오.To retrieve this information and store it in the table, use these stored procedures:

    • Insert_Idrefs_ValuesInsert_Idrefs_Values

      CourseAttendence 테이블에 학과 ID와 학생 ID를 삽입합니다.Inserts the values of course ID and student ID in the CourseAttendence table.

    • Extract_idrefs_valuesExtract_idrefs_values

      <Course> 요소에서 각각의 학생 ID를 추출합니다.Extracts the individual student IDs from each <Course> element. Edge 테이블을 사용하여 이 값을 검색합니다.An edge table is used to retrieve these values.

    다음 단계를 참조하십시오.Here are the steps:

-- 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  

11.K. XML의 base64로 인코딩된 데이터에서 이진 검색Retrieving binary from base64 encoded data in XML

이진 데이터는 주로 base64 인코딩을 사용하여 XML에 포함됩니다.Binary data is frequently included in XML using base64 encoding. OPENXML을 사용하여 이 XML을 조각으로 나누면 base64로 인코딩된 데이터를 얻습니다.When you shred this XML by using OPENXML, you receive the base64 encoded data. 이 예에서는 base64로 인코딩된 데이터를 다시 이진으로 변환하는 방법에 대해 설명합니다.This example shows how you can convert the base64 encoded data back to binary.

  • 예제 이진 데이터가 포함된 테이블을 만듭니다.Create a table with sample binary data.

  • FOR XML 쿼리 및 BINARY BASE64 옵션을 사용하여 이진 데이터가 base64로 인코딩된 XML을 생성합니다.Use a FOR XML query and the BINARY BASE64 option to construct XML that has the binary data encoded as base64.

  • OPENXML을 사용하여 XML을 조각으로 나눕니다.Shred the XML by using OPENXML. OPENXML에 의해 반환된 데이터는 base64 인코딩 데이터가 됩니다.The data returned by OPENXML will be base64 encoded data. 그런 다음 .value 함수를 호출하여 이를 다시 이진으로 변환합니다.Next, call the .value function to convert it back to binary.

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에 있는 원래 이진 데이터입니다.The binary data returned is the original binary data in table T.

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

참고 항목See Also

sp_xml_preparedocument(Transact-SQL) sp_xml_preparedocument (Transact-SQL)
sp_xml_removedocument(Transact-SQL) sp_xml_removedocument (Transact-SQL)
OPENXML(Transact-SQL) OPENXML (Transact-SQL)
OPENXML(SQL Server) OPENXML (SQL Server)