Since SQL2005 the ability to store entire XML documents within a database field has been achievable using the xml data type.  In this article I’m going to demonstrate how easy it is to create, store and retrieve an XML document from within SQL2008.

--Create a new table

CREATE TABLE BooksXML (
  BookID int PRIMARY KEY,
  BookXml xml NOT NULL)

Once the table has been created we need to add some basic information and insert it into the table.

--Insert two XML fragments

DECLARE @xml AS XML
SET @xml = '
<Books>
 <Book ID="1"
   BookTitle="Where Eagles Dare"
   BookAuthor="Alistair MacClean"
   BookCost="£9.99">
 </Book>
</Books>'

--insert into the table
INSERT INTO BooksXML (BookID, BookXml) VALUES (1, @xml)

SET @xml = '
<Books>
 <Book ID="2"
   BookTitle="Marker"
   BookAuthor="Robin Cook"
   BookCost="£8.99">
</Books>'

--insert into the table
INSERT INTO BooksXML (BookID, BookXml) VALUES (2, @xml)

Your XML must be well formed when inserting into the database, any attempt to insert malformed XML will result in a regular XML error message.

Once we have stored the data, you will want to get at it again to display it.  If you want to find a specific book title you could use the exist function as below.

--Retrieve the book Marker by its name.

SELECT * FROM BooksXML
WHERE BookXml.exist('Books/Book[@name="Marker"]') = 1

And that is it, you’ve stored XML, retrieved XML of a specific value, by delving into the XML stored within the SQL Server database.