7.1. xml Data Type

The new xml data type supports storing both XML documents and fragments in the database. An XML fragment is an XML instance that does not have a single top-level (root) element. You can create columns, parameters, and variables of the new xml type and store XML instances in them. xml data type instances have a maximum size of 2GB.

An XML schema collection can be associated with a column, parameter, or variable of xml data type. An xml data type with an associated schema is referred to as being typed. The XML schema validates xml data type instances against constraints and provides data type information about the elements and attributes in the instance. The schema also helps SQL Server optimize data storage. The XML schema collection must be registered with SQL Server before it can be used to create typed xml instances. Registration is described in the "Managing XML Schema Collections" section later in this chapter.

If you want to use xml data type query methods against xml data type columns or variables, or want to create or rebuild indexes on XML data type columns, you must set the SQL Server 2005 database configuration options listed in Table 7-1. By default, the values are set as required. They may be changed using the SET statement. You can check the values for each database by executing the following T-SQL query:

    SELECT * FROM sys.databases

Table 7-1. SQL Server 2005 database configuration option settings for using xml data type query methods

SET option	Required value
ANSI_NULLS	ON
ANSI_PADDING	ON
ANSI_WARNINGS	ON
ARITHABORT	ON
CONCAT_NULL_YIELDS_NULL	ON
NUMERIC_ROUNDABORT	OFF
QUOTED_IDENTIFIER	ON

7.1.1. Creating xml Data Type Columns and Variables

The following subsections describe how to create xml data type columns and T-SQL variables.

7.1.1.1. Columns

Use the CREATE TABLE statement to create a table that contains one or more xml data type columns. The syntax for creating a table with an xml data type column is:

    CREATE TABLE table_name (
      ...

      xml_column_name xml
      [ [DOCUMENT | CONTENT] (schema_name.xml_schema_collection_name ) ],

      ...
    )

where:

table_name

The name of the table in the database.

xml_column_name

The name of the xml data type column in the table.

[ DOCUMENT | CONTENT ]

The DOCUMENT facet constrains the typed xml data type instance to allow only a single top-level element.

The CONTENT facet explicitly allows the typed xml data type instance to have zero or more top-level elements and text nodes in top-level elements. The default is CONTENT.

schema_name

The XML schema in the XML schema collection to associate with the xml data type column.

xml_schema_collection_name

The name of an existing XML schema collection.

The following example creates a table named xmlTest that has an untyped xml data type column named xmlCol. The example also creates a clustered primary key on the ID column for use in later examples.

    USE ProgrammingSqlServer2005

    CREATE TABLE xmlTable
    (
      ID int NOT NULL,
      xmlCol xml,
    CONSTRAINT PK_xmlTable
      PRIMARY KEY CLUSTERED (ID)
    )

You can query the sys.columns catalog view to get information about the xml data type columns in a database. The following query returns the xml data type columns in a database:

    USE AdventureWorks

    SELECT o.name, c.* FROM sys.columns c
    JOIN sys.objects o ON c.object_id = o.object_id
    WHERE EXISTS
        (SELECT * FROM sys.types t
        WHERE c.system_type_id = t.system_type_id AND
        name='xml')

Partial results for running the query against the AdventureWorks database are shown in Figure 7-1.

Figure 7-1. Results for sys.columns catalog view example

This query joins the sys.columns catalog view to the sys.objects catalog view to return the table or view that the xml data type column belongs to as the first column in the result set. The EXISTS clause filters the results to include only xml data types.

7.1.1.2. Variables

The DECLARE statement is used to create T-SQL variables . The syntax for creating an xml data type variable is:

    DECLARE variable_name [AS] xml
      [ ( [ DOCUMENT | CONTENT] schema_name.xml_schema_collection_name ) ]

where:

variable_name

The name of the xml data type variable. The variable name must be prefixed with an ampersand (@).

The other parameters are the same as those discussed in the preceding "Columns" section.

The following example uses an xml data type variable to insert a row into the xmlTable created in the preceding "Columns" section:

    USE ProgrammingSqlServer2005

    DECLARE @xmlVar xml
    SET @xmlVar = '<rootNode><childElement/></rootNode>'

    INSERT INTO xmlTable (ID, xmlCol)
    VALUES (1, @xmlVar)

The following example creates a stored procedure to modify a row in the xmlTable table:

    USE ProgrammingSqlServer2005
    GO

    CREATE PROCEDURE updateXmlTable
        @ID int,
        @xmlCol xml
    AS
    BEGIN
        UPDATE xmlTable
        SET xmlCol = @xmlCol
        WHERE ID = @ID
    END

Execute the stored procedure using the following code to update the xmlCol value for the row with ID = 1:

    USE ProgrammingSqlServer2005

    SELECT * FROM xmlTable

    EXEC updateXmlTable 1, '<newRootNode><newChildElement/></newRootNode>'

    SELECT * FROM xmlTable

The before and after result sets returned by the query are shown in Figure 7-2.

Figure 7-2. Result sets for stored procedure using xml data type example

7.1.2. Limitations

The xml data type has the following limitations :

• It cannot be stored in a sql_variant instance.

• It cannot be cast or converted to the text or ntext data types.

• It does not support PRIMARY KEY, FOREIGN KEY, UNIQUE, COLLATE, or RULE constraints.

• Only string data types can be cast to an xml data type.

• It cannot be compared or sorted and, as a result, cannot be used in a GROUP BY clause.

• It cannot be used in distributed partitioned views. Partitioned views join horizontally partitioned data from a set of member tables, making it appear as one table. In a distributed partitioned view, at least one of the tables resides on a remote server instance.

• It cannot be used as a parameter to any scalar built-in function other than ISNULL, COALESCE, or DATALENGTH.

• It cannot be used as a key column in an index.

• XML declaration processing instructions (PIs) (<?xml ... ?>) are not preserved when the XML instance is stored in the database. All other PIs in the XML instance are preserved.

• The order of attributes in XML instances stored in xml type columns is not preserved.

• By default, insignificant whitespace is not preserved. Whitespace can be preserved for an xml data type instance by specifying the optional style argument to the CONVERT function.

• Single quotation marks (') and double quotation marks (") around attribute values are not preserved because the data is stored as name/value pairs in the database.

• Namespace prefixes are not preserved and may change when xml data type instances are retrieved.