Typed vs. Untyped XML
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.
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)