아티클
04/01/2012

FOR XML 및 OPENXML을 사용하여 XML 데이터 게시 및 처리

SQL 쿼리를 실행하여 결과를 표준 행 집합이 아닌 XML로 반환할 수 있습니다. 이러한 쿼리는 직접 실행하거나 저장 프로시저 및 사용자 정의 함수 내에서 실행할 수 있습니다. 결과를 직접 검색하려면 먼저 SELECT 문의 FOR XML 절을 사용합니다. 그런 다음 FOR XML 절 내에 XML 모드로 RAW, AUTO, EXPLICIT 또는 PATH를 지정합니다.

예를 들어 다음 SELECT 문은 AdventureWorks2008R2 데이터베이스의 Sales.Customer 및 Sales.SalesOrderHeader 테이블에서 정보를 검색합니다. 다음 쿼리는 FOR XML 절에 AUTO 모드를 지정합니다.

USE AdventureWorks2008R2
GO
SELECT Cust.CustomerID, 
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID, 
       OrderHeader.Status
FROM Sales.Customer Cust 
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID
FOR XML AUTO

FOR XML 절을 사용하여 데이터를 XML 문서로 검색할 수 있는 반면 Transact-SQL OPENXML 함수를 사용하면 XML 문서로 표현되는 데이터를 삽입할 수 있습니다. OPENXML은 테이블이나 뷰와 비슷한 행 집합 공급자이며 메모리에 있는 XML 문서에 행 집합을 제공합니다. OPENXML을 사용하면 XML 문서의 내부 표현에 대한 행 집합 뷰를 제공하여 마치 관계형 행 집합인 것처럼 XML 데이터에 액세스할 수 있습니다. 행 집합의 레코드는 데이터베이스 테이블에 저장할 수 있습니다. OPENXML은 원본 테이블이나 뷰를 지정할 수 있는 SELECT 및 SELECT INTO 문에서 사용할 수 있습니다.

다음 예에서는 INSERT 및 SELECT 문에서 OPENXML을 사용하는 방법을 보여 줍니다. 예제 XML 문서에 <Customers> 및 <Orders> 요소가 들어 있습니다.

우선 sp_xml_preparedocument 저장 프로시저가 XML 문서를 구문 분석합니다. 구문 분석된 문서는 XML 문서의 노드(요소, 특성, 텍스트 및 주석)를 트리로 표시합니다. 그런 다음 OPENXML에서 이 구분 분석된 XML 문서를 참조하고 해당 XML 문서의 일부 또는 전체에 대한 행 집합 뷰를 제공합니다. OPENXML을 사용하는 INSERT 문은 그러한 행 집합의 데이터를 데이터베이스 테이블에 삽입할 수 있습니다. OPENXML을 여러 번 호출하면 XML 문서의 여러 부분에 대한 행 집합 뷰를 제공하고, XML 문서의 여러 부분을 다른 테이블에 삽입하는 등 다양하게 처리할 수 있습니다. 이 프로세스는 XML을 테이블로 조각화한다라고도 합니다.

다음 예에서는 두 개의 INSERT 문을 사용하여 <Customers> 요소를 Customers 테이블에 저장하고 <Orders> 요소를 Orders 테이블에 저장하는 방식으로 XML 문서가 조각화됩니다. 또한 XML 문서에서 CustomerID 및 OrderDate를 검색하는 OPENXML이 포함된 SELECT 문을 보여 줍니다. 이 과정의 마지막 단계에서 sp_xml_removedocument를 호출합니다. 이 작업은 구문 분석 단계 중에 생성된 내부 XML 트리를 표시하는 데 할당된 메모리를 해제하기 위해 수행합니다.

-- Create tables for later population using OPENXML.
CREATE TABLE Customers (CustomerID varchar(20) primary key,
                ContactName varchar(20), 
                CompanyName varchar(20))
GO
CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime)
GO
DECLARE @docHandle int
DECLARE @xmlDocument nvarchar(max) -- or xml type
SET @xmlDocument = N'<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers 
SELECT * 
FROM OPENXML(@docHandle, N'/ROOT/Customers') 
  WITH Customers
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders 
SELECT * 
FROM OPENXML(@docHandle, N'//Orders') 
  WITH Orders
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders') WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @docHandle

다음 그림에서는 sp_xml_preparedocument에 의해 생성된 이전 XML 문서의 구문 분석된 XML 트리를 보여 줍니다.

구문 분석된 XML 트리