FOR XML 및 OPENXML을 사용하여 XML 데이터 게시 및 처리
SQL 쿼리를 실행하여 결과를 표준 행 집합이 아닌 XML로 반환할 수 있습니다. 이러한 쿼리는 직접 실행하거나 저장 프로시저 및 사용자 정의 함수 내에서 실행할 수 있습니다. 결과를 직접 검색하려면 먼저 SELECT 문의 FOR XML 절을 사용합니다. 그런 다음 FOR XML 절 내에 XML 모드 중 RAW, AUTO, EXPLICIT 또는 PATH를 지정합니다.
예를 들어 다음 SELECT 문은 AdventureWorks 데이터베이스의 Sales.Customer 및 Sales.SalesOrderHeader 테이블에서 정보를 검색합니다. 다음 쿼리는 FOR XML 절에 AUTO 모드를 지정합니다.
USE AdventureWorks
GO
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerType
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을 테이블로 조각화한다라고도 합니다.
다음 예에서는 두 개의 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_pareparedocument에 의해 생성된 이전 XML 문서의 구문 분석된 XML 트리를 보여 줍니다.
.gif "구문 분석된 XML 트리")
참고 항목
참조
OPENXML을 사용하여 XML 쿼리
FOR XML을 사용하는 XML 생성
FOR XML 절의 기본 구문