When we need to store data in SQL Server in XML format .in this situation we can use XML Data Type.
Create the following table to store data: -
create table XmlExample(CustID int, Prod_Detail xml);
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 * from XmlExample
If you run this query then you will get following output:-
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: -
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.
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: -
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: -
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
Now it’s showing other column data as Elements instead of attributes.
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: -
Hope you enjoy the article. In the next article I will discuss the more feature of XML in SQL Server.
For any query you can send mail at firstname.lastname@example.org