Typed vs. Untyped XML

  • Article

You can create variables, parameters, and columns of the xml type.. You can optionally associate a collection of XML schemas with a variable, parameter, or column of xml type. In this case, the xml data type instance is called typed. Otherwise, the XML instance is called untyped.

An XML schema provides the following:

  • Validation constraints
    Whenever a typed xml instance is assigned to or modified, SQL Server validates the instance.
  • Data type information about the instance data
    Schemas provide information about the types of attributes and elements in the xml data type instance. The type information provides more precise operational semantics to the values. For example, decimal arithmetic operations can be performed on a decimal value, but not on a string value. Because of this, typed XML storage can be made significantly more compact than untyped XML.

Before you can create typed xml variables, parameters, or columns, you must first register the XML schema collection by using Create an XML Schema Collection. You can then associate the XML schema collection with variables, parameters, or columns of the xml data type. The following examples show how this is done.

Examples

In the following examples, a two-part naming convention is used for specifying the XML schema collection name. The first part is the AdventureWorks schema name, and the second part is the XML schema collection name.

A. Creating an xml type variable and associating a schema collection with it

The following example creates an xml type variable and associates a schema collection with it. The schema collection specified in the example is already imported in the AdventureWorks database.

DECLARE @x xml (Production.ProductDescriptionSchemaCollection)

B. Creating a table with an xml type column and specifying a schema for the column

The following example creates a table with an xml type column and specifies a schema for the column:

CREATE TABLE T1(
 Col1 int, 
 Col2 xml (Production.ProductDescriptionSchemaCollection))

C. Passing an xml type parameter to a stored procedure

The following example passes an xml type parameter to a stored procedure and specifies a schema for the variable:

CREATE PROCEDURE SampleProc 
  @ProdDescription xml (Production.ProductDescriptionSchemaCollection) 
AS

Note the following about the XML schema collection:

  • An XML schema collection is available only in the database in which it was registered by using Creating an XML Schema Collection.
  • If you cast from a string to a typed xml data type, the parsing also performs validation and typing, based on the XML schema namespaces in the collection specified.
  • You can cast from a typed xml data type to an untyped xml data type, and vice versa.

For more information about other ways to generate XML in SQL Server, see Generating XML Instances. After XML is generated, it can be assigned either to an xml data type variable or stored in xml type columns for additional processing.

In the data type hierarchy, the xml data type appears below sql_variant and user-defined types, but above any of the built-in types.

D. Specifying facets to constrain a typed xml column

For typed xml columns, you can constrain the column to allow only single, top-level elements for each instance stored in it. You do this by specifying the optional DOCUMENT facet when a table is created, as shown in the following example:

CREATE TABLE T(Col1 xml 
   (DOCUMENT Production.ProductDescriptionSchemaCollection))
GO
DROP TABLE T
GO

By default, instances stored in the typed xml column are stored as XML content and not as XML documents. This allows for the following:

  • Zero or many top-level elements
  • Text nodes in top-level elements

You can also explicitly specify this behavior by adding CONTENT facet, as shown in the following example:

CREATE TABLE T(Col1 xml(CONTENT Production.ProductDescriptionSchemaCollection))
GO -- Default

Note that you can specify the optional DOCUMENT/CONTENT facets anywhere you define xml type (typed xml). For example, when you create a typed xml variable, you can add the DOCUMENT/CONTENT facet, as shown in the following:

declare @x xml (DOCUMENT Production.ProductDescriptionSchemaCollection)

See Also

Concepts

Generating XML Instances
XML Data Modification Language (XML DML)
xml Data Type
Sample XML Applications

Other Resources

xml Data Type Methods

Help and Information

Getting SQL Server 2005 Assistance