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.
No comments:
Post a Comment