Showing posts with label parsing. Show all posts
Showing posts with label parsing. Show all posts

13 September 2007

💎SQL Reloaded: XML on SQL Server 2005

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!
Related Posts Plugin for WordPress, Blogger...

About Me

My photo
Koeln, NRW, Germany
IT Professional with more than 24 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.