Using XML within SQL Server 2008

During my work for the SQL certification (70-433 TS: Microsoft SQL Server 2008, Database Development), I found many difficult questions (difficult for me…) around the usage of XML within SQL server.

This article will not deal with all XML features that are available within SQL Server 2008, but just give some key points that I need to remember for the exam:

The XML Schema repository

For more information on the usage of XML Schema and XSD file format, you can just Google it; I found for example this article on Wikipedia.

You can add as many XML schemas in SQL server as you may need. To add a new XML Schema, the TSQL command is:

CREATE XML SCHEMA COLLECTION MyXmlSchema N'<?xml version="1.0" encoding="UTF-16"?>

<xsd:schema xmlns="..." ...>

....

</xsd:schema>'

Note also the availability of following queries:

ALTER XML SCHEMA COLLECTION MyXmlSchema N'...

DROP XML SCHEMA COLLECTION MyXmlSchema N'...

The xml Native Data Type

SQL Server 2005 supports a new native data type named xml that you use in exactly the same way as any other built-in data type

Typed and Un-typed xml Columns

When you provide a schema for the XML documents you will store, you can create a typed xml column in a table:

CREATE TABLE MyTable(MyKey int, MyXml xml(MyXmlSchema))

Without providing schema, you create an un-typed xml column:

CREATE TABLE MyTable(MyKey int, MyXml xml)

Inserting and Selecting on an xml Column

You can insert an XML document into an xml column in the same way as you would do for any other built-in data type. You can insert it as a string value, or use the CAST or CONVERT functions to specifically convert it to an xml type:

INSERT INTO MyTable(MyKey, MyXml)

      VALUES (1, '<FootBallClub id="1"><Name>Olympique de Marseille</Name><Position>1</Position></FootBallClub>')

INSERT INTO MyTable(MyKey, MyXml)

      VALUES (2, CAST('<FootBallClub id="2"><Name>Nimes Olympique</Name><Position>2</Position></FootBallClub>' AS xml))

INSERT INTO MyTable(MyKey, MyXml)

      VALUES (3, CONVERT(xml, '<FootBallClub id="3"><Name>Milan AC</Name><Position>100</Position></FootBallClub>'))

To extract an XML document from an xml column, you can convert it to nvarchar data type for example:

SELECT CONVERT(nvarchar(500), MyXml) as MyXmlDoc FROM MyTable

Querying XML in SQL server

XQuery support is available since SQL Server 2005. If you wish to have a tutorial about XQuery, you can go on the W3C Web Site

The following methods are available for the xml object in SQL Server:

• The query method, which returns a fragment of un-typed XML
• The value method, which returns a single value from the XML and exposes it as a standard (non-xml) SQL data type
• The exist method, which can be used to test whether a specific node/value exists in the XML data
• The modify method, which executes an XML Data Modification Language (XML-DML) statement
• The nodes function, which returns a single-column rowset of nodes from the XML

Using the query Method

These are example of SQL statements that query the table we just created:

SELECT MyXml.query('/FootBallClub[@id="1"]/Name') FROM MyTable

The result is:

<Name>Olympique de Marseille</Name>

To get just the value of the matching element, you can use the data function:

SELECT MyXml.query('data(/FootBallClub[@id="1"]/Name)') FROM MyTable  

In this case, the result would be just:

Olympique de Marseille

If the xml column is a typed column, you must specify the namespace:

SELECT MyXml.query('declare namespace s="http://blablabla"; ...

If you are familiar with FLWOR XQuery’s (For/Let/Where/Order by/Return), you can build an Xml document that contains all FootBallClub that have Id’s lower than 5.

Let’s add this document in a new row, of our table:

<FootBallClub id="1">

<Name>Olympique de Marseille</Name>

<Position>1</Position>

</FootBallClub>

<FootBallClub id="2">

<Name>Nimes Olympique</Name>

<Position>2</Position>

</FootBallClub>

<FootBallClub id="3">

<Name>Milan AC</Name>

<Position>100</Position>

</FootBallClub>

Here is the query

SELECT MyXml.query('

<myclubs>

{

  for $p in //FootBallClub

  where data($p/@id) < 2

  order by $p/name[1]

  return $p/Name

}

</myclubs>')

FROM MyTable WHERE MyKey = 4

In this case the result is

<myclubs><Name>Olympique de Marseille</Name></myclubs>

Using the value Method

SELECT MyXml.value('(/FootBallClub[@id="1"]/Name)[1]', 'nvarchar(30)') FROM MyTable

Return Olympique de Marseille

Using the exist Method

SELECT MyXml.exist('(/FootBallClub[@id="1"]/Name)[1]') FROM MyTable

Return 1 (if false return 0)

Using the modify Method

…To delete a node

UPDATE MyTable SET MyXml.modify('delete /FootBallClub[@id="3"]')

WHERE MyKey = 4

…To insert a new node

UPDATE MyTable SET MyXml.modify('insert <FootBallClub id="4"><Name>Milan AC V2</Name><Position>101</Position></FootBallClub> before (/FootBallClub[@id="2"])[1]')

FROM MyTable WHERE MyKey = 4

To specify the destination position in case of insert statement, you can choose between:

{as first | as last} into | before | after

…to update a node

UPDATE MyTable SET MyXml.modify('replace value of (/FootBallClub[@id="4"]/Name/text())[1] with "Milan AC new name"')

FROM MyTable WHERE MyKey = 4

Note the text(): this need to be specified because we are using un-typed xml column. For typed xml, the statement would be

'replace value of (/FootBallClub[@id="4"]/Name)[1] with xs:string("Milan AC new name ")')

Using the nodes Method

SELECT T.colName.query('.')

FROM MyTable

CROSS APPLY MyXml.nodes('/FootBallClub') as T(colName)

This query returns all clubs present in each of the nodes of each column. In our case it returns 6 lines (1 in line 1, 1 in line 2, 1 in line 3 and 3 in line 4)

Passing Parameters to an XQuery

The following 2 queries are identical:

SELECT MyXml.query('//Name') FROM MyTable

WHERE MyXml.value('(/FootBallClub/@id)[1]', 'int') = 1

Or by using sql:variable

DECLARE @clubId INT

SET @clubId = 1

SELECT MyXml.query('//Name') FROM MyTable

WHERE MyXml.exist('/FootBallClub[./@id = sql:variable("@clubId")]') = 1

They return 2 lines that contains Olympique de Marseille (1 in line 1 and 1 in line 4)

In this last case we will use sql:column("MyKey") to query the table

SELECT MyXml.query('

<result>

  <MyColumnId>{ sql:column("MyKey") }</MyColumnId>

  <ClubName>{ data(//Name) }</ClubName>

  <ClubPosition>{ data(//Position) }</ClubPosition>

</result>

')

FROM MyTable WHERE MyKey = 1