SQL Server/SQL Server Tip

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

SungWookKang 2015. 7. 20. 11:20
반응형

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

 

반응형