XML In SQL Server Part 1: Storing XML In SQL Server

There times when you have to store data as XML in a SQL Server database table.  In this blog we will go over how to store XML as data in SQL Server.  There's an xml data type in SQL Server that we can use to store XML data.

Example: Create a database table that contains a column to store XML data using the xml data type

CREATE TABLE Books
(
  Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  Book XML NOT NULL
);

If you look at the "Book" column for the table "Books" you will see that it has a data type of XML



Now that we have our table set up, we can insert XML data to into the table

INSERT INTO Books(Book)
VALUES(
 CAST ( '<book>
  <author>Bill King</author>
  <title>ACME Consulting: An Inside Look</title>
  <publisher>ACME Publishing</publisher>
  <language>Swahili</language>
 </book>' AS XML));

In the example above we CAST the type to XML first before we insert the data into the Books column because we want to make sure that the data we are inserting into the column is a well-formed XML data. If you query the table now you will see that there's one record with XML data in the "Book" column