This is a homework I did for my masters degree (still studying). Unfortunately, studying after my normal work hours doesn't give me much time to post. Therefore, this post has only short comments. Hopefully, it 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 records - 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
The last statement leads to the following error message:
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 Book.Athors.query('/.') AS AuthorsList
FROM XMLBooks.nodes('/books/book/authors') Book(Authors)
Happy coding!