XML nodes() 함수를 이용한 OPENXML 교체

XML nodes() 함수를 이용한 OPENXML 교체

 

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

 

OPENXML은 XML문서에 대한 행 집합 뷰를 제공한다. OPENXML이 행 집합 공급자이므로 테이블, 뷰 또는 OPENROWSET 함수 등의 행 집합 공급자가 있을 수 있는 T-SQL 문에 OPENXML을 사용 할 수 있다.

 

XML 문서의 내부 표시는 sp_xml_preparedocumnet 를 호출하여 만들 수 있다.

 

실습을 위해 간단히 XML 문서를 정의 한다.

<Polist> <po><poNumber>100</poNumber><PoDate>2012-01-01</PoDate></po> <po><poNumber>200</poNumber><PoDate>2012-02-06</PoDate></po> </Polist>

 

 

[SELECT 문에 OPENXML 사용]

Sp_xml_preparedocument를 사용하여 XML 이미지의 내부 표현을 만든다. OPENXML 행 집합 공급자를 사용하는 SELECT문은 XML 문서의 내부 표현에 대해 실행 된다.

Sp_xml_preparedocumnet프로시저는 MSXML파서에 의해 사용 된다. SQL Server가 모든 메모리를 사용하지 않도록 사용 즉시 XML문서와 함께 sp_xml_removedocumnet저장프로시저를 호출해야 한다.

DECLARE @DocHandle int DECLARE @XmlDocument nvarchar(1000)   SET @XmlDocument = N'<Polist> <po><poNumber>100</poNumber><PoDate>2012-01-01</PoDate></po> <po><poNumber>200</poNumber><PoDate>2012-02-06</PoDate></po> </Polist>'   EXEC sp_xml_preparedocument @DocHandle Output, @XmlDocument   select * from OPENXML (@DocHandle, '/Polist/po', 2) with (poNumber nvarchar(10), PoDate datetime)   exec sp_xml_removedocument @DocHandle

 

 

 

[코드 스니펫 형식] (SQL Server 2005 이상)

XML Column 형식 노드를 행 집합으로 XML로 변환하여 사용한다.

노드의 Doc(col) 이 테이블 컬럼의 별칭으로 사용 된다.

DECLARE @Xml xml   SET @Xml = N'<Polist> <po><poNumber>100</poNumber><PoDate>2012-01-01</PoDate></po> <po><poNumber>200</poNumber><PoDate>2012-02-06</PoDate></po> </Polist>'   select     doc.col.value('poNumber[1]', 'nvarchar(10)') as poNumber,     doc.col.value('PoDate[1]', 'datetime') as PoDate from @Xml.nodes('/Polist/po')doc(col)

 

 

 

문서 속성 대신 요소를 사용한 예제 이다. poNumber[1] 대신 @poNumber을 사용하였다.

DECLARE @Xml xml   SET @Xml = N'<Polist> <po poNumber = "100" PoDate = "2012-01-01"/> <po poNumber = "200" PoDate = "2012-02-06"/> </Polist>'   select     doc.col.value('@poNumber', 'nvarchar(10)') as poNumber,     doc.col.value('@PoDate', 'datetime') as PoDate from @Xml.nodes('/Polist/po')doc(col)

 

 

 

XML에 대한 자세한 내용은 아래 링크를 참고 하자.

 

[참고 링크]

http://www.mssqltips.com/sqlservertip/1609/replacing-openxml-with-the-xml-nodes-function-in-sql-server-2005/

http://msdn.microsoft.com/ko-kr/library/ms186918.aspx

http://msdn.microsoft.com/ko-kr/library/ms190353.aspx

 

 

 

2013-02-06 / 강성욱 / http://sqlmvp.kr