Today I gave software developers in the Netherlands a preview of the XML Features of SQL Server 2005 or better know by its code name “Yukon” (the US National Park, not the Canadian frozen land). I show off the very cool and advanced XML to Relational mapping. (Clemens is a big fan). I then talk all about the deep XML integration via the XML Data type. This is a native SQL Server type so we have the option to store XML in its native format. For example you can do just about anything a standard datatype can do, excluding Foreign Keys and PKs. Here is the XML Data Type at work in a variable:

DECLARE @xmlData AS XML

SET @xmlData = (SELECT * From Customers where CustomerID='ALFKI'  FOR XML AUTO, TYPE)

SELECT @xmlData

The results look like this:

<Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" ContactTitle="Sales Representative" Address="Obere Str. 57" City="Berlin" PostalCode="12209" Country="Germany" Phone="030-0074321" Fax="030-0076545"/>

(1 row(s) affected)

Then it is fun to create tables with XML fields in them and do things like create an index on them, etc.

--create the table with the XML Datatype

CREATE TABLE OrdersXML

     (OrderDocID INT PRIMARY KEY, xOrders XML NOT NULL)

Then you can insert some XML into the table, you can do this manually, from an XML Raw statement or via a XML file.

--Get some XML Data, can also use a file

DECLARE @xmlData AS XML

SET @xmlData = (SELECT * From Orders FOR XML AUTO, TYPE)

--insert into the table

Insert Into OrdersXML (OrderDocID, xOrders) Values (1, @xmlData)

Then let’s add an index on that column:

CREATE XML INDEX idx_1 ON OrdersXML (xOrders)

It is more fun to create an XML Schema as data validation for data validation. This is my favorite thing about the XML integration, it gives you the best of both worlds. This shows off the deep integration of the XML model and the relational model, the XML Schema works just like a constraint, allowing us to validate the XML that we put into our XML data type fields in the database.

--xml schema as validation

use northwind

CREATE XMLSCHEMA

'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

            xmlns:tns="http://corzen.com/customer"

            targetNamespace="http://corzen.com/customer" >

   <xsd:simpleType name="personAge" >

     <xsd:restriction base="xsd:float" >

       <xsd:maxInclusive value="65" />

       <xsd:minExclusive value="18" />

     </xsd:restriction>

   </xsd:simpleType>

   <xsd:element name="age" type="tns:personAge" />

</xsd:schema>'

GO

-- create table with xml column and use schema as a 'constraint'

CREATE TABLE xml_schematest (

   the_id INTEGER,

   xml_col XML('http://corzen.com/customer')

)

GO

-- works

INSERT INTO xml_schematest VALUES(1,

 '<p:age xmlns:p="http://corzen.com/customer">55</p:age>')

GO

-- fails, age > 65

INSERT INTO xml_schematest VALUES(2,

 '<p:age xmlns:p="http://corzen.com/customer">100</p:age>')

GO

-- fails, column is validated on update also

UPDATE xml_schematest

  SET xml_col = '<p:age xmlns:p="http://corzen.com/customer">100</p:age>'

  WHERE the_id = 1

DROP TABLE xml_schematest

GO

DROP XMLSCHEMA NAMESPACE 'http://corzen.com/customer'

GO

But wait, there’s more! I show off a little bit of XQuery. What is cool is that XQuery is integrated fully with TSQL.

--XQuery

use adventureworks

--take a look at an XML column

select * from ProductModel where productmodelid=19

--query out a piece of data from the xml field

SELECT Name, CatalogDescription::query('

namespace PD="http://www.adventure-works.com/schemas/products/description"

<Product ProductModelID="{ /PD:ProductDescription[1]/@ProductModelID }" />

') as Result

FROM ProductModel

where Name like 'Mo%' and CatalogDescription::exist('

namespace PD="http://www.adventure-works.com/schemas/products/description"

namespace wm="http://www.adventure-works.com/schemas/products/WarrAndMain"

     /PD:ProductDescription/PD:Features/wm:Warranty ') = 1