xml Data Type
The xml data type lets you store XML documents and fragments in a SQL Server database. An XML fragment is an XML instance that is missing a single top-level element. You can create columns and variables of the xml type and store XML instances in them. Note that the stored representation of xml data type instances cannot exceed 2 GB.
You can optionally associate an XML schema collection with a column, a parameter, or a variable of the xml data type. The schemas in the collection are used to validate and type the XML instances. In this case, the XML is said to be typed.
The xml data type and associated methods help integrate XML into the relational framework of SQL Server. For more information, see xml Data Type Methods.
Note
This topic describes untyped XML. For information about typed XML, see Typed vs. Untyped XML.
Creating xml Data Type Variables and Columns
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 an xml type column:
CREATE TABLE T1(Col1 int primary key, Col2 xml)
Example B
Create a variable of xml type:
DECLARE @x xml
Create a typed xml variable by specifying an XML schema collection:
declare @x xml (Sales.StoreSurveySchemaCollection)
Example C
Pass an xml type parameter to a stored procedure:
CREATE PROCEDURE SampleProc(@XmlDoc xml) AS ...
You can use XQuery to query XML instances stored in columns, parameters, or variables. You can also use the XML Data Manipulation Language (XML DML) to apply updates to the XML instances. Because the XQuery standard did not define XQuery DML at the time of development, SQL Server introduces XML Data Modification Language extensions to XQuery. These extensions allow you to perform insert, update, and delete operations.
Assigning Defaults
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
Provide the default XML as an XML constant (the string is implicitly CAST to xml type):
CREATE TABLE T (XmlColumn xml default N'<element1/><element2/>')
Example B
Provide the default XML as an explicit CAST to xml:
CREATE TABLE T (XmlColumn xml
default CAST(N'<element1/><element2/>' AS xml))
SQL Server also supports NULL and NOT NULL constraints on columns of xml type. For example:
CREATE TABLE T (XmlColumn xml NOT NULL)
Specifying Constraints
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 />')
Modifying Tables
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="https://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.
Creating Views
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.
Using XML in Computed Columns
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
See Also
Reference
Using FOR XML and OPENXML to Publish and Process XML Data
Concepts
Typed vs. Untyped XML
Generating XML Instances
XML Data Modification Language (XML DML)
Indexes on xml Data Type Columns
Sample XML Applications