OPEN XML

특징

• openxml은 테이블 뷰와 유사한 인메모리 로우셋을 제공한다
• openxml은 xml 데이터를 마치 관계형 로우셋처럼 접근할 수 있게 해준다.
• 로우셋은 데이터베이스 테이블에 저장할 수 있다.
• openxml을 사용하여 xml 문서에 대한 쿼리를 작성하려면 먼저 sp_xml_preparedocument를 호출해야한다. sp_xml_preparedocument은 xml 문서를 파싱하고 파싱된 문서를 사용할 수 있는 핸들을 반환한다. 그 문서 핸들은 openxml이 파싱한다.
• 메모리를 해제하려면 sp_xml_removedocument를 호출하여 xml 문서의 내부 표현을 메모리에서 제거해야한다.
• sp_xml_preparedocument, sp_xml_removedocument 둘 다 기본 저장 프로시저. 항상 쌍으로 다닌다.
• openxml은 Xpath 포멧의 인수를 받아서 원하는 데이터를 로우셋으로 돌려준다.
• @xmlDocument는 xml 타입 또는 (n)varchar(max) 타입이다.

어트리뷰트(속성) 중심 매핑 vs 엘리먼트(요소) 중심 매핑

어트리뷰트 중심 매핑

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

openxml flag 1

OPENXML(xmlDocumentHandle, Xpath, 1)

엘리먼트 중심 매핑

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

Openxml flag 2

OPENXML(xmlDocumentHandle, Xpath, 2)

엘리먼트 중심 매핑에서 flag를 1로 하거나, 프로퍼티 중심 매핑에서 flag를 2로 하면 결과가 null이 나온다.

엘리먼트 중심 매핑과 어트리뷰트 중심 매핑 결합

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

Openxml flag 3

속성 중심 매핑이 먼저 적용된 다음 처리되지 않은 모든 열에 대해 요소중심 매핑이 적용된다.

에지 테이블

다음과 같은 컬럼을 가진 테이블

Xpath

• 엘리먼트로 지정할 때 /
• 어트리뷰트로 지정할 때 @
• 현재 어트리뷰트 '.'

개념

• rowpattern: 전제 xml 데이터 중 불러올 행집합을 결정하는 xpath.
• colpattern: rowset 컬럼과 xml 엘리먼트, 어트리뷰트를 매핑하기위한 xpath(with절 뒤에 옴). flag에 의행 설정된 매핑을 덮어쓸 수 있다.
• colpattern vs rowpattern 예시

SELECT *  
FROM OPENXML (@DocHandle, '/ROOT/Customer',1)   //rowpattern
      WITH (CustomerID  varchar(10),  //colpattern
            ContactName varchar(20))  //colpattern

Reference: MicroSoft Docs