That's the work I did for a homework at school (still studying, hoping to finish my Masters degree). Sorry, I have only short comments for it, hopefully will be helpful for somebody!
/*creating a table for demonstration purposes
ID - identity field
Books - XML document where the tables are stored
*/
CREATE TABLE XMLBooks(
ID int IDENTITY(1,1) PRIMARY KEY
, Books XML)
/*dropping the table */
--DROP TABLE XMLBooks
/* inserting a record - an XML document containing one book*/
INSERT INTO XMLBooks
VALUES ('<books>
<book id="1" language="EN">
<title>Inside SQL Server 2005 Tools</title>
<authors>
<author>Michael Raheem</author>
<author>Dima Sonkin</author>
<author>Thierry D''Hers</author>
<author>Kami LeMonds</author>
</authors>
<year>2006</year>
<publisher>Addison Wesley</publisher>
<price></price>
<pages>816</pages>
<isbn10>0-321-39796-7</isbn10>
<isbn13>978-0-321-39796-6</isbn13>
</book>
</books>')
/* inserting a record - an XML document containing three books*/
INSERT INTO XMLBooks
VALUES ('<books>
<book id="2" language="EN">
<title>Core C# and .NET</title>
<authors>
<author>Stephen C. Perry</author>
</authors>
<year>2005</year>
<publisher>Prentice Hall</publisher>
<price></price>
<pages>1008</pages>
<isbn10>0-13-147227-5</isbn10>
<isbn13></isbn13>
</book>
<book id="3" language="EN">
<title> Microsoft SQL Server 2005</title>
<authors>
<author>Andrew J. Brust</author>
<author>Stephen Forte</author>
</authors>
<year>2006</year>
<publisher>Microsoft Press</publisher>
<price></price>
<pages>600</pages>
<isbn10>0-7356-1923-9</isbn10>
<isbn13>978-0-7356-1923-4</isbn13>
</book>
<book id="4" language="EN">
<title>MCAD/MCSD: Visual Basic .NET XML Web Services and Server Components Study Guide</title>
<authors>
<author>Pamela Fanstill</author>
<author>Brian Reisman </author>
<author>Mitch Ruebush</author>
</authors>
<year>2003</year>
<publisher>Sybex</publisher>
<price></price>
<pages>598</pages>
<isbn10>0-7821-4193-5</isbn10>
<isbn13></isbn13>
</book>
</books>')
/* a classical SELECT */
SELECT *
FROM XMLBooks
/* a first XML falvoured query*/
SELECT Books.query('/.')
FROM XMLBooks
/* selecting an element's content*/
SELECT Books.query('//book[@id=1]/title/text()')
FROM XMLBooks
/* selecting an element's content*/
SELECT Books.value('data(//book[@id=1]/title/)','varchar(250)')
FROM XMLBooks
Msg 2256, Level 16, State 1, Line 2
XQuery [XMLBooks.Books.value()]: Syntax error near ')', expected a "node test".
/* selecting an element's content*/
SELECT Books.value('data((//book[@id=1]/title)[1])','varchar(250)')
FROM XMLBooks
/* selecting only the occurence*/
SELECT Books.value('data((//book[@id=1]/title)[1])','varchar(250)')
FROM XMLBooks
WHERE Books.exist('//book[@id=1]')=1
/* updating a node by inserting a new child after an existing one */
UPDATE XMLBooks SET Books.modify('
insert
Jack Daniels
after (//book[@id=1]//author)[1]')
/* updating a node */
UPDATE XMLBooks SET Books.modify('
insert
Jack Daniels
before (//book[@id=1]//author)[1]')
/* deleting a child */
UPDATE XMLBooks SET Books.modify('
delete //book[@id=1]//author[1]')
/* modifying an attribute */
UPDATE XMLBooks SET Books.modify('
replace value of (//book/@id)[1]
with "100"')
/* deleting a record */
DELETE
FROM XMLBooks
WHERE Books.exist('//book[@id=1]')=1
SELECT Motocross.Team.query(‘.’)
AS RESULT
FROM @xmlvar.nodes(‘/Motocross/Team’) Motocross(Team)
SELECT Book.Athors.query('/.') AS AuthorsList
FROM XMLBooks.nodes('/books/book/authors') Book(Authors)
No comments:
Post a Comment