Typed XML vs UnTyped XML

XML stands for EXtensible Markup Language. XML tags are not predefined. You must define your own tags. It is used so much in web designing and managing structure and data. XML introduced in SQL server as well to manage extensive data with your specific structure as you can define relational tables in database. From SQL SERVER 2005 xml introduced as data type both for column and variable.

Typed XML vs UnTyped XML

When we define an xml as a variable, parameter then it is called as UnTyped but when we assign some defined structure with it, it becomes typed. Msdn says “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.”. Untyped xml can verify the opening and closing structure of XML basics like tagging etc but cannot verify structure.

 Typed xml when assigned to a column, SQL Server validates the instance. 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.

To create an Untyped xml you only needs to declare an xml variable and assign an xml to it. as shows in following code and screen shot.

You can also create Untyped XML using output of any query using For XML as shown in following screen

To create typed XML you needs to declare XML schema collection first and then assign it to a column of XML data type. Then you can insert data according to assigned schema structure. XML data type stores upto 2GB of xml data. When Schema against XML or Typed XML will require more space to store data and implement Xml Indexes, discussed later. 

There are some general limitation applied to XML discussed below

The stored representation of xml data type instances cannot exceed 2 GB.

·         It cannot be used as a subtype of a sql_variant instance.

·         It does not support casting or converting to either text or ntext. Use varchar(max) or nvarchar(max) instead.

·         It cannot be compared or sorted. This means an xml data type cannot be used in a GROUP BY statement.

·         It cannot be used as a parameter to any scalar, built-in functions other than ISNULL, COALESCE, and DATALENGTH.