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.
Sushant Mishra
17-Jul-2017It is great to associate with such a blog.