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 트리를 보여 줍니다.
참고 항목
참조
OPENXML을 사용하여 XML 쿼리
FOR XML을 사용하는 XML 생성
FOR XML 절의 기본 구문