OPENXML(Transact-SQL)
OPENXML은 XML 문서에 대한 행 집합 뷰를 제공합니다. OPENXML이 행 집합 공급자이므로 테이블, 뷰 또는 OPENROWSET 함수 등의 행 집합 공급자가 있을 수 있는 Transact-SQL 문에 OPENXML을 사용할 수 있습니다.
.gif "항목 링크 아이콘")
구문
OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]
인수
- idoc
XML 문서의 내부 표현 문서 핸들입니다. XML 문서의 내부 표시는 sp_xml_preparedocument를 호출하여 만듭니다.
- rowpattern
idoc 매개 변수로 핸들을 전달하는 XML 문서에서 행으로 처리될 노드를 나타내기 위해 사용하는 XPath 패턴입니다.
flags
XML 데이터와 관계형 행 집합 사이에 사용해야 하는 매핑과 남는 열을 채우는 방법을 나타냅니다. flags는 선택적 입력 매개 변수이며 다음 값 중 하나가 될 수 있습니다.바이트 값 설명 0
기본적으로 특성 중심 매핑을 사용합니다.
1
특성 중심 매핑을 사용합니다. XML_ELEMENTS와 결합할 수 있습니다. 이 경우 특성 중심 매핑이 먼저 적용된 다음 아직 처리되지 않은 모든 열에 대해 요소 중심 매핑이 적용됩니다.
2
요소 중심 매핑을 사용합니다. XML_ATTRIBUTES와 결합할 수 있습니다. 이 경우 특성 중심 매핑이 먼저 적용된 다음 아직 처리되지 않은 모든 열에 대해 요소 중심 매핑이 적용됩니다.
8
XML_ATTRIBUTES 또는 XML_ELEMENTS와 결합(논리적 OR 연산을 수행)할 수 있습니다. 검색 상황에서 이 플래그는 소비된 데이터를 오버플로 속성인 @mp:xmltext로 복사할 수 없음을 나타냅니다.
SchemaDeclaration
다음 형식의 스키마 정의입니다. ColNameColType [ColPattern | MetaProperty] [**,**ColNameColType [ColPattern | MetaProperty]...]- ColName
행 집합의 열 이름입니다.
- ColType
행 집합에 있는 열의 SQL Server 데이터 형식입니다. 열 형식이 특성의 기본 xml 데이터 형식과 다른 경우에는 형식 강제 변환이 발생합니다.
ColPattern
선택 사항이며 XML 노드를 열에 매핑하는 방법을 설명하는 일반 XPath 패턴입니다. ColPattern을 지정하지 않은 경우에는 기본 매핑(flags에 의해 지정된 특성 중심 또는 요소 중심 매핑)이 사용됩니다.ColPattern으로 지정된 XPath 패턴은 flags에 의해 지정된 기본 매핑을 개선하거나 덮어쓰도록 매핑(특성 중심 및 요소 중심 매핑)의 특성을 지정하는 데 사용합니다.
ColPattern으로 지정된 일반 XPath 패턴은 메타 속성도 지원합니다.
- MetaProperty
OPENXML이 제공하는 메타 속성 중 하나입니다. MetaProperty를 지정한 경우 열에 메타 속성이 제공하는 정보가 포함됩니다. 메타 속성을 통해 상대적 위치 및 네임스페이스 정보 등 XML 노드에 대한 정보를 추출할 수 있습니다. 텍스트 형태로 표시되는 것보다 많은 정보를 제공합니다.
- ColName
- TableName
원하는 스키마가 있는 테이블이 이미 존재하고 열 패턴이 필요하지 않을 때 SchemaDeclaration 대신 지정할 수 있는 테이블 이름입니다.
주의
WITH 절은 SchemaDeclaration을 사용하거나 기존 TableName을 지정하여 행 집합 형식과 필요한 경우 추가 매핑 정보를 제공합니다. 선택적인 WITH 절을 지정하지 않은 경우에는 결과가 edge 테이블 형식으로 반환됩니다. edge 테이블은 세부 수준의 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 |
요소 또는 특성의 로컬 이름을 지정합니다. DOM 개체에 이름이 없는 경우에는 NULL입니다. |
prefix |
nvarchar |
노드 이름의 네임스페이스 접두사입니다. |
namespaceuri |
nvarchar |
노드의 네임스페이스 URI입니다. 값이 NULL이면 네임스페이스가 없는 것입니다. |
datatype |
nvarchar |
요소 또는 특성 행의 실제 데이터 형식이며 데이터 형식이 아닐 경우에는 NULL입니다. 데이터 형식은 인라인 DTD 또는 인라인 스키마로부터 추정할 수 있습니다. |
prev |
bigint |
이전의 형제 요소에 대한 XML ID입니다. 바로 이전의 형제가 없으면 NULL입니다. |
text |
ntext |
특성 값 또는 요소 내용을 텍스트 형식으로 포함합니다. edge 테이블 항목에 값이 필요 없는 경우에는 NULL입니다. |
예
1. 단순 SELECT 문에 OPENXML 사용
다음 예는 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
2. 열과 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특성에 매핑됩니다.
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
3. edge 테이블 형식으로 결과 얻기
다음 예의 예제 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