Creating and Inserting XML data in SQL Server 2005/2008

With the XML data type introduced in SQL Server 2005/2008, storing XML data is quite simple as you no longer need to now store XML in the database as Binary formats.

Let's quickly see how to create a table with the XML data type and store an XML document in that table.

DECLARE @TT TABLE

(

ID int,

Name varchar(30),

Address XML

)

 

INSERT @TT

SELECT 1, 'Jason', CAST
('<Address Street1="342A NW AXE STREET" PIN="544333"/>' as
XML) UNION ALL

SELECT 2, 'Brooke', CAST
('<Address Street1="71H BRISBON" PIN="565533"/>' as XML)
UNION ALL

SELECT 3, 'Dally', CAST
('<Address Street1="R/F/3 MASON CORNER" PIN="699783"/>' as
XML)

 

SELECT * FROM @TT

As shown in the query above, we created the Address column with the XML data type and inserted XML data using CAST function. The CAST function also ensures that the XML document is well formed. If the XML was not well formed, as in this line shown below,

SELECT 3, 'Dally', CAST
('<Address Street1="R/F/3 MASON CORNER" PIN="699783">' as XML)

then an XML Parsing error would be raised - XML parsing: line 1, character 51, unexpected end of input

You can even add XML Schema definitions to SQL Server and use it to validate XML documents. We will see this feature in one of the forthcoming blog posts.

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook