Storing XML Data In SQL Server

XML

XML stands for EXtensible Markup Language. XML was designed for storing and transporting data. XML language is very simple and tag based language. Here I am trying to explain why we need XML data through this figure. 

From this figure I can say that to communicate with any kind of application or technology we need XML or JSON data. This is same as if you know "English" language you can go to any country and can communicate with any people in the world.

In software sector XML is just like english language if the data is transferred in xml, then the data is understood by any type of application.

 

Here in this figure I am explaining it very clearly.

So here data from one application when exposed by XML\Json, it can be understood by other application.

Therefore XML data is very important. We can save XML data in our SQL Server database. To save XML data we have XML datatype in SQL Server.

Here I am creating a table to show how to save XML data in SQL Server. 

CREATE TABLE [dbo].[myxml](
[id] [int] NOT NULL,
[xmlinfo] [xml] NOT NULL)

So here is the table.

 

Now i am creating xml data and inserting to my table "myxml".

INSERT INTO myxml
  VALUES (3, '<bike>
<company>
Bajaj
</company>
<Name>
Bajaj Chetak
</Name>
<amount>
30000
</amount>
</bike>')
INSERT INTO myxml
  VALUES (3, '<bike type="normal">
<company>
Honda
</company>
<Name>
Honda Shine
</Name>
<amount>
75000
</amount>
</bike>')
INSERT INTO myxml
  VALUES (4, '<bike type="Racing">
<company>
Yamaha
</company>
<Name>
Champian
</Name>
<amount>
350000
</amount>
</bike>')
INSERT INTO myxml
  VALUES (5, '<bike type="Luggage">
<company>
Bajaj
</company>
<Name>
M80
</Name>
<amount>
23000
</amount>
</bike>')
INSERT INTO myxml
  VALUES (1, '<bike type="Luggage">
<company>
TVS
</company>
<Name>
LUNA
</Name>
<amount>
23000
</amount>
</bike>')

This will insert xml data in table.

To retrieve the data we have the following query. 

select * from myxml

 

Thus in this way we can save XML data.

For retriving data we can write query like this.

SELECT xmlinfo
FROM myxml
WHERE xmlinfo.exist ('/bike[@type = "Luggage"]') = 1

 

So, it will retrieve the data where bike type is luggage.

Now when we click on any of them it will show the complete details.

 
Thus in this way we can retrieve particular XML data from SQL Server.