Wednesday 16 March 2011

How To Insert Bulk Data In DataBase Using Xml

This is a Xml Text
declare @Xml as xml
set @Xml ='<DocumentElement>
<RootName>
<Id>1</Id>
<Name>Sudipta Sanyal</Name>
<Address>KolKata</Address>
<Phone>1234567890</Phone>
</RootName>
<RootName>
<Id>2</Id>
<Name>Sudipta Sanyal2</Name>
<Address>KolKata2</Address>
<Phone>12345678902</Phone>
</RootName>
</DocumentElement>'
-------------------This is a Temp Table
declare @Temp as table
(
Id int,
Name nvarchar(50),
Address nvarchar(50),
Phone nvarchar(50)
)
----------------This is The query by which You can insert into database bulk data
insert into @Temp (Id,Name,Address,Phone)
select
cast (SS.query('data(Id)') as nvarchar(200)) as Id,
cast (SS.query('data(Name)') as nvarchar(200)) as Name,
cast (SS.query('data(Address)') as nvarchar(200)) as Address,
cast (SS.query('data(Phone)') as nvarchar(200)) as Phone
from @Xml.nodes('DocumentElement/RootName') as Sudipta(SS)
--------------If u want to select data then
select * from @Temp


No comments:

Post a Comment

Get all non-clustered indexes

DECLARE cIX CURSOR FOR     SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID         FROM Sys.Indexes SI             ...