Commit 88b84f1

title: "Compare Typed XML to Untyped XML"

ms.reviewer: randolphwest

ms.date: 05/05/2022

Before you can create typed **xml** variables, parameters, or columns, you must first register the XML schema collection by using [CREATE XML SCHEMA COLLECTION (Transact-SQL)](../../t-sql/statements/create-xml-schema-collection-transact-sql.md). You can then associate the XML schema collection with variables, parameters, or columns of the **xml** data type.

DECLARE @x xml (Production.ProductDescriptionSchemaCollection);

CREATE TABLE T1(

Col1 int,

Col2 xml (Production.ProductDescriptionSchemaCollection));

@ProdDescription xml (Production.ProductDescriptionSchemaCollection)

- An XML schema collection is available only in the database in which it was registered by using [Creating an XML Schema Collection](../../t-sql/statements/create-xml-schema-collection-transact-sql.md).

- 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 [Create Instances of XML Data](../../relational-databases/xml/create-instances-of-xml-data.md). 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.

CREATE TABLE T(Col1 xml

(DOCUMENT Production.ProductDescriptionSchemaCollection));

CREATE TABLE T(Col1 xml(CONTENT Production.ProductDescriptionSchemaCollection));

declare @x xml (DOCUMENT Production.ProductDescriptionSchemaCollection);

[!INCLUDE[sql2008-md](../../includes/sql2008-md.md)] made several extensions to the XML Schema support, including support for lax validation, improved handling of **xs:date**, **xs:time** and **xs:dateTime** instance data, and added support for list and union types. In most cases the changes don't affect the upgrade experience. However if you used an XML Schema collection in [!INCLUDE[ssVersion2005](../../includes/ssversion2005-md.md)] that allowed values of type **xs:date**, **xs:time**, or **xs:dateTime** (or any subtype) then the following upgrade steps occur when you attach your [!INCLUDE[ssVersion2005](../../includes/ssversion2005-md.md)] database to a later version of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]:

1. For every XML column, that is typed with an XML Schema Collection that contains elements or attributes that are typed as either **xs:anyType**, **xs:anySimpleType**, **xs:date** or any of its subtypes, **xs:time** or any subtype thereof, or **xs:dateTime** or any of its subtypes, or are union or list types containing any of these types the following occurs:

1. All XML indices on the column will be disabled.

2. All [!INCLUDE[ssVersion2005](../../includes/ssversion2005-md.md)] values will continue to be represented in the Z timezone, because they've been normalized to the Z timezone.

3. Any **xs:date** or **xs:dateTime** values that are smaller than January 1 of year 1 will lead to a runtime error when the index gets rebuild or an XQuery or XML-DML statements gets executed against the **xml** data type containing that value.

2. Any negative years in **xs:date** or **xs:dateTime** facets or default values in an XML Schema collection will automatically be updated to the smallest value allowed by the base **xs:date** or **xs:dateTime** type (for example, 0001-01-01T00:00:00.0000000Z for **xs:dateTime**).

You can still use a Transact-SQL SELECT statement to retrieve the whole **xml** data type, even if it contains negative years. It's recommended that you replace negative years with a year within the newly supported range or change the type of the element or attribute to **xs:string**.