Your browser does not support JavaScript! Please enable script of your browser.
 
"Coding , A Rhythmic Literary Job"
Handy Tricks For Beginners & Professionals
SQL Server 2012, 2008 - Tutorial, Article, Archive, Pdf, Handy Tricks, Training, Code Snippets, Reference Manual, T-SQL, PL/SQL, Development, Administration, Performance Tuning, Backup, Recovery, Reporting, Analysis, Security, XML, CLR Objects

SQL Server XML Data Type

Posted By : Shailendra Chauhan, 05 Mar 2012
Updated On : 25 Sep 2012
  Version Support : SQL Server 2005,2008,2012
Keywords : XML Data Type pdf, XML Data Type Limitation, XML RAW, XML AUTO, XML EXPLICIT, Query XML Data

xml data type was introduced in SQL Server 2005 to work with XML data. Using this data type, we can store XML in its native format and can also query/modify the xml data within the xml. We can use xml data type like as:

  1. Variable

  2. Field/Column in a table

  3. Parameter in the user-defined function (UDF) or stored procedure(SP)

  4. return value from a UDF or SP

We can define xml data type field to NOT NULL or we can provide a default value to it.

Limitation Of XML Data type

  1. We can’t directly compare an instance of the XML data type to another instance of the XML data type. For equality comparisons we first need to convert the XML type to a character type.

  2. We can’t use GROUP BY or ORDER BY with an XML data type column.

  3. We can’t use XML data type field as a primary key, Unique key and foreign key.

  4. We can’t define XML data type field with COLLATE keyword.

Query XML Data

Suppose we have following tables in database. Using these tables we will produce query result as an xml

 CREATE TABLE Department (
 DeptID int IDENTITY(1,1) primary key ,
 DeptName varchar(50) NULL,
 Location varchar(50) NULL )
CREATE TABLE Employee (
 EmpID int IDENTITY(1,1) NOT NULL,
 EmpName varchar(50) NULL,
 Address varchar(100) NULL,
 DeptID int foreign Key references Department(DeptID) 
 )
--Now Insert data into these tables
INSERT INTO Department (DeptName,Location)VALUES('HR','Delhi')
INSERT INTO Department (DeptName,Location)VALUES('IT','Delhi')
INSERT INTO Department (DeptName,Location)VALUES('Technical','Delhi')
INSERT INTO Employee( EmpName,Address,DeptID)VALUES('Shailendra','Noida',2)
INSERT INTO Employee( EmpName,Address,DeptID)VALUES('Mohan','Noida',2)
INSERT INTO Employee( EmpName,Address,DeptID)VALUES('Vipul','Noida',1)
INSERT INTO Employee( EmpName,Address,DeptID)VALUES('Mrinal','Noida',3) 

We can retrieve data table records as xml data using FORXML clause in SELECT statement. In FORXML Clause we can define three xml mode.

  1. AUTO

    It generates output with both element and attribute features in combination with a sub query.

     SELECT DeptName, EmpID
    FROM Employee AS Emp JOIN Department AS Dept
    ON Emp.DeptID= Dept.DeptID
    FOR XML AUTO; 
    Output:
     <Dept DeptName="IT">
     <Emp EmpID="1" />
     <Emp EmpID="2" />
    </Dept>
    <Dept DeptName="HR">
     <Emp EmpID="3" />
    </Dept>
    <Dept DeptName="Technical">
     <Emp EmpID="4" />
     <Emp EmpID="5" />
    </Dept> 
  2. EXPLICIT

    It converts the rowset that is result of the query execution, into an XML document. This mode provides more control over the format of the XML means in which format you want xml you need to define that format in select query.

     SELECT
     1 tag,
     NULL parent,
     EmpID [employee!1!ID],
     EmpName [employee!1!name],
     NULL [order!2!date],
     NULL [department!3!name]
    FROM Employee
    UNION ALL 
    SELECT
     3,
     1,
     EmpID,
     NULL,
     NULL,
     DeptName
    FROM Employee e JOIN Department d
    ON e.DeptID=d.DeptID
    ORDER BY 3, 1
    FOR XML EXPLICIT; 
    Output
     <employee ID="1" name="Shailendra">
     <department name="IT" />
    </employee>
    <employee ID="2" name="Mohan">
     <department name="IT" />
    </employee>
    <employee ID="3" name="Vipul">
     <department name="HR" />
    </employee>
    <employee ID="4" name="Mrinal">
     <department name="Technical" />
    </employee>
    <employee ID="5" name="Jitendra">
     <department name="Technical" />
    </employee> 
  3. RAW

    It produce a single element or the optionally provided element name for each row in the query result set that is returned by select statement.

     SELECT Emp.EmpID, Dept.DeptName
    Employee as Emp JOIN Department as Dept
    ON Emp.DeptID= Dept.DeptID
    FOR XML RAW; 
    Output
     <row EmpID="1" DeptName="IT" />
    <row EmpID="2" DeptName="IT" />
    <row EmpID="3" DeptName="HR" />
    <row EmpID="4" DeptName="Technical" />
    <row EmpID="5" DeptName="Technical" /> 
Summary

In this article I try to explain the XML Data Type with example. I hope after reading this article your will be aware of XML Datatype in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

About the Author
Shailendra Chauhan works as Software Analyst at reputed MNC and has more than 5 years of hand over Microsoft .NET technologies. He is a .NET Consultant and is the founder & chief editor of www.dotnet-tricks.com and www.dotnetinterviewtricks.com blogs. He is an author of book ASP.NET MVC Interview Questions and Answers.
He loves to work with web applications and mobile apps using Microsoft technology including ASP.NET, MVC, C#, SQL Server, WCF, Web API, Entity Framework,Cloud Computing, Windows Azure, jQuery, jQuery Mobile, Knockout.js, Angular.js and many more web technologies. More...
 
 
Recommended Articles!
 
 
Search Articles
 
 
Subscribe & follow Us
 
 
 
Free ASP.NET MVC Interview Book
Learn In Hindi
Browse By Category
 
Recent Articles
 
Popular Articles
 
Like us on Facebook
 
Featured AD
Join our Training programs in Delhi/Noida on ASP.NET MVC, WCF, EF, jQuery - call us    +91-9871749695