The xml data type is a built-in data type in SQL Server and is somewhat similar to other built-in types such as int and varchar. As with other built-in types, you can use the xml data type as a column type when you create a table as a variable type, a parameter type, a function-return type, or in CAST and CONVERT. Using these is shown in the following examples.

Example A

CREATE TABLE T1(Col1 int primary key, Col2 xml) 

Example B

DECLARE @x xml 

declare @x xml (Sales.StoreSurveySchemaCollection)

Example C

CREATE PROCEDURE SampleProc(@XmlDoc xml) AS ... 

In a table, you can assign a default XML instance to a column of xml type. You can provide the default XML in one of two ways, as shown in the following.

Example A

CREATE TABLE T (XmlColumn xml default N'<element1/><element2/>')

Example B

CREATE TABLE T (XmlColumn xml 
                  default CAST(N'<element1/><element2/>' AS xml))

CREATE TABLE T (XmlColumn xml NOT NULL)

When you create columns of xml type, you can define column-level or table-level constraints. However, you cannot use the XML data type methods when you specify constraints. An alternative is to create a wrapper, user-defined function to wrap the xml data type method and specify user-defined function in the check constraint as shown in the following example.

In the following example, the constraint on Col2 specifies that each XML instance stored in this column must have a <ProductDescription> element that contains a ProductID attribute. This constraint is enforced by this user-defined function:

CREATE FUNCTION my_udf(@var xml) returns bit
AS BEGIN 
RETURN @var.exist('/ProductDescription/@ProductID')
END
GO

Note that the exist() method of the xml data type returns 1 if the <ProductDescription> element in the instance contains the ProductID attribute. Otherwise, it returns 0.

Now, you can create a table with a column-level constraint as follows:

CREATE TABLE T (
    Col1 int primary key, 
    Col2 xml check(dbo.my_udf(Col2)=1))
GO

The following insert succeeds:

INSERT INTO T values(1,'<ProductDescription ProductID="1" />')

Because of the constraint, the following insert fails:

INSERT INTO T values(1,'<Product />')

The ALTER TABLE statement supports the xml data type. For example, you can alter any string type column to the xml data type. Note that in these cases, the documents contained in the column must be well formed. Also, if you are changing the type of the column from string to typed xml, the documents in the column are validated against the specified XSD schemas.

CREATE TABLE T (Col1 int primary key, Col2 nvarchar(max))
GO
INSERT INTO T 
VALUES (1, '<Root><Product ProductID="1"/></Root>')
GO
ALTER TABLE T 
ALTER COLUMN Col2 xml
GO

You can change an xml type column from untyped XML to typed XML. For example:

CREATE TABLE T (Col1 int primary key, Col2 xml)
GO
INSERT INTO T 
values (1, '<p1:ProductDescription ProductModelID="1" 
xmlns:p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription">
            </p1:ProductDescription>')
GO 
-- Make it a typed xml column by specifying a schema collection.
ALTER TABLE T 
ALTER COLUMN Col2 xml (Production.ProductDescriptionSchemaCollection)
GO

Note:
The script will run against AdventureWorks database, because the XML schema collection, Production.ProductDescriptionSchemaCollection, is created as part of the AdventureWorks database.

In the previous example, all the instances stored in the column are validated and typed against the XSD schemas in the specified collection. If the column contains one or more XML instances that are invalid with regard to the specified schema, the ALTER TABLE statement will fail and you will not be able to change your untyped XML column into typed XML.

Note:
If a table is large, modifying an xml type column can be costly. This is because each document must be checked for being well formed and, for typed XML, must also be validated.

For more information about typed XML, see Typed vs. Untyped XML.

You can use an xml type column to create views. The following example creates a view in which the value from an xml type column is retrieved using the value() method of the xml data type.

-- Create the table.
CREATE TABLE T (
    ProductID          int primary key, 
    CatalogDescription xml)
GO
-- Insert sample data.
INSERT INTO T values(1,'<ProductDescription ProductID="1" ProductName="SomeName" />')
GO
-- Create view (note the value() method used to retrieve ProductName 
-- attribute value from the XML).
CREATE VIEW MyView AS 
  SELECT ProductID,
         CatalogDescription.value('(/ProductDescription/@ProductName)[1]', 'varchar(40)') AS PName
  FROM T
GO 

Execute the following query against the view:

SELECT * 
FROM   MyView

This is the result:

ProductID   PName      
----------- ------------
1           SomeName 

Note:
The xml data type cannot be used in Distributed Partitioned Views.

XML instances can appear as a source for a computed column, or as a type of computed column. For example, in the following CREATE TABLE statement, an xml type column (col2) is computed from col1:

CREATE TABLE T(col1 varchar(max), col2 AS CAST(col1 AS xml) )  

The xml data type can also appear as a source in creating a computed column, as shown in the following CREATE TABLE statement:

CREATE TABLE T (col1 xml, col2 as cast(col1 as varchar(1000) )) 

You can create a computed column by extracting a value from an xml type column as shown in the following example. Because the xml data type methods cannot be used directly in creating computed columns, the example first defines a function (my_udf) that returns a value from an XML instance. The function wraps the value() method of the xml type. The function name is then specified in the CREATE TABLE statement for the computed column.

CREATE FUNCTION my_udf(@var xml) returns int
AS BEGIN 
RETURN @var.value('(/ProductDescription/@ProductModelID)[1]' , 'int')
END
GO
-- Use the function in CREATE TABLE.
CREATE TABLE T (col1 xml, col2 as dbo.my_udf(col1) )
GO
-- Try adding a row. 
INSERT INTO T values('<ProductDescription ProductModelID="1" />')
GO
-- Verify results.
SELECT col2, col1
FROM T
 

As in the previous example, the following example defines a function to return an xml type instance for a computed column. Inside the function, the query() method of the xml data type retrieves a value from an xml type parameter.

CREATE FUNCTION my_udf(@var xml) 
  RETURNS xml AS 
BEGIN 
   RETURN @var.query('ProductDescription/Features')
END

In the following CREATE TABLE statement, Col2 is a computed column that uses the XML data (<Features> element) that is returned by the function:

CREATE TABLE T (Col1 xml, Col2 as dbo.my_udf(Col1) )
-- Insert a row in table T.
INSERT INTO T VALUES('
<ProductDescription ProductModelID="1" >
  <Features>
    <Feature1>description</Feature1>
    <Feature2>description</Feature2>
  </Features>
</ProductDescription>')
-- Verify the results.
SELECT *
FROM T

Reference

Concepts

Other Resources

Help and Information