blog

Home / DeveloperSection / Blogs / Working With XML Data in Sql Server.

Working With XML Data in Sql Server.

James Smith5777 06-Jul-2011

XML has been widely recognized as a platform-independent format for data representation. It is useful for exchanging information among loosely coupled, 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 server.

Create a table in Sql server which store xml data like following example demonstrate it.

create table StudentXml(ID int primary key,xCol xml not null)

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 saved in some where hard disk.

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 StudentXml
select '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.


Updated 18-Sep-2014

Leave Comment

Comments

Liked By