May 27, 2009

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.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

0 Responses to "Creating and Inserting XML data in SQL Server 2005/2008"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions