다음을 통해 공유


OPENXML(Transact-SQL)

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

OPENXML 는 XML 문서에 대한 행 집합 보기를 제공합니다. OPENXML 행 집합 공급자이므로 테이블, OPENXML 뷰 또는 OPENROWSET 함수와 같은 행 집합 공급자가 나타날 수 있는 Transact-SQL 문에서 사용할 수 있습니다.

Transact-SQL 구문 표기 규칙

구문

OPENXML ( idoc int [ in ]
    , rowpattern nvarchar [ in ]
    , [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]

인수

idoc

XML 문서의 내부 표현에 대한 문서 핸들입니다. XML 문서의 내부 표현은 호출 sp_xml_preparedocument을 통해 만들어집니다.

rowpattern

행으로 처리할 노드를 식별하는 데 사용되는 XPath 패턴입니다. 이 노드는 idoc 매개 변수로 핸들이 전달되는 XML 문서에서 가져옵니다.

flags

XML 데이터와 관계형 행 집합 사이에 사용되는 매핑과 남는 열을 채우는 방법을 나타냅니다. flags는 선택적 입력 매개 변수이며 다음 값 중 하나일 수 있습니다.

바이트 값 설명
0 기본값은 매핑입니다 attribute-centric .
1 매핑을 attribute-centric 사용합니다. XML_ELEMENTS와 결합할 수 있습니다. 이 경우 attribute-centric 매핑이 먼저 적용됩니다. 다음으로, element-centric 나머지 열에 매핑이 적용됩니다.
2 매핑을 element-centric 사용합니다. XML_ATTRIBUTES와 결합할 수 있습니다. 이 경우 element-centric 매핑이 먼저 적용됩니다. 다음으로, attribute-centric 나머지 열에 매핑이 적용됩니다.
8 또는 .와 결합할 수 있습니다(논리적 OR)XML_ATTRIBUTESXML_ELEMENTS. 검색 컨텍스트에서 이 플래그는 사용된 데이터를 오버플로 속성 @mp:xmltext에 복사해서는 안 됨을 나타냅니다.

SchemaDeclaration

양식의 스키마 정의: ColNameColType [ ColPattern | MetaProperty ] [ , ColNameColType [ ColPattern | MetaProperty ] ... ]

  • ColName

    행 집합의 열 이름입니다.

  • ColType

    행 집합에 있는 열의 SQL Server 데이터 형식입니다. 열 형식이 특성의 기본 xml 데이터 형식과 다른 경우 형식 강제 변환이 발생합니다.

  • ColPattern

    XML 노드를 열에 매핑하는 방법을 설명하는 선택적 일반 XPath 패턴입니다. ColPattern을 지정하지 않으면 기본 매핑(attribute-centric또는 element-centric 플래그지정된 매핑)이 발생합니다.

    ColPattern으로 지정된 XPath 패턴은 플래그표시된 기본 매핑을 덮어쓰거나 향상시키는 매핑(for attribute-centricelement-centric mapping)의 특수 특성을 지정하는 데 사용됩니다.

    ColPattern으로 지정된 일반 XPath 패턴은 메타 속성도 지원합니다.

  • MetaProperty

    에서 제공하는 OPENXML메타 속성 중 하나입니다. MetaProperty가 지정되면 메타 속성이 제공하는 정보가 열에 포함됩니다. 메타 속성을 통해 상대적 위치 및 네임스페이스 정보 등 XML 노드에 대한 정보를 추출할 수 있습니다. 이 메타 속성은 텍스트로 표시되는 것보다 더 많은 정보를 제공합니다.

TableName

원하는 스키마가 있는 테이블이 이미 있고 열 패턴이 필요하지 않은 경우 스키마Declaration 대신 지정할 수 있는 테이블 이름입니다.

설명

이 절은 WITH SchemaDeclaration을 사용하거나 기존 TableName을 지정하여 행 집합 형식(및 필요에 따라 추가 매핑 정보)을 제공합니다. 선택적 WITH 절을 지정하지 않으면 결과가 에지 테이블 형식으로 반환됩니다. 에지 테이블은 단일 테이블의 세분화된 XML 문서 구조(예: 요소/특성 이름, 문서 계층 구조, 네임스페이스, PI 등)를 나타냅니다.

다음 표에서는 edge 테이블의 구조에 대해 설명합니다.

열 이름 데이터 형식 설명
id bigint 문서 노드의 고유 ID입니다.

루트 요소에는 ID 값이 있습니다 0. 음수 ID 값은 예약된 값입니다.
parentid bigint 노드의 부모를 나타냅니다. 이 ID로 식별된 부모가 반드시 부모 요소는 아니지만 부모가 이 ID로 식별되는 노드에 따라 달라집니다 nodetype . 예를 들어 노드가 텍스트 노드인 경우 해당 노드의 부모는 특성 노드일 수 있습니다.

노드가 XML 문서의 최상위 수준에 있는 경우 노드 ParentID 는 .입니다 NULL.
nodetype int 노드 유형을 나타냅니다. 이 값은 XML DOM 노드 형식 번호 매기기와 일치하는 정수입니다. 노드 유형은 다음과 같습니다.

1 = 요소 노드
2 = 특성 노드
3 = 텍스트 노드
localname nvarchar 요소 또는 특성의 로컬 이름을 지정합니다. NULL DOM 개체에 이름이 없으면 입니다.
prefix nvarchar 노드 이름의 네임스페이스 접두사입니다.
namespaceuri nvarchar 노드의 네임스페이스 URI입니다. 값이 NULL면 네임스페이스가 없습니다.
data type nvarchar 요소 또는 특성 행의 실제 데이터 형식이고, 그렇지 않으면 .입니다 NULL. 데이터 형식은 인라인 DTD 또는 인라인 스키마로부터 추정할 수 있습니다.
prev bigint 이전 형제 요소의 XML ID입니다. NULL 직접 이전 형제가 없는 경우
text ntext 텍스트 형식의 특성 값 또는 요소 콘텐츠를 포함합니다(또는 NULL 에지 테이블 항목에 값이 필요하지 않은 경우).

예제

A. OPENXML에서 기본 SELECT 문 사용

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

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

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

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

DECLARE @idoc INT, @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

매핑을 나타내는 element-centric 플래그사용하여 2동일한 SELECT 문을 실행하는 경우 XML 문서에 이름이 지정 CustomerID 되거나 ContactName XML 문서에 있는 요소가 없기 때문에 XML 문서의 두 고객 값 CustomerID ContactName 이 모두 반환NULL됩니다.

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

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

B. 열과 XML 특성 간의 매핑에 ColPattern 지정

다음 쿼리는 XML 문서에서 고객 ID, 주문 날짜, 제품 ID, 수량 특성을 반환합니다. rowpattern<OrderDetails> 요소를 식별합니다. ProductIDQuantity<OrderDetails> 요소의 특성입니다. 그러나 OrderID, CustomerIDOrderDate는 부모 요소 <Orders>의 특성입니다.

선택적 ColPattern이 지정되는 매핑은 다음과 같습니다.

  • 행 집합의 OrderID, CustomerIDOrderDate는 XML 문서에서 rowpattern으로 식별된 노드의 부모 특성에 매핑됩니다.

  • 행 집합의 ProdID 열은 ProductID 특성에 매핑되며, 행 집합의 Qty 열은 rowpattern으로 식별된 노드의 Quantity 특성에 매핑됩니다.

매핑은 element-centric flags 매개 변수에 의해 지정되지만 ColPattern지정된 매핑은 이 매핑을 덮어씁니다.

DECLARE @idoc INT, @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">v
   <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

C. 에지 테이블 형식으로 결과 가져오기

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

OPENXML 문에서 rowpattern(/ROOT/Customers)은 처리할 <Customers> 노드를 식별합니다. 절이 WITH 제공되지 OPENXML 않으므로 에지 테이블 형식으로 행 집합을 반환합니다.

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

DECLARE @idoc INT, @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;