Use of XML in SQL Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

Use of XML in SQL Server

When we need to store data in SQL Server in XML format .in this situation we can use XML Data Type.

For Example:-

Create the following table to store data:-

create table XmlExample(CustID int, Prod_Detail xml);

Insetion of DATA

We can insert as follows:-

insert into XmlExample values(1,'<TechAltum><Product><ID>P-101</ID><Name>Mobile</Name><Price>10,000</Price></Product></TechAltum>');

or

insert into XmlExample values(2,cast('<TechAltum><Product><ID>P-102</ID><Name>AC</Name><Price>18,000</Price></Product></TechAltum>' as XML));

Selection of DATA

select * from XmlExample

if you run this query then you will get following output:-

Figure 1

In prod_detail it showing data as XML. It is a link. If you put cursor on it and click on it, it will show the xml file as follows:-

 

Figure 2

Use of For Clause with XML

When we select data which will be in XML format, SQL Server allow us to use For Clause with some modes. These modes and their use are following:-

Note:-to explain these modes I added one column Address in this table. Now this table has 3 columns.

1.  RAW

As we seen that when we use simple select command it shows xml file as we stored. But when we use select query with RAW mode it shows XML file in different manner. It added one more node in xml file with the name of Row and adds other column data with this node as attribute.

For Example:-

select * from XmlExample for XML RAW

the output of this command as follows:-

Figure 3

As you can see that it showing only one column as we have 3 columns in this table and we select * which means all column of data.

Confused?  Now click on this link you will see the output as follows:-

 

Figure 4

It added other column as attribute in row node and added in the xml file.

As we seen that it showing other data in xml as attribute, But if we want to show this data as element then we can also use Element with raw.

For example:-

select * from XmlExample for XML raw, elements

it will show output as follows:-

Figure 5

Now it’s showing other column data as Elements instead of attributes.

2.  Auto

It works same like raw but in raw when xml create its node name is Row. But in Auto it creates the node name with table name.

For example:-

select * from XmlExample for XML auto

it will show output as follows:-

Figure 6

Hope you enjoy the article. In the next article I will discuss the more feature of XML in SQL Server.