Using The XML Data Type

Good afternoon everyone!

SQL Server 2008 sees the introduction of XML data types which will allow you to store your XML documents and the purpose of today's blog post is to explain how to implement XML data types and use them within your database queries.

Typed or Untyped XML

XML data types can be either typed or untyped. Typed means that an XML schema collection has been assigned to the type in order to check the contents.

The code below (idea taken from Microsoft SQL Server 2008 - Database Developer Training Kit: MCTS Exam 70-433 p 275, ISBN: 978-0-7356-2639-3) gives an example of a typed and untyped XML variable in a created database:

Untyped XML variables declared in a new table can look as simple as this:

However, typed XML variables also need to reference a schema collection that has to be defined first:

Schema Definition:

Then the Typed XML variable declared in a new table will look like this:

Note that the way that the Typed and Untyped XML data types are stored in the back end is completely different.  So that means if the data structure changes you will need to make changes to the schema collection too, which involves a lot of work both for SQL Server itself and for you as the developer as you will need to convert all typed data to untyped and then back again to make changes.

Querying and Modifying Data in XML Data Types

EXIST

The EXIST method returns a bit value and is used to determine whether the search criteria entered by the user in the query is found within an XML instance. Note that you may have to use conversions when using untyped XML in order to find the desired search criteria. This wouldn't apply to typed XML as all details of data types are stored within the XML schema.

VALUE

The VALUE method returns a scalar value, such as the result from a COUNT or a SUM query.  Note that you will have to use conversions in order to return the result in the correct format (for example, COUNT and SUM would return an INT value).

NODES

The NODES method is used to bring back the XML data into table form. This can be incredibly useful for passing multiple values in one single parameter.

QUERY

The QUERY method allows you to return a fragment of an XML document and then style it however you want. The keywords typically used in order to do this are:

• FOR
• LET
• RETURN
• ORDER BY
• WHERE

What Next...

Next we will be discussing what SQLCLR and Filestream is. This is a very technical subject therefore I will only be covering the absolute basics at this stage. I won't be including any code just yet, it'll just be a pure explanation however in the future I may go into more detail, depending on other factors.

As A Final Word...

Thank you for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there.