---
title: "Working With XML Data in Sql Server."  
description: "XML has been widely recognized as a platform-independent format for data representation. It is useful for exchanging information among loosely coupled"  
author: "James Smith"  
published: 2011-07-06  
updated: 2014-09-18  
canonical: https://www.mindstick.com/blog/203/working-with-xml-data-in-sql-server  
category: "database"  
tags: ["database"]  
reading_time: 2 minutes  

---

# Working With XML Data in Sql Server.

XML has been widely [recognized as](https://www.mindstick.com/forum/34352/the-term-update-database-is-not-recognized-as-the-name-of-a-cmdlet-function) a platform-independent format for data representation. It is useful for exchanging information among [loosely coupled](https://www.mindstick.com/forum/161717/how-would-you-design-a-loosely-coupled-system-using-dot-net), disparate system, such as in business to business application and workflow situation.\

Here in this blog we learn that how to work with xml [data in Sql](https://www.mindstick.com/articles/12937/how-to-import-excel-data-in-sql-server-2014) server.

Create a [table in Sql](https://www.mindstick.com/forum/205/find-the-all-column-with-schema-for-any-table-in-sql-server) server which store xml data like following example demonstrate it.

[create table](https://www.mindstick.com/articles/443/how-to-create-table-in-sql-server) StudentXml(ID int [primary key](https://www.mindstick.com/blog/214/primary-key),xCol xml [not null](https://www.mindstick.com/interview/1933/what-is-not-null-constraint))

Here one thing remember that when you create table for storing xml data then the column which store xml information must have type of xml.

Now I will show you two examples for inserting xml in table. First example will show you a demonstration to store data directly and second example will demonstrate you to store data from [xml file](https://www.mindstick.com/articles/75/how-to-read-and-write-xml-file-through-c-sharp) saved in some where [hard disk](https://answers.mindstick.com/qa/102443/how-do-external-ssds-differ-from-traditional-hard-disk-drives).

##### Example to store data directly

\

```
insert into StudentXml values('1',     '          <StudentData>              <Student id="S0001">                   <Name>James</Name>                   <Age>21</Age>                   <City>LA</City>              </Student>               <Student id="S0002">                   <Name>Tori</Name>                   <Age>22</Age>                   <City>LA</City>              </Student>              <Student id="S0001">                   <Name>Keera</Name>                   <Age>23</Age>                   <City>Phonix</City>              </Student>          </StudentData>     ')
```

##### Example to store data from file

\

```
insert into StudentXmlselect '2', xCol from (SELECT * FROM OPENROWSET       (BULK 'E:\XmlData\Student.xml',      SINGLE_BLOB) AS xCol) AS R(xCol)
```

Note: Here you have to remember that xCol is name of column that I have created in table and the words which have a blue color is pre-defined. You have to use them as it is only replace name of xCol in your exercise.

---

Original Source: https://www.mindstick.com/blog/203/working-with-xml-data-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.