April 23, 2011

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 –

image

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 –

image

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 –

image

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 –

image

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

image

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 –

image

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 –

image

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

image

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

image

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.


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



 
  Feedback:

comments

5 Responses to "XML Basics in SQL Server 2005 and 2008"
  1. Ankie said...
    April 23, 2011 at 3:40 AM

    I guess there is a small typo in the post. FORXML should be FOR XML (space required)

  2. Suprotim Agarwal said...
    April 23, 2011 at 3:43 AM

    Thanks Ankie. The typo has been fixed

  3. Pravinkumar said...
    April 23, 2011 at 6:10 AM

    Thanks Ankie !!

  4. enrique mallon said...
    June 10, 2014 at 10:09 PM
    This comment has been removed by the author.
  5. enrique mallon said...
    June 10, 2014 at 10:10 PM

    You must try sql recovery tool for XML data type of the SQL database from here:- http://www.sqlrepairtool.org/repair-mdf.html

 

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