uch of the buzz surrounding the release of SQL Server Yukon seems to be about its new integration with the CLR. However, there are plenty of other worthwhile features in this lastest release—and at least one of them can make your database application programming whole a lot easier. Besides CLR integration, Yukon also offers three new XML support features that effectively eliminate the use of middle-tier components.

With SQL Server 2000, you needed datatypes like varchar or text to store XML data. Furthermore, there was no chance to query XML data directly on the database tier. You read your XML data in strings and passed them to the middle-tier components, which would then query against the XML data. The resulting XML was shown through the use of XSL transformations. Obviously, this was not a very elegant approach.

Yukon's three new XML support features allow you to interact directly with your XML data.

• The new XML datatype allows you to store native data inside Yukon.
• Yukon allows you to run queries against XML data with XQuery or XPath. You can also embed XQuery statements directly into T-SQL statements. XML schemas allow you to assign a schema to the new XML datatype, so you can ensure that your XML data always matches the specified XML schema.
• The XML Data Modification Language (XML DML) allows you to update XML data through the use of T-SQL statements. XML indices are used when you need a performance boost.

XML Datatypes
One way to think about the new datatype (xml) is to see it as similar to datatypes like varchar, datetime, or float. You can use it to declare columns, parameters, or variables. This new datatype allows you to:

• Store XML data in a typed form. To accomplish this a XML schema must be registered within Yukon and assigned to the XML datatype.
• Store XML data in an untyped form.
• Query against the XML data directly in T-SQL statements or stored procedures, using query languages like XQuery or XPath.
• Update XML data directly in T-SQL statements or stored procedures, using the new XML Data Modification Language (XML DML).
• Create indices on XML datatype columns, improving database performance.

Typed or Untyped
What's the difference bwteen storing data in typed or untyped form? First of all, XML data is stored by default in untyped form—without the use of an XML schema. This XML data must be well-formed. To store XML data in a typed form, you must reference an XML schema within the declaration of your columns, parameters, and variables. The following code shows how to use an untyped XML datatype:

-- Creating a new XML datatype variable
DECLARE @xml

-- Creating a new table with a xml datatype column
CREATE TABLE MyTable(ID int primary key, 
   MyXMLColumn xml)

-- Creating a stored procedure with an xml datatype
-- parameter
CREATE PROCEDURE MyStoredProcedure(ID int, 
   MyXMLValue xml)
AS
...

-- Creating a new table with a xml datatype column 
-- with a default value
CREATE TABLE MyTable(ID int primary key, 
   MyXMLColumn xml default
   '<Customer></Customer>")

To use a typed XML datatype, reference an XML schema during the declaration of the datatype. You must register the XML schema inside of Yukon in order to reference it. During this registration process, the XML schema is analyzed and all found types are imported and registered in Yukon's metasystem. Apparently, XML schemas are not simply stored as text blobs in Yukon. Because of this, it's impossible to restore the XML schema to its original state.

Luckily, Yukon provides a function to recreate the XML schema from the metadata. However, keep in mind that annotations, like comments, can't be reproduced because they are not stored in the metasystem. To register a XML schema within Yukon, use the new T-SQL statement CREATE XMLSCHEMA:

CREATE XMLSCHEMA
'<?xml-version="1.0" encoding="utf-8" ?>
<xs:schema targetNamespace=
  "http://www.csharp.at/Conferences/2004/02/ASPKonferenz" 
   elementFormDefault="qualified" 
   xmlns"http://www.csharp.at/Conferences/
   2004/02/ASPKonferenz" 
   xmlns:xs="http://www.w3.org/2001/XMLSchema" 
   xmlns:NS="http://www.csharp.at/Conferences/
   2004/02/ASPKonferenz">
   <xs:element name="Customer">
      <xs:complexType>
         <xs:attribute name="CustomerID" 
            type="xs:string" use="required" />
         <xs:attribute name="State" 
            type="xs:string" use="optional" />
         <xs:attribute name="Type" 
            type="xs:string" use="optional" >/
      </xs:complexType>
   </xs:element>
</xs:schema>'

After its registered, the XML schema can then be referenced through the target namespace http://www.csharp.at/Conferences/2004/02/ASPKonferenz.

For the new XML schema, a new record is inserted into the system table sys.xml_namespaces. This new record consists of the namespace name and a unique ID assigned to that namespace. With this ID, relations to other system tables are established. For each new type within the XML schema, a new record in the system table sys.xml_components is inserted. In the example, a new record was created for the <Customer> element and for each attribute as well.

With the system function xml_schema_namespace, Yukon allows you to regenerate the XML schema out of the stored metadata. You can call this function from a SELECT statement. As a parameter, you pass in the namespace name of the registered XML schema that should be regenerated:

SELECT xml_schema_namespace 
(N'http://www.csharp.at/Conferences/2004/02/ASPKonferenz)

To delete an already registered XML schema, use the T-SQL statement DROP XMLSCHEMA. Remember, the deleted XML schema must have no references to other objects (like tables or stored procedures):

DROP XMLSCHEMA NAMESPACE
'http://www.csharp.at/Conferences/2004/02/ASPKonferenz'

Here's how to create a typed XML datatype column within a table:

CREATE TABLE Customers
(
   ID int primary key,
   CustomerDocument xml 
      ('http://www.csharp.at/Conferences/
      2004/02/ASPKonferenz')
)

As you can see, the target namespace of the referencing XML schema must be provided during the creation of the new table.

Loading the XML Datatype
How do you load XML data into the new XML datatype? With Yukon, you can convert string data to XML data implicitly or explicitly by using the T-SQL functions CAST and CONVERT. Take a look:

INSERT INTO Customers (ID, CustomerDocument)
VALUES
(
   1, 
   '<Customer 
      xmlns="http://www.csharp.at/Conferences/
      2004/02/ASPKonferenz" 
      CustomerID="ALFKI" State="DE" Type="ISV">
      Alfreds Futterkiste
   </Customer>'
)

INSERT INTO Customers(ID, CustomerDocument)
VALUES
(
   2, 
   CAST('<Customer xmlns="http://www.csharp.at/
      Conferences/2004/02/ASPKonferenz" 
      CustomerID="MSFT" State="WA" Type="ISV">
      Microsoft Corporation
   </Customer>' AS xml)
)

INSERT INTO Customers(ID, CustomerDocument)
VALUES
(
   3, 
   CONVERT(xml, '<Customer xmlns="http://www.csharp.at/
      Conferences/2004/02/ASPKonferenz" 
      CustomerID="TT" State="AT" Type="ISV">
      TechTalk
   </Customer>')
)

You need to provide the target namespace in order to insert XML data to a typed XML datatype. Without the the target namespace, Yukon raises an exception and the data won't be inserted. This guarantees that all inserted XML data is validated and matches the provided XML schema.

Creating Constraints
You can also create constraints during the creation of a table with the use of the T-SQL function check:

CREATE TABLE Customers
(
   Col1 int primary key,
   Col2 xml check(
      Col2::exist('/Customer/@CustomerID' = 1)
)

-- The following insert operation succeeds
INSERT INTO Customers (Col1, Col2)
VALUES (1, '<Customer CustomerID="ALFKI" />')

-- The following insert operation didnt succeeds
INSERT INTO Customers (Col1, Col2)
VALUES (2, '<Customer>Alfreds Futterkiste</Customer>')

You need to check the constraint in order to make sure that the attribute CustomerID exists in the inserted XML data. The following code shows the creation of a constraint that verifies that the column CustomerID has the same value as the attribute CustomerID of the XML element <Customer>S:

CREATE TABLE Customers
(
   CustomerID int primary key,
   CustomerDocument xml,
   Constraint CustomerDocument_Check check
   (
      CustomerDocument::value('/Customer/@CustomerID', 
         'int') = CustomerID
   )
)

-- The following insert operation succeeds
INSERT INTO Customers (CustomerID, CustomerDocument)
VALUES (1, '<Customer CustomerID="1" />')

-- The following insert operation didn't succeeds
INSERT INTO Customers (CustomerID, CustomerDocument)
VALUES (2, '<Customer CustomerID="1" />')

XML Datatype Methods
With the help of these four functions, you can now embed XQuery statements directly into T-SQL code:

• modify(): This method updates XML data. Since these updates also use the XML Data Modification Language (XML DML), I'll disuss this method later in more detail.
• query(): This method executes a XQuery statement on an XML datatype. The result is an untyped XML fragment:

  
  DECLARE @myDoc xml
  SET @myDoc =
  '<Root>
     <ProductDescription ProductID="1"
        ProductName="Road Bike">
        <Features>
           <Warranty>1 year</Warranty>
           <Maintenance>3 years</Maintenance>
        </Features>
     </ProductionDescription>
  </Root>'
  
  SELECT @myDoc::query('/Root/ProductDescription/Features')
  
  -- Result
  <Features>
     <Warranty>1 year</Warranty>
     <Maintenance>3 years/Maintenance>
  </Features>
  

  These functions are not limited to use on XML variables. The following code shows how to use query() on a XML column:

  
  USE AdventureWorks
  
  SELECT CatalogDescription::query('
     namespace PD="http://www.adventure-works.com/
     schemas/products/description"
     <Product ProductModelID="{ /PD:ProductDescription[1]/
        @ProductModelID }" />)' AS Result
  FROM ProductModel
  WHERE CatalogDescription::exist('namespace PD="
     http://www.adventure-works.com/schemas/product/
     description" namespace wm="http://www.adventure-works.
     com/schemas/products/WarrAndMain"
     /PD:ProductDescription/PD:Features/wm:Warranty') = 1
  
  -- Result
  <Product ProductModelID="19" />
  <Product ProductModelID="23" />
  <Product ProductModelID="25" />
  <Product ProductModelID="28" />
  <Product ProductModelID="34" />
  <Product ProductModelID="35" />
  

  In the previous code, the column CatalogDescription is a typed XML column. Because it is, you need to provide the XML namespace names in the XQuery statements. This query constructs the XML element <Product>, which consists of the attribute ProductIDS. From the column CatalogDescription, you only return the elements that contain the <Warranty> element. This example shows very nicely how T-SQL code can be combined with XQuery statements.
• value(): This method returns a native SQL Server type like datetime, varchar, or int. Here's what this looks like:

  
  USE AdventureWorks
  
  SELECT CatalogDescription::value('namespace 
     PD="http://www.adventure-works.com/schemas/products/
     deescription"
     /PD:ProductDescription/@ProductModelID', 'int')
     AS Result
  FROM ProductModel
  ORDER BY Result DESC
  

  value() expects a XQuery statement in the first parameter and the native SQL Server datatype to which the result should be converted in the second parameter. The following table shows how the most important XSD datatypes are mapped to the SQL Server datatypes:

  XSD datatype	SQL Server datatype
  string	nvarchar(4000)
  boolean	bit
  decimal	numeric
  float	real
  double	float
  duration	varbinary
  datetime	varbinary
  time	varbinary
  date	varbinary

• exist(): This method determines if a specified node or value exists in the XML data. exist() returns 1 (true) if the node/value exists and returns 0 (false) if it doesn't. The following code uses exist() to determine whether a <WorkCenter> element with the ID 50 exists in the XML data:

  
  DECLARE @x xml ('http://schemas.adventure-
  
     works.com/ManufInstructions/ByProdModel')
  
   
  
  SELECT @x = Instructions FROM ProductPlan
  
  WHERE ProductModelID = 67
  
   
  
  DECLARE @f int
  
  SET @f = @x::exist('namespace AWMI="
  
     http://schemas.adventure-works.com/
  
     ManufInstructions/ByProdModel"
  
     /AWMI:root/AWM:WorkCenter[@WorkCenterID=50]')
  
   
  
  SELECT @f
  

XQuery Extended
You can see that Yukon uses XQuery statements to produce some very powerful T-SQL code. But what if you want to reference another column or variable within your XQuery statement? The XQuery specification doesn't provide any support for this. For that reason, Microsoft has extended XQuery with two important functions:

• sql:column(): This function accesses columns within your XQuery statement. To do this, your column name must be fully qualified with the table name, for instance Customer.CustomerID. Take a look:

  
  USE AdventureWorks
  
  SELECT P.ProductID, CatalogDescription::query('
     namespace pd="http://www.adventure-works.com/
     schemas/products/description"
     <Product
        ProductID="{ sql:column("P.ProductID") }"
        ProductName="{ sql:column("P.Name") }"
        ProductPrice="{ sql:column("P.ListPrice") }"
        ProductModelID="{ sql:column("PM.ProductModelID") }
        ">
        {
           if (not(empty(/pd:ProductDescription)))
           then
              attribute ProductModelName
              {
                 /pd:ProductDescription[1]/
                 @ProductModelName
              }
           else ()
        }
     </Product>' AS Result
  FROM ProductModel PM, Product P
  WHERE PM.ProductModelID = P.ProductModelID
  ORDER BY PM.ProductModelID
  
  -- Result
  <Product ProductID="1225" ProductName="Classic 
     Vest, S" ProductPrice="63.5" ProductModelID = "1" />
  ...
  

  Notice that the values of the attributes ProductID, ProductName, ProductPrice, and ProductModelID are taken from the corresponding columns in the tables Product and ProductModel. In contrast to this, the attribute ProductModelName is taken from the XML column CatalogDescription.
• sql:variable(): This function references a variable (like in a stored procedure) within the XQuery statement. Here's how to use this function:

  
  USE AdventureWorks
  
  DECLARE @price money
  SET @price = 2500.00
  DECLARE @x xml
  
  SELECT @x = CatalogDescription::query('
     namespace pd="http://www.adventure-works.com/schemas/
     products/description"
     <Product
        ProductID="{ sql:column(Product.ProductID") }"
        ProductModelID="{ 
           sql:column(Product.ProductModelID") }"
        ProductModelName="{ /pd:ProductDescription[1]
           /@ProductModelName }"
        ListPrice="{ sql:column("Product.ListPrice") }"
        DiscountPrice="{ sql:variable("@price") }"
     />'
  FROM Product, ProductModel
  WHERE Product.ProductModelID = 
     ProductModel.ProductModelID AND ProductID = 168
  
  SELECT @x
  
  -- Result
  <Product ProductID="168" 
     ProductModelID="19" 
     ProductModelName="Mountain 100" 
     ListPrice="3374.99" 
     DiscountPrice="2500.00" />
  

The XML Data Modification Language (XML DML)
What if you want to alter XML data stored within Yukon? In SQL 200, you had to load the data from the database and pass it to a middle-tier component. The component then changed the data and wrote the changes back to the database.

Eliminating this step, Microsoft has extended the XQuery specification with the XML Data Modification Language. Based on XQuery, this language provides the necessary functionality to change XML data directly on the database tier.

Access the XML Data Modification Language using the XML data method modify(). The XML DML uses three keywords, insert, delete, and update.

• insert: Take a look at the syntax of the insert keyword:

  
  insert Expression1
  (
     {as first | as last} into | after | before
     Expression2
  )
  

  The following table describes each parameter:

  Parameter	Description
  Expression1	References the nodes that should be inserted. This can be an XQuery statement or a static XML fragment.
  into	The XML nodes specified through Expression1 are inserted directly as children to the XML nodes specified by Expression2.
  as first	Indicates that the nodes of Expression1 are inserted first into the nodes specified by Expression2.
  as last	Indicates that the nodes of Expression1 are inserted last into the nodes specified by Expression2.
  after	Indicates that the nodes of Expression1 are inserted directly after the nodes specified by Expression2 as siblings.
  before	Indicates that the nodes of Expression1 are inserted directly before the nodes specified by Expression2 as siblings.
  Expression2	Specifies the XML nodes in which the nodes from Expression1 should be inserted. This is an XQuery statement.

  The following code shows how to use the insert keyword:

  
  DECLARE @myDoc xml
  SET @myDoc = 
  '<Root>
     <ProductDescription ProductID="1" 
        ProductName="Road Bike">
        <Features>
        </Features>
     </ProductDescription>
  </Root>'
  SELECT @myDoc
  
  SET @myDoc::modify('insert <Maintenance>3 years
     </Maintenance> into /Root/ProductDescription/
     Features')
  SELECT @myDoc
  
  SET @myDoc::modifiy('insert <Warranty>1 year
     </Warranty> as first into 
     /Root/ProductDescription/Features')
  SELECT @myDoc
  
  SET @myDoc::modify('insert <Material>Aluminum
     </Material> as last into 
     /Root/ProductDescription/Features')
  SELECT @myDoc
  
  SET @myDoc::modify('insert <BikeFrame>Strong
     </BikeFrame> after 
     /Root/ProductDescription/Features/Material')
  SELECT @myDoc
  

  The attribute attribute of the insert keyword allows you to insert new attributes into XML data:

  
  DECLARE @myDoc xml
  SET @myDoc =
  <Root>
     <WorkCenter WorkCenterID=10>
        <step>Step 1</step>
        <step>Step 2</step>
     </WorkCenter>
  </Root>
  SELECT @myDoc
  
  SET @myDoc::modify(insert attribute LaborHours {.5} 
     into /Root/WorkCenter[@WorkCenterID=10])
  SELECT @myDoc
  
  DECLARE @Hrs float
  SET @Hrs = .2
  SET @myDoc::modify(insert attribute MachineHours
     {sql:variable(@Hrs)} into
     /Root/WorkCenter[@WorkCenterID=10])
  SELECT @myDoc
  
  SET @myDoc::modify(insert
     (
        attribute SetupHours {.5 },
        attribute SomeOtherAtt {.2 }
     )
     into /Root/WorkCenter[@WorkCenterID=10])
  SELECT @myDoc
  

• delete: This keyword, naturally, deletes nodes from your XML data. This keyword expects only one parameter: an XQuery statement that selects the nodes to be deleted. Here's how to use it:

  
  DECLARE @myDoc xml
  SET @myDoc = '<?Instructions for=TheWC.exe ?>
  <Root>
     <!-- Instructions for the first work center-->
     <WorkCenter WorkCenterID="10" LaborHours="1.1" 
        MachineHours=".2">
        Some text 1
        <step>Step 1</step>
        <step>Step 2</step>
     </WorkCenter>
  </Root>'
  SELECT @myDoc
  
  SET @myDoc::modify('delete /Root/WorkCenter/step[2]')
  SELECT @myDoc
  
  SET @myDoc::modify('delete 
     /Root/WorkCenter/@MachineHours')
  SELECT @myDoc
  
  SET @myDoc::modify('delete /Root/WorkCenter/text()')
  SELECT @myDoc
  
  SET @myDoc::modify('delete //processing-instruction()')
  SELECT @myDoc
  

• update: As to be expected, this keyword updates XML data nodes:

  
  update [value of]
     Expression1
  to
     Expression2
  

  The following table describes each parameter in more detail:

  Parameter	Description
  Expression1	Specifies the XML node that should be updated. The specified XQuery statement must return only 1 node. If 0 or more than 1 nodes are returned an exception is raised.
  Expression2	Specifies the new value of the node specified by Expression1. This can be also an XQuery statement.
  [value of]	Optional parameter. If this parameter is used then Expression2 is not treated as an XQuery statement, but as plain XML data.

  The following code shows how to use this keyword:

  
  DECLARE @myDoc xml
  SET @myDoc = 
  '<Root>
     <WorkCenter WorkCenterID="10" LaborHours="1.1" 
        MachineHours=".2">
        Manu steps are described here
        <step>Step 1</step>
        <step>Step 2</step>
     </WorkCenter>
  </Root>'
  SELECT @myDoc
  
  SET @myDoc::modify('update 
     /Root/WorkCenter/step[1]/text() to "new text 
     describing the manu step"')
  SELECT @myDoc
  
  SET @myDoc::modify('update /Root/WorkCenter/@LaborHours 
     to "100.0"')
  SELECT @myDoc
  

Yukon's XML Limitations
As you've seen, SQL Server Yukon is first class for storing, querying, and updating XML Data. However, there are some some limitations when working with the new XML datatype and XML schemas:

1. XML can't be casted to text or ntext.
2. An XML column can't be part of a primary—or foreign key constraint.
3. Only strings can be casted to XML.
4. An XML column can't be used in a GROUP BY statement.
5. An XML column can't be part of an index.
6. It is only possible to create 32 XML columns per table.

The XML schema support in Yukon has the following limitations:

1. Annotations (like comments) are not stored in the metasystem of Yukon.
2. The XSD ID attribute is not supported.
3. Default values can't be longer than 4000 unicode characters.
4. XML schemas can't be converted to their origin state. Therefore, you need to manage XML schemas separately.

Even with these limitations, the ability to directly query and modify XML data on the database tier is a great step forward. If you work with XML data in your applications, it is well worth your time to investigate Yukon and find out whether you can benefit from these new features.