Search content within the blog

Thursday, May 28, 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.



ID int,

Name varchar(30),

Address XML



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

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

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




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.

No comments:

Post a Comment