XML Data type (Internally, stored using the varbinary(max))

Xml data type used to store:
1. XML fragments
2. XML documents

 XML fragment lacks the XML declaration () and does not have to have a root element .

The XML data type can be either typed or untyped. Typed simply means that an XML schema collection is assigned to the type to verify its contents.

Let's see an example to clarify the idea:

USE TestDb
GO

CREATE XML SCHEMA COLLECTION EmployeeSchema
AS
'<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="unqualified">
    <xs:element name="employee">
        <xs:complexType>
            <xs:attribute name="FullName" type="xs:string"/>
            <xs:attribute name="IdentityNo" type="xs:integer"/>
        </xs:complexType>
    </xs:element>
</xs:schema>';
GO

CREATE TABLE Employees
(
     EmpID INT IDENTITY PRIMARY KEY
     --this column called as typed because its contents need 
     --to match the schema
    ,FullName XML(DOCUMENT EmployeeSchema)
    --this column called as untyped 
    ,FullName2 XML NULL
)
GO

-- Will succeed:
INSERT Employees VALUES 
('<employee FullName="Fadi Ahmad Abdulwahab" IdentityNo="123234532"/>','<data>fadi</data>');
-- Will not succeed:
INSERT Employees VALUES 
('<employee FullName="Fadi Ahmad Abdulwahab" IdentityNo="fadi"/>','<data>fadi</data>');
GO

Last point need to take care of it:


If you need to change the schema you must first alter all columns that use the schema to instead use untyped XML. Then, you must drop the schema collection, re-create it with the added attributes, and finally alter the columns again to use the schema. And this also harms the performance of Sql server engine because it needs to convert typed data to untyped data.

Note:SQL SERVER 2008 provide you with many of methods that can help you to query or retrieve data as XML.