XML 문서 가져오기 및 XQuery 쿼리 예제

출처 : http://laigo.kr/723

SQL Server 2012 에서 XML 문서를 테이블에 Bulk Insert 하는 방법과 XQuery 를 사용하여 XML 데이터를 검색하는 몇 가지 예제를 아래와 같이 정리하였습니다.

 

 

1. XML 원본 문서를 테이블에 Bulk Insert 하는 방법

CREATE DATABASE XMLDB
GO

 

USE XMLDB
GO

 

CREATE TABLE TBL_XML (
 ID  INT IDENTITY PRIMARY KEY,
 XMLDATA XML 
)
GO

INSERT INTO TBL_XML(XMLDATA) 
SELECT * FROM OPENROWSET (
     BULK 'D:\SQL\BOOKS.XML', SINGLE_BLOB) AS x;

 

 

 

2. XQuery 를 사용하여 XML 데이터 검색 예제 

CREATE TABLE TBL_XQUERY (
 ID  INT IDENTITY PRIMARY KEY,
 XMLDATA XML 
)
GO

INSERT INTO TBL_XQUERY VALUES ('
 <CATALOG> 
  <book id="bk101">
   <author>Gambardella, Matthew</author>
   <title>XML Developer''s Guide</title>
   <genre>Computer</genre>
   <price>44.95</price>
   <publish_date>2000-10-01</publish_date>
   <description>An in-depth look at creating applications with XML.</description>
  </book> 
 </CATALOG>
')

INSERT INTO TBL_XQUERY VALUES ('
 <CATALOG> 
  <book id="bk102">
    <author>Ralls, Kim</author>
    <title>Midnight Rain</title>
    <genre>Fantasy</genre>
    <price>5.95</price>
    <publish_date>2000-12-16</publish_date>
    <description>A former architect battles corporate zombies, n evil sorceress, and her own childhood to become queen of the world.</description>
  </book>
 </CATALOG>
')

INSERT INTO TBL_XQUERY VALUES ('
 <CATALOG> 
    <book id="bk103">
    <author>Corets, Eva</author>
    <title>Maeve Ascendant</title>
    <genre>Fantasy</genre>
    <price>5.95</price>
    <publish_date>2000-11-17</publish_date>
    <description>After the collapse of a nanotechnology society in England, the young survivors lay the foundation for a new society.</description>
    </book>
 </CATALOG>
')

 

 

SELECT XMLDATA.query('/CATALOG/book')
FROM TBL_XQUERY
WHERE ID = 2
/*
<book id="bk102">
  <author>Ralls, Kim</author>
  <title>Midnight Rain</title>
  <genre>Fantasy</genre>
  <price>5.95</price>
  <publish_date>2000-12-16</publish_date>
  <description>A former architect battles corporate zombies, n evil sorceress, and her own childhood to become queen of the world.</description>
</book>
*/

 

SELECT XMLDATA.value ( '(/CATALOG/book/@id)[1]', 'char(5)' ) AS 'Book id'
FROM TBL_XQUERY
/*
bk101
bk102
bk103
*/

 

SELECT XMLDATA 
FROM TBL_XQUERY
WHERE XMLDATA.exist('/CATALOG/book[@id="bk103"]') = 1
/*
<CATALOG>
  <book id="bk103">
    <author>Corets, Eva</author>
    <title>Maeve Ascendant</title>
    <genre>Fantasy</genre>
    <price>5.95</price>
    <publish_date>2000-11-17</publish_date>
    <description>After the collapse of a nanotechnology society in England, the young survivors lay the foundation for a new society.</description>
  </book>
</CATALOG>
*/

 

SELECT 
 nCol.value('@id', 'char(5)') AS 'Book id', 
 nCol.value('(author)[1]', 'varchar(10)') AS author,
 nCol.value('(title)[1]', 'varchar(50)') AS title,
 nCol.value('(genre)[1]', 'varchar(10)') AS genre
FROM TBL_XQUERY 
CROSS APPLY XMLDATA.nodes('/CATALOG/book') AS nTbl(nCol)
ORDER BY ID
/*
bk101 Gambardell XML Developer's Guide Computer
bk102 Ralls, Kim Midnight Rain   Fantasy
bk103 Corets, Ev Maeve Ascendant   Fantasy
*/

 

 

 

[참고자료]

Examples of Bulk Import and Export of XML Documents (SQL Server)
http://msdn.microsoft.com/en-us/library/ms191184.aspx

 

XQuery 언어 참조(SQL Server)
http://msdn.microsoft.com/ko-kr/library/ms189075.aspx