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

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;

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
*/

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;

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
*/