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
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')
)
-- works
INSERT INTO xml_schematest VALUES(1,
'<p:age xmlns:p="http://corzen.com/customer">55</p:age>')
-- fails, age > 65
INSERT INTO xml_schematest VALUES(2,
'<p:age xmlns:p="http://corzen.com/customer">100</p:age>')
-- 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
DROP XMLSCHEMA NAMESPACE 'http://corzen.com/customer'
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 wm="http://www.adventure-works.com/schemas/products/WarrAndMain"
/PD:ProductDescription/PD:Features/wm:Warranty ') = 1
