XML Basics in SQL Server 2005 and 2008

In this article, we will see how to write basic queries to insert, query and generate XML data in SQL Server 2005/2008. For this demonstration, I am using the ‘Northwind’ database.

The first step is to start ‘SQL Server Management Studio’ and start a new query window. Then use the following clauses

1. FOR XML AUTO – This clause returns a simple, nested XML tree result.

SELECT * FROM Customers FOR XML AUTO

2. FOR XML RAW – This clause returns a simple, nested XML tree result by transforming each row in an <ROW/> Element.

SELECT * FROM Customers FOR XML RAW

3. FOR XML AUTO, ELEMENTS – This clause returns a XML result by specifying columns as sub elements.

SELECT * FROM Customers FOR XML AUTO,ELEMENTS

4. FOR XML AUTO, ELEMENTS, TYPE – This clause returns a XML result by specifying columns as sub elements and ‘TYPE’ specifies that it returns result as XML type which we can store in XML data type in SQL Server.

SELECT * FROM Customers FOR XML AUTO, ELEMENTS, TYPE

In SQL Server 2005, Microsoft has introduced a new data type ‘XML’. We can use this data type to store well formed as well as valid xml in our tables. Let’s see a few examples of the same –

Let’s first create a table which will use XML data type as shown in the following script –

Now let’s insert few rows in our table. For inserting the data in the above table, we will define a variable with ‘XML Data Type’ as shown below –



Now let’s write a select statement which will fetch the inserted data –

SELECT * FROM CustomerProducts

Now if you check the result, it should look like the following –

Now let’s write some queries which will test the XML data type. Let’s insert a record into our ‘CustomerProducts’ table which is not well formed, as shown below –

If you check the above XML, it does not having closing tag for <ProductID>. So the result will be as shown below –



Now let’s see how to validate the XML data using XML Schemas. Let’s drop the existing table we created above.

DROP TABLE CustomerProducts

Now create a schema for XML data validation as shown below –

CREATE XML SCHEMA COLLECTION ProductSchema AS'
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.microsoft.com/schemas/northwind/products"
xmlns:prod="http://www.microsoft.com/schemas/northwind/products">
<xs:element name="Product">
<xs:complexType>
<xs:sequence>
<xs:element ref="prod:ProductID" />
<xs:element ref="prod:ProductName" />
<xs:element ref="prod:SupplierID" />
<xs:element ref="prod:CategoryID" />
<xs:element ref="prod:QuantityPerUnit" />
<xs:element ref="prod:UnitPrice" />
<xs:element ref="prod:UnitsInStock" />
<xs:element ref="prod:UnitsOnOrder" />
<xs:element ref="prod:ReorderLevel" />
<xs:element ref="prod:Discontinued" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="ProductID" type="xs:integer" />
<xs:element name="ProductName" type="xs:string" />
<xs:element name="SupplierID" type="xs:integer" />
<xs:element name="CategoryID" type="xs:integer" />
<xs:element name="QuantityPerUnit" type="xs:string" />
<xs:element name="UnitPrice" type="xs:double" />
<xs:element name="UnitsInStock" type="xs:integer" />
<xs:element name="UnitsOnOrder" type="xs:integer" />
<xs:element name="ReorderLevel" type="xs:integer" />
<xs:element name="Discontinued" type="xs:boolean" />
</xs:schema>'

To see all the available XML schemas execute below query –

SELECT * FROM sys.xml_schema_collections

The result is as shown below –



Now let’s create the CustomerProduct table once again with the XML data type which will take the address of the above schema, as shown below –



Now try to insert a record and you will get an exception –



The exception is thrown because we are not passing the ProductName. Now let’s insert the following record which matches our schema validations –

You can now query the data and you should see valid XML.

Summary – In this article, we have seen how to use XML for querying and inserting the data in the table. We saw some basic queries of XML while working with SQL Server.