SQL Server/SQL Server Tip

XML로 생성된 정보를 테이블로 저장하기(sp_xml_preparedocument)

SungWookKang 2015. 8. 12. 10:16
반응형

XML로 생성된 정보를 테이블로 저장하기(sp_xml_preparedocument)

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

 

XML로 작성된 정보를 파싱하여 테이블로 저장하는 방법에 대해서 살펴본다. SQL Server에서제공되는 sp_xml_preparedocument 프로시저와 OPENXML 함수 기능을 함께 사용하여 편리하게 XML데이터를 사용할 수 있다.

 

sp_xml_preparedocument는 입력으로 제공되는 XML 텍스트를 읽고 MSXML 파서(Msxmlsql.dll)을 사용하여 텍스트의 구문을 분석하여 사용할 수 있는 상태로 제공한다. sp_xml_preparedocument는 XML문서의 새로 생성된 내부 표현에 액세스하는데 사용할 수 있는 핸들을 반환하고 이 핸들은 sp_xml_removedocument를 실행하여 무효화한 경우를 제외하고는 세션이 유지 되는동안 유효하다.

 

구문 분석된 문서는 SQL Server 내부캐시에 저장되며 MSXML 파서는 SQL Server에서 사용할 수 있는 총 메모리의 1/8을 사요한다. 메모리 부족을 방지하려면 sp_xml_removedocument를 실행하여 메모리를 확보해야한다.

 

참고로 sp_xml_preparedocument는 한번에 열수 있는 요소 수를 최대 256개로 제한되어 있다.

 

다음과 같이 XML로 생성된 파일이 있을 때 OPENROWSET 명령을 사용하여 가져온다.

 

CREATE TABLE XMLwithOpenXML

(

Id INT IDENTITY PRIMARY KEY,

XMLData XML,

LoadedDateTime DATETIME

)

 

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)

SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()

FROM OPENROWSET(BULK 'C:\Test.xml', SINGLE_BLOB) AS x;

 

SELECT * FROM XMLwithOpenXML

 

 

XMLData 컬럼의 링크를 클릭하면 테이블로 로드된 XML정보를 확인할 수 있다.

 

XML 데이터가 로드되었으면 OPENXML 함수와 sp_xml_preparedocument를 사용하여 데이터를 파싱한다. 정상적으로 파싱된 데이터가 반환되면 INSERT 구문을 추가하여 데이터를 테이블로 저장하여 사용할 수 있다.

USE sw_test

GO

 

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

 

SELECT @XML = XMLData FROM XMLwithOpenXML

 

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

 

SELECT ponumber, podate

FROM OPENXML(@hDoc, 'root/polist/po')

WITH

(

ponumber int 'ponumber',

podate datetime 'podate'

)

 

EXEC sp_xml_removedocument @hDoc

GO

 

 

 

[참고자료]

 

 

2015-08-12 / 강성욱 / http://sqlmvp.kr

 

 

MSSQL, SQL Server, SQL XML, XML 저장, XML 파싱, DB XML

반응형