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 |
[참고자료]
- sp_xml_preparedocument
https://msdn.microsoft.com/ko-kr/library/ms187367(v=SQL.120).aspx
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp