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.

Good afternoon everyone! I hope you all had a great weekend!

This blog post is an introduction to the basics of XML within SQL Server. XML in itself is a very large topic, however I am only going to be covering the basics for now and what will be required in the MCTS 70-433 exam. At a later date I will probably expand on this as my knowledge increases.

What is XML?

XML stands for Extensible Markup Language, which is used to transport and store data, not necessarily display it. XML is used in a variety of situations in a database:

• To retrieve relational data as an XML document rather than a tabular display of results. 
• To pass data into the database as XML. 
• Storing and querying against an XML document in the database. 

XML can be created and stored in SQL as both a document, or as fragments.

SQL Server XML Modes (With Examples) 

There are various XML modes in SQL Server that you should be aware about: 

FOR XML RAW

FOR XML RAW is probably one of the easiest methods of implementing XML in SQL. It literally passes out all information that you request in a single line format, with one data row for each input in the database.

Query:

Tabular Result:

XML Result:

FOR XML AUTO

FOR XML AUTO is similar to XML raw, only it also supports hierarchy structures. However, it can only cope with 1 path of branches, which may be useful in some instances but not all.

Query:

Tabular Result:

XML Result:

FOR XML EXPLICIT

FOR XML EXPLICIT is probably the most complex of all the XML Modes. As you can see from the example below, it can be quite tricky to code and maintain, especially when you have many layers to the hierarchy. However, the formatted output does look quite nice and is easy enough to read.

Query:

Tabular Result:

XML Result:

FOR XML PATH

FOR XML PATH is arguably the most appropriate mode which covers the majority of solutions, plus it is easier to implement and maintain in comparison to XML EXPLICIT.

Query:

Tabular Result:

XML Result:

Nesting XML Queries 

It is also possible to nest your XML queries in order to produce a hierarchy and a complete XML document. 

What Next...

In SQL 2008 onwards, Micrososft have included an XML data type. Tomorrow's blog post will explain: 

• What the XML Data Type is.
• What the XML Data Type is used for. 
• How the XML Data Type can be used properly and appropriately within your database.

So stay tuned for that!

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.