OPENXML(Transact-SQL)

아티클
06/22/2024

적용 대상: 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-centric 및 element-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 문이 필수 열(이 경우에는 모든 열)을 검색하는 두 열(ContactName와 SELECT)로 된 행 집합을 만듭니다.

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> 요소를 식별합니다. ProductID 및 Quantity는 <OrderDetails> 요소의 특성입니다. 그러나 OrderID, CustomerID 및 OrderDate는 부모 요소 <Orders>의 특성입니다.

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

행 집합의 OrderID, CustomerID 및 OrderDate는 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;

예제: OPENXML 사용

다음을 통해 공유

OPENXML(Transact-SQL)

구문

인수

idoc

rowpattern

flags

SchemaDeclaration

TableName

설명

예제

A. OPENXML에서 기본 SELECT 문 사용

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

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

피드백

추가 리소스

다음을 통해 공유

OPENXML(Transact-SQL)

구문

인수

idoc

rowpattern

flags

SchemaDeclaration

TableName

설명

예제

A. OPENXML에서 기본 SELECT 문 사용

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

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

관련 콘텐츠

피드백

추가 리소스