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://msdn.microsoft.com/ko-kr/library/ms186918.aspx
http://msdn.microsoft.com/ko-kr/library/ms190353.aspx
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
