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. 
  1. CREATE TABLE [dbo].[myxml](  
  2. [id] [intNOT NULL,  
  3. [xmlinfo] [xml] NOT NULL)  
So here is the table.

 

Now i am creating xml data and inserting to my table "myxml".
  1. INSERT INTO myxml  
  2.   VALUES (3, '<bike>  
  3. <company>  
  4. Bajaj  
  5. </company>  
  6. <Name>  
  7. Bajaj Chetak  
  8. </Name>  
  9. <amount>  
  10. 30000  
  11. </amount>  
  12. </bike>')  
  13. INSERT INTO myxml  
  14.   VALUES (3, '<bike type="normal">  
  15. <company>  
  16. Honda  
  17. </company>  
  18. <Name>  
  19. Honda Shine  
  20. </Name>  
  21. <amount>  
  22. 75000  
  23. </amount>  
  24. </bike>')  
  25. INSERT INTO myxml  
  26.   VALUES (4, '<bike type="Racing">  
  27. <company>  
  28. Yamaha  
  29. </company>  
  30. <Name>  
  31. Champian  
  32. </Name>  
  33. <amount>  
  34. 350000  
  35. </amount>  
  36. </bike>')  
  37. INSERT INTO myxml  
  38.   VALUES (5, '<bike type="Luggage">  
  39. <company>  
  40. Bajaj  
  41. </company>  
  42. <Name>  
  43. M80  
  44. </Name>  
  45. <amount>  
  46. 23000  
  47. </amount>  
  48. </bike>')  
  49. INSERT INTO myxml  
  50.   VALUES (1, '<bike type="Luggage">  
  51. <company>  
  52. TVS  
  53. </company>  
  54. <Name>  
  55. LUNA  
  56. </Name>  
  57. <amount>  
  58. 23000  
  59. </amount>  
  60. </bike>')  
This will insert xml data in table.

To retrieve the data we have the following query. 
  1. select * from myxml   
 

Thus in this way we can save XML data.
For retriving data we can write query like this.
  1. SELECT xmlinfo  
  2. FROM myxml  
  3. 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.


Similar Articles