데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 

 

강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1733 SQL Server 그래픽 실행 계획 노드 정보 jevida(강성욱) 2016.09.14 1077
1732 프로파일러를 이용한 실행계획 캡처하기 jevida(강성욱) 2016.09.14 967
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3149
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1493
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1276
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1092
1727 SQL Server Trigger jevida(강성욱) 2016.09.14 948
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 855
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 917
» XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 977
1723 XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 1928
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1267
1721 SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1249
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2731
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1703
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 1831
1717 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5766
1716 DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1363
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 3998
1714 DMV를 이용한 SQL Server 성능 카운터 확인 jevida(강성욱) 2016.09.13 1821





XE Login