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
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