XML in SQL Server

Written By:- Isha Malhotra

XML in SQL

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);

Insert xml data into table in SQL Server

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));

Select XML Data in SQL Server

		
select * from XmlExample

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

		
select xml in sql server

		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: - 

		
select xml from table

		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.

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: -

		
xml with raw

		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: - 

		
xml with raw with output

		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

		
		
		
xml output

		Figure 5

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

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: -

		
xml output with auto

		Figure 6

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

Email Address

For any query you can send mail at info@techaltum.com
Thanks