- 전체
- Sample DB
- database modeling
- [표준 SQL] Standard SQL
- G-SQL
- 10-Min
- ORACLE
- MS SQLserver
- MySQL
- SQLite
- postgreSQL
- 데이터아키텍처전문가 - 국가공인자격
- 데이터 분석 전문가 [ADP]
- [국가공인] SQL 개발자/전문가
- NoSQL
- hadoop
- hadoop eco system
- big data (빅데이터)
- stat(통계) R 언어
- XML DB & XQuery
- spark
- DataBase Tool
- 데이터분석 & 데이터사이언스
- Engineer Quality Management
- [기계학습] machine learning
- 데이터 수집 및 전처리
- 국가기술자격 빅데이터분석기사
- 암호화폐 (비트코인, cryptocurrency, bitcoin)
XML DB & XQuery XML 문서 가져오기 및 XQuery 쿼리 예제
2016.11.29 20:26
XML 문서 가져오기 및 XQuery 쿼리 예제
SQL Server 2012 에서 XML 문서를 테이블에 Bulk Insert 하는 방법과 XQuery 를 사용하여 XML 데이터를 검색하는 몇 가지 예제를 아래와 같이 정리하였습니다.
1. XML 원본 문서를 테이블에 Bulk Insert 하는 방법
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 데이터 검색 예제
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
[출처] http://laigo.kr/723
XML 문서 가져오기 및 XQuery 쿼리 예제
SQL Server 2012 에서 XML 문서를 테이블에 Bulk Insert 하는 방법과 XQuery 를 사용하여 XML 데이터를 검색하는 몇 가지 예제를 아래와 같이 정리하였습니다.
1. XML 원본 문서를 테이블에 Bulk Insert 하는 방법
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 데이터 검색 예제
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
[출처] http://laigo.kr/723
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.
