If your data is highly structured with known schema, the relational model is likely to work best for data storage. SQL Server provides the required functionality and tools you may need. On the other hand, if the structure is semi-structured or unstructured, or unknown, you have to give consideration to modeling such data.

XML is a good choice if you want a platform-independent model in order to ensure portability of the data by using structural and semantic markup. Additionally, it is an appropriate option if some of the following properties are satisfied:

• Your data is sparse or you do not know the structure of the data, or the structure of your data may change significantly in the future.
  
• Your data represents containment hierarchy, instead of references among entities, and may be recursive.
  
• Order is inherent in your data.
  
• You want to query into the data or update parts of it, based on its structure.
  

If none of these conditions is met, you should use the relational data model. For example, if your data is in XML format but your application just uses the database to store and retrieve the data, an [n]varchar(max) column is all you require. Storing the data in an XML column has additional benefits. This includes having the engine determine that the data is well formed or valid, and also includes support for fine-grained query and updates into the XML data.

Following are some of the reasons to use native XML features in SQL Server 2005 instead of managing your XML data in the file system:

• You want to share, query, and modify your XML data in an efficient and transacted way. Fine-grained data access is important to your application. For example, you may want to extract some of the sections within an XML document, or you may want to insert a new section without replacing your whole document.
  
• You have relational data and XML data and you want interoperability between both relational and XML data within your application.
  
• You need language support for query and data modification for cross-domain applications.
  
• You want the server to guarantee that the data is well formed and also optionally validate your data according to XML schemas.
  
• You want indexing of XML data for efficient query processing and good scalability, and the use of a first-rate query optimizer.
  
• You want SOAP, ADO.NET, and OLE DB access to XML data.
  
• You want to use administrative functionality of the database server for managing your XML data. For example, this would be backup, recovery, and replication.
  

If none of these conditions is satisfied, it may be better to store your data as a non-XML, large object type, such as [n]varchar(max) or varbinary(max).

The storage options for XML in SQL Server 2005 include the following:

• Native storage as xml data type
  The data is stored in an internal representation that preserves the XML content of the data. This includes containment hierarchy, document order, and element and attribute values. Specifically, the InfoSet content of the XML data is preserved. For more information about InfoSet, visit http://www.w3.org/TR/xml-infoset. The InfoSet content may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.
  For typed xml data type, an xml data type bound to XML schemas, the post-schema validation InfoSet (PSVI) adds type information to the InfoSet and is encoded in the internal representation. This improves parsing speed significantly. For more information, see the W3C XML Schema specifications at http://www.w3.org/TR/xmlschema-1 and http://www.w3.org/TR/xmlschema-2.
  
• Mapping between XML and relational storage
  By using an annotated schema (AXSD), the XML is decomposed into columns in one or more tables. This preserves fidelity of the data at the relational level. As a result, the hierarchical structure is preserved although order among elements is ignored. The schema cannot be recursive.
  
• Large object storage, [n]varchar(max) and varbinary(max)
  An identical copy of the data is stored. This is useful for special-purpose applications such as legal documents. Most applications do not require an exact copy and are satisfied with the XML content (InfoSet fidelity).
  

Generally, you may have to use a combination of these approaches. For example, you may want to store your XML data in an xml data type column and promote properties from it into relational columns. Or, you may want to use mapping technology to store nonrecursive parts in non-XML columns and only the recursive parts in xml data type columns.

Choice of XML Technology

Native XML Storage

XML View Technology

Hybrid Model

An xml data type column can be created in a table that contains other relational columns, or in a separate table with a foreign key relationship to a main table.

Create an xml data type column in the same table when one of the following conditions is true:

• Your application performs data retrieval on the XML column and does not require an XML index on the XML column.
  
• You want to build an XML index on the xml data type column and the primary key of the main table is the same as its clustering key. For more information, see Indexing an xml Data Type Column.
  

Create the xml data type column in a separate table if the following conditions are true:

• You want to build an XML index on the xml data type column, but the primary key of the main table is different from its clustering key, or the main table does not have a primary key, or the main table is a heap (no clustering key). This may be true if the main table already exists.
  
• You do not want table scans to slow down because of the presence of the XML column in the table. This uses space whether it is stored in-row or out-of-row.
  

The granularity of the XML data stored in an XML column is very important for locking and, to a lesser degree, it is also important for updates. SQL Server uses the same locking mechanism for both XML and non-XML data. Therefore, row-level locking causes all XML instances in the row to be locked. When the granularity is large, locking large XML instances for updates causes throughput to decline in a multiuser scenario. On the other hand, severe decomposition loses object encapsulation and increases reassembly cost.

A balance between data modeling requirements and locking and update characteristics is important for good design. However, in SQL Server 2005, the size of actual stored XML instances is not as critical.

For example, updates to an XML instance are performed by using new support for partial binary large object (BLOB) and partial index updates in which the existing stored XML instance is compared to its updated version. Partial binary large object (BLOB) update performs a differential comparison between the two XML instances and updates only the differences. Partial index updates modify only those rows that must be changed in the XML index.

The SQL Server 2005 the xml data type implements the ISO SQL-2003 standard xml data type. Therefore, it can store well-formed XML version 1.0 documents and also so-called XML content fragments with text nodes and an arbitrary number of top-level elements in an untyped XML column. The system checks that the data is well formed, does not require the column to be bound to XML schemas, and rejects data that is not well formed in the extended sense. This is true also of untyped XML variables and parameters.

If you have XML schemas describing your XML data, you can associate the schemas with the XML column to yield typed XML. The XML schemas are used to validate the data, perform more precise type checks during compilation of query and data modification statements than untyped XML, and optimize storage and query processing.

Use untyped xml data type in the following situations:

• You do not have a schema for your XML data.
  
• You have schemas, but you do not want the server to validate the data. This is sometimes the case when an application performs client-side validation before storing the data at the server, or temporarily stores XML data that is invalid according to the schema, or uses schema components unsupported at the server (for example, key/keyref).
  

Use typed xml data type in the following situations:

• You have schemas for your XML data and you want the server to validate your XML data according to the XML schemas.
  
• You want to take advantage of storage and query optimizations based on type information.
  
• You want to take better advantage of type information during compilation of your queries.
  

Typed XML columns, parameters, and variables can store XML documents or content. However, you have to specify with a flag whether you are storing a document or content at the time of declaration. Additionally, you have to provide the collection of XML schemas. Specify DOCUMENT if each XML instance has exactly one top-level element. Otherwise, use CONTENT. The query compiler uses the DOCUMENT flag in type checks during query compilation to infer singleton top-level elements.

Besides typing an XML column, you can use relational (column or row) constraints on typed or untyped xml data type columns. Use constraints in the following situations:

• Your business rules cannot be expressed in XML schemas. For example, the delivery address of a flower shop must be within 50 miles of its business location. This can be written as a constraint on the XML column. The constraint may involve xml data type methods.
  
• Your constraint involves other XML or non-XML columns in the table. An example is the enforcement of the ID of a Customer (/Customer/@CustId) found in an XML instance to match the value in a relational CustomerID column.
  

Document Type Definition (DTD)

XML indexes can be created on xml data type columns. It indexes all tags, values and paths over the XML instances in the column and benefits query performance. Your application may benefit from an XML index in the following situations:

• Queries on XML columns are common in your workload. XML index maintenance cost during data modification must be considered.
  
• Your XML values are relatively large and the retrieved parts are relatively small. Building the index avoids parsing the whole data at run time and benefits index lookups for efficient query processing.
  

The first index on an XML column is the primary XML index. In using it, three types of secondary XML indexes can be created on the XML column to speed up common classes of queries, as described in the following section.

Primary XML Index

<book genre="security" publicationdate="2002" ISBN="0-7356-1588-2">
   <title>Writing Secure Code</title>
   <author>
      <first-name>Michael</first-name>
      <last-name>Howard</last-name>
   </author>
   <author>
      <first-name>David</first-name>
      <last-name>LeBlanc</last-name>
   </author>
   <price>39.99</price>
</book>

CREATE PRIMARY XML INDEX idx_xCol on T (xCol)

Secondary XML Indexes

SELECT pk, xCol
FROM   T
WHERE  xCol.exist ('/book/@genre[.="novel"]') = 1

CREATE XML INDEX idx_xCol_Path on T (xCol)
   USING XML INDEX idx_xCol FOR PATH

SELECT xCol.value ('(/book/@genre)[1]', 'varchar(50)'),
    xCol.value ('(/book/title/text())[1]', 'varchar(50)'),
    xCol.value ('(/book/@ISBN)[1]', 'varchar(50)')
FROM    T

CREATE XML INDEX idx_xCol_Property on T (xCol)
   USING XML INDEX idx_xCol FOR PROPERTY

SELECT xCol
FROM    T
WHERE    xCol.exist ('//book/@ISBN[. = "0-7356-1588-2"]') = 1

CREATE XML INDEX idx_xCol_Value on T (xCol)
   USING XML INDEX idx_xCol FOR VALUE

Full-Text Index on an XML Column

SELECT * 
FROM   T 
WHERE  CONTAINS(xCol,'custom') 
AND    xCol.exist('/book/title/text()[contains(.,"custom")]') =1

SELECT * 
FROM   T 
WHERE  CONTAINS(xCol,'run') 

If queries are made principally on a small number of element and attribute values, you may want to promote those quantities into relational columns. This is helpful when queries are issued on a small part of the XML data while the whole XML instance is retrieved. Creating an XML index on the XML column is not required. Instead, the promoted column can be indexed. Queries must be written to use the promoted column. That is, the query optimizer does not target again the queries on the XML column to the promoted column.

The promoted column can be a computed column in the same table or it can be a separate, user-maintained column in a table. This is sufficient when singleton values are promoted from each XML instance. However, for multi-valued properties, you have to create a separate table for the property, as described in the following section.

Computed Column Based on the xml Data Type

CREATE FUNCTION udf_get_book_ISBN (@xData xml)
RETURNS varchar(20)
BEGIN
   DECLARE @ISBN   varchar(20)
   SELECT @ISBN = @xData.value('/book[1]/@ISBN', 'varchar(20)')
   RETURN @ISBN 
END

ALTER TABLE      T
ADD   ISBN AS dbo.udf_get_book_ISBN(xCol)

SELECT xCol
FROM   T
WHERE  xCol.exist('/book/@ISBN[. = "0-7356-1588-2"]') = 1

SELECT xCol
FROM   T
WHERE  ISBN = '0-7356-1588-2'

Creating Property Tables

You may want to promote some of the multivalued properties from your XML data into one or more tables, create indexes on those tables, and target again your queries to use them. A typical scenario is one in which a small number of properties covers most of your query workload. You can do the following:

• Create one or more tables to hold the multivalued properties. You may find it convenient to store one property per table and duplicate the primary key of the base table in the property tables for back joining with the base table.
  
• If you want to maintain the relative order of the properties, you have to introduce a separate column for the relative order.
  
• Create triggers on the XML column to maintain the property tables. Within the triggers, do one of the following:
  
  • Use xml data type methods, such as nodes() and value(), to insert and delete rows of the property tables.
    
  • Create streaming table-valued functions in the common language runtime (CLR) to insert and delete rows of the property tables.
    
  • Write queries for SQL access to the property tables and for XML access to the XML column in the base table, with joins between the tables by using their primary key.
    

Example: Create a Property Table

For illustration, assume that you want to promote the first name of the authors. Books have one or more authors, so that first name is a multivalued property. Each first name is stored in a separate row of a property table. The primary key of the base table is duplicated in the property table for back join.

create table tblPropAuthor (propPK int, propAuthor varchar(max))

Example: Create a User-defined Function to Generate a Rowset from an XML Instance

The following table-valued function, udf_XML2Table, accepts a primary key value and an XML instance. It retrieves the first name of all authors of the <book> elements and returns a rowset of primary key, first name pairs.

create function udf_XML2Table (@pk int, @xCol xml)
returns @ret_Table table (propPK int, propAuthor varchar(max))
with schemabinding
as
begin
      insert into @ret_Table 
      select @pk, nref.value('.', 'varchar(max)')
      from   @xCol.nodes('/book/author/first-name') R(nref)
      return
end

Example: Create Triggers to Populate a Property Table

The insert trigger inserts rows into the property table:

create trigger trg_docs_INS on T for insert
as
      declare @wantedXML xml
      declare @FK int
      select @wantedXML = xCol from inserted
      select @FK = PK from inserted

   insert into tblPropAuthor
   select * from dbo.udf_XML2Table(@FK, @wantedXML)

The delete trigger deletes the rows from the property table based on the primary key value of the deleted rows:

create trigger trg_docs_DEL on T for delete
as
   declare @FK int
   select @FK = PK from deleted
   delete tblPropAuthor where propPK = @FK

The update trigger deletes the existing rows in the property table corresponding to the updated XML instance and inserts new rows into the property table:

create trigger trg_docs_UPD
on T
for update
as
if update(xCol) or update(pk)
begin
      declare @FK int
      declare @wantedXML xml
      select @FK = PK from deleted
      delete tblPropAuthor where propPK = @FK

   select @wantedXML = xCol from inserted
   select @FK = pk from inserted

   insert into tblPropAuthor 
      select * from dbo.udf_XML2Table(@FK, @wantedXML)
end

Example: Find XML Instances Whose Authors Have the First Name of "David"

The query can be formed on the XML column. Alternatively, it can search the property table for first name "David" and perform a back join with the base table to return the XML instance. For example:

SELECT xCol 
FROM     T JOIN tblPropAuthor ON T.pk = tblPropAuthor.propPK
WHERE    tblPropAuthor.propAuthor = 'David'

Example: Solution Using the CLR Streaming Table-valued Function

This solution is made up of the following steps:

1. Define a CLR class, SqlReaderBase, that implements ISqlReader and generates a streaming, table-valued output by applying a path expression on an XML instance.
   
2. Create an assembly and a Transact-SQL user-defined function to start the CLR class.
   
3. Define the insert, update, and delete triggers by using the user-defined function to maintain a property tables.
   

To do this, you first create the streaming CLR function. The xml data type is exposed as a managed class SqlXml in ADO.NET and supports the CreateReader() method that returns an XmlReader.

Note:

The example code in this section uses XPathDocument and XPathNavigator. These force you to load all the XML documents into memory. If you are using similar code in your application to process several large XML documents, this code is not scalable. Instead, keep memory allocations small and use streaming interfaces whenever possible. For more information about performance, see Architecture of CLR Integration.

public class c_streaming_xml_tvf {
   public static ISqlReader streaming_xml_tvf 
(SqlXml xmlDoc, string pathExpression) {
      return (new TestSqlReaderBase (xmlDoc, pathExpression));
   }
}

// Class that implements ISqlReader
public class TestSqlReaderBase : ISqlReader {
XPathNodeIterator m_iterator;         
   public SqlChars FirstName;
// Metadata for current resultset
private SqlMetaData[] m_rgSqlMetaData;      

   public TestSqlReaderBase (SqlXml xmlDoc, string pathExpression) {   
      // Variables for XPath navigation
      XPathDocument xDoc;
      XPathNavigator xNav;
      XPathExpression xPath;
   
      // Set sql metadata
      m_rgSqlMetaData = new SqlMetaData[1];
      m_rgSqlMetaData[0] = new SqlMetaData ("FirstName",  
SqlDbType.NVarChar,50);   
   
      //Set up the Navigator
      if (!xmlDoc.IsNull)
          xDoc = new XPathDocument (xmlDoc.CreateReader());
      else
          xDoc = new XPathDocument ();
      xNav = xDoc.CreateNavigator();
      xPath = xNav.Compile (pathExpression);
      m_iterator = xNav.Select(xPath);
   }
   public bool Read() {
      bool moreRows = true;
      if (moreRows = m_iterator.MoveNext())
         FirstName = new SqlChars (m_iterator.Current.Value);
      return moreRows;
   }
}

Next, create an assembly and a Transact-SQL user-defined function, SQL_streaming_xml_tvf (not shown), that corresponds to the CLR function, streaming_xml_tvf. The user-defined function is used to define the table-valued function, CLR_udf_XML2Table, for rowset generation:

create function CLR_udf_XML2Table (@pk int, @xCol xml)
returns @ret_Table table (FK int, FirstName varchar(max))
with schemabinding
as
begin
      insert into @ret_Table 
   select @pk, FirstName 
   FROM   SQL_streaming_xml_tvf (@xCol, '/book/author/first-name')
      return
end

Finally, define triggers as shown in the example, "Create triggers to populate a property table", but replace udf_XML2Table with the CLR_udf_XML2Table function. The insert trigger is shown in the following example:

create trigger CLR_trg_docs_INS on T for insert
as
   declare @wantedXML xml
   declare @FK int
   select @wantedXML = xCol from inserted
   select @FK = PK from inserted

   insert into tblPropAuthor
      select *
   from    dbo.CLR_udf_XML2Table(@FK, @wantedXML)

The delete trigger is identical to the non-CLR version. However, the update trigger just replaces the function udf_XML2Table() with CLR_udf_XML2Table().

An XML schema collection is a metadata entity that is scoped by a relational schema. It contains one or more XML schemas that may be related, such as through <xs:import>), or that may be unrelated. Individual XML schemas within an XML schema collection are identified by using their target namespace.

An XML schema collection is created by using CREATE XML SCHEMA COLLECTION (Transact-SQL) syntax and providing one or more XML schemas. More XML schema components can be added to an existing XML schema, and more schemas can be added to an XML schema collection by using ALTER XML SCHEMA COLLECTION (Transact-SQL) syntax. XML schema collections can be secured like any SQL object by using the security model in SQL Server 2005.

Multi-Typed Column

Schema Evolution

You can transfer XML data to SQL Server 2005 in several ways. For example:

• If you have your data in an [n]text or image column in a SQL Server 2000 database, you can import the table into a SQL Server 2005 database by using SQL Server 2005 Integration Services (SSIS). Change the column type to XML by using the ALTER TABLE statement.
  
• You can bulk copy your data from SQL Server 2000 by using bcp out, and then bulk insert the data into the SQL Server 2005 database by using bcp in.
  
• If you have data in relational columns in a SQL Server 2000 database, create a new table with an [n]text column and, optionally, a primary key column for a row identifier. Use client-side programming to retrieve the XML that is generated at the server with FOR XML and write it into the [n]text column. Then, use the previously mentioned techniques to transfer data to a SQL Server 2005 database. You can choose to write the XML into an XML column in the SQL Server 2005 database directly.
  

ALTER TABLE R ALTER COLUMN XYZ XML

You can bulk load XML data into the server by using the bulk loading capabilities of SQL Server, such as bcp. OPENROWSET allows you to load data into an XML column from files. The following example illustrates this point.

INSERT INTO T
SELECT 10, xCol
FROM    (SELECT *    
    FROM OPENROWSET (BULK 'C:\MyFile\xmlfile.xml', SINGLE_CLOB) 
 AS xCol) AS R(xCol)

SQL Server 2005 stores XML data in Unicode (UTF-16). XML data retrieved from the server comes out in UTF-16 encoding. If you want a different encoding, you have to perform the required conversion on the retrieved data. Sometimes, the XML data may be in a different encoding. If it is, you have to use care during data loading. For example:

• If your text XML is in Unicode (UCS-2, UTF-16), you can assign it to an XML column, variable, or parameter without any problems.
  
• If the encoding is not Unicode and is implicit, because of the source code page, the string code page in the database should be the same as or compatible with the code points that you want to load. If required, use COLLATE. If no such server code page exists, you have to add an explicit XML declaration with the correct encoding.
  
• To use an explicit encoding, use either the varbinary() type, which has no interaction with code pages, or use a string type of the appropriate code page. Then, assign the data to an XML column, variable, or parameter.
  

SELECT CAST( 
CAST (('<?xml version="1.0" encoding="iso8859-1"?>'+ vcdoc) AS VARBINARY (MAX)) 
 AS XML)

Compilation errors are returned from syntactically incorrect Xquery expressions and XML DML statements. The compilation phase checks static type correctness of XQuery expressions and DML statements, and uses XML schemas for type inferences for typed XML. It raises static type errors if an expression could fail at run time because of a type safety violation. Examples of static error are the addition of a string to an integer and querying for a nonexistent node for typed data.

As a deviation from the W3C standard, XQuery run-time errors are converted into empty sequences. These sequences may propagate as empty XML or NULL to the query result, depending upon the invocation context.

Explicit casting to the correct type allows users to work around static errors, although run-time cast errors will be transformed to empty sequences.

The following sections describe type checking in more detail.

Location steps, function parameters, and operators that require singletons will return an error if the compiler cannot determine whether a singleton is guaranteed at run time. This problem occurs frequently with untyped data. For example, the lookup of an attribute requires a singleton parent element. An ordinal that selects a single parent node is sufficient. The evaluation of a node()-value() combination to extract attribute values may not require the ordinal specification. This is shown in the next example.

SELECT nref.value('@genre', 'varchar(max)') LastName
FROM   T CROSS APPLY xCol.nodes('//book') AS R(nref)

SELECT xCol.value('//author/last-name', 'nvarchar(50)') LastName
FROM   T

SELECT xCol.value('//author/last-name[1]', 'nvarchar(50)') LastName
FROM   T

SELECT xCol.value('(//author/last-name/text())[1]', 'nvarchar(50)') LastName
FROM   T

If the type of a node cannot be determined, it becomes anyType. This is not implicitly cast to any other type. This occurs most notably during navigation by using the parent axis, for example, xCol.query('/book/@genre/../price'). The parent node type is determined to be anyType. An element may also be defined as anyType in an XML schema. In both cases, the loss of more precise type information frequently leads to static type errors and requires explicit casting of atomic values to their specific type.

XQuery has a function fn:data() to extract scalar, typed values from nodes, a node test text() to return text nodes, and the function fn:string() that returns the string value of a node. Their use can be confusing. Following are the guidelines for using them correctly in SQL Server 2005. The XML instance <age>12</age> is used for the purpose of illustration.

• Untyped XML: The path expression /age/text() returns the text node "12". The function fn:data(/age) returns the string value "12" and so does fn:string(/age).
  
• Typed XML: The expression /age/text() returns a static error for any simple typed <age> element. On the other hand, fn:data(/age) returns integer 12. The fn:string(/age) yields the string "12".
  

Union types require careful handling because of type checking. Two of the problems are illustrated in the following examples.

<xs:element name="r">
<xs:simpleType>
   <xs:union memberTypes="xs:int xs:float xs:double"/>
</xs:simpleType>
</xs:element>

You can use multiple value() methods on xml data type in a SELECT clause to generate a rowset of extracted values. The nodes() method yields an internal reference for each selected node that can be used for additional query. The combination of the nodes() and value() methods can be more efficient in generating the rowset when it has several columns and, perhaps, when the path expressions used in its generation are complex.

The nodes() method yields instances of a special xml data type, each of which has its context set to a different selected node. This kind of XML instance supports query(), value(), nodes(), and exist() methods and can be used in count(*) aggregations. All other uses cause an error.

SELECT nref.value('(first-name/text())[1]', 'nvarchar(50)') FirstName,
       nref.value('(last-name/text())[1]', 'nvarchar(50)') LastName
FROM   T CROSS APPLY xCol.nodes('//author') AS R(nref)
WHERE  nref.exist('first-name[. != "David"]') = 1

DECLARE name_cursor CURSOR
FOR
   SELECT xCol 
   FROM   T
OPEN name_cursor
DECLARE @xmlVal XML
DECLARE @idoc int
FETCH NEXT FROM name_cursor INTO @xmlVal

WHILE (@@FETCH_STATUS = 0)
BEGIN
   EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlVal
   SELECT   *
   FROM   OPENXML (@idoc, '//author')
          WITH (FirstName  varchar(50) 'first-name',
                LastName   varchar(50) 'last-name') R
   WHERE  R.FirstName != 'David'

   EXEC sp_xml_removedocument @idoc
   FETCH NEXT FROM name_cursor INTO @xmlVal
END
CLOSE name_cursor
DEALLOCATE name_cursor 

Your business logic can be added to XML data in several ways:

• You can write row or column constraints to enforce domain-specific constraints during insertion and modification of XML data.
  
• You can write a trigger on the XML column that fires when you insert or update values in the column. The trigger can contain domain-specific validation rules or populate property tables.
  
• You can write SQLCLR functions in managed code to which you pass XML values, and use XML processing capabilities provided by the System.Xml namespace. An example is to apply XSL transformation to XML data. Alternatively, you can deserialize the XML into one or more managed classes and operate on them by using managed code.
  
• You can write Transact-SQL stored procedures and functions that start the processing on the XML column for your business needs.
  

public static SqlXml TransformXml (SqlXml XmlData, string xslPath) {
   // Load XSL transformation
   XslCompiledTransform xform = new XslCompiledTransform();
   XPathDocument xslDoc = new XPathDocument (xslPath);
   xform.Load(xslDoc);

   // Load XML data 
   XPathDocument xDoc = new XPathDocument (XmlData.CreateReader());

   // Return the transformed value
   MemoryStream xsltResult = new MemoryStream();
   xform.Transform(xDoc, null, xsltResult);
   SqlXml retSqlXml = new SqlXml(xsltResult);
   return (retSqlXml);
} 

SELECT SqlXslTransform (xCol, 'C:\MyFile\xsltransform.xsl')
FROM    T
WHERE  xCol.exist('/book/title/text()[contains(.,"custom")]') =1

XML index entries appear in the catalog view, sys.indexes, with the index "type" 3. The name column contains the name of the XML index.

XML indexes are also recorded in the catalog view, sys.xml_indexes. This contains all the columns of sys.indexes and some specific ones that are useful for XML indexes. The value NULL in the column, secondary_type, indicates a primary XML index; the values 'P', 'R' and 'V' stand for PATH, PROPERTY, and VALUE secondary XML indexes, respectively.

The space use of XML indexes can be found in the table-valued function sys.dm_db_index_physical_stats. It provides information, such as the number of disk pages occupied, average row size in bytes, and number of records, for all index types.. This also includes XML indexes. This information is available for each database partition. XML indexes use the same partitioning scheme and partitioning function of the base table.

XML schema collections are enumerated in the catalog view, sys.xml_schema_collections. The XML schema collection "sys" is defined by the system. It contains the predefined namespaces that can be used in all user-defined XML schema collections without having to load them explicitly. This list contains the namespaces for xml, xs, xsi, fn, and xdt. Two other catalog views are sys.xml_schema_namespaces, which enumerates all namespaces within each XML schema collection, and sys.xml_components, which enumerates all XML schema components within each XML schema.

The built-in function XML_SCHEMA_NAMESPACE, schemaName, XmlSchemacollectionName, namespace-uri, yields an xml data type instance.. This instance contains XML schema fragments for schemas that are contained in an XML schema collection, except the predefined XML schemas.

You can enumerate the contents of an XML schema collection in the following ways:

• Write Transact-SQL queries on the appropriate catalog views for XML schema collections.
  
• Use the built-in function XML_SCHEMA_NAMESPACE(). You can apply xml data type methods on the output of this function. However, you cannot modify the underlying XML schemas.
  

These are illustrated in the following examples.

SELECT XSN.name
FROM    sys.xml_schema_collections XSC JOIN sys.xml_schema_namespaces XSN
    ON (XSC.xml_collection_id = XSN.xml_collection_id)
WHERE    XSC.name = 'myCollection'   

SELECT XML_SCHEMA_NAMESPACE (N'dbo', N'myCollection')

SELECT XML_SCHEMA_NAMESPACE (N'dbo', N'myCollection', 
N'http://www.microsoft.com/books')

You can query XML schemas that you have loaded into XML schema collections in the following ways:

• Write Transact-SQL queries on catalog views for XML schema namespaces.
  
• Create a table that contains an xml data type column to store your XML schemas and also load them into the XML type system. You can query the XML column by using the xml data type methods. Also, you can build an XML index on this column. However, with this approach, the application must maintain consistency between the XML schemas stored in the XML column and the XML type system. For example, if you drop the XML schema namespace from the XML type system, you also have to drop it from the table in order to preserve consistency.