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

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

 

 

 

[참고자료]

• https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/
• sp_xml_preparedocument

  https://msdn.microsoft.com/ko-kr/library/ms187367(v=SQL.120).aspx

 

 

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

 

 

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