데이터베이스 개발자 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
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1458
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1227
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1087
1727 SQL Server Trigger jevida(강성욱) 2016.09.14 930
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 846
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 911
» XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 940
1723 XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 1854
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1250
1721 SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1222
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2649
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1682
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 1702
1717 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5583
1716 DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1336
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 3653
1714 DMV를 이용한 SQL Server 성능 카운터 확인 jevida(강성욱) 2016.09.13 1745
1713 DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1239
1712 DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1254
1711 세션에 따른 캐시된 쿼리 플랜 설정 확인 jevida(강성욱) 2016.09.13 862





XE Login