Bulk insert/update using C# or VB.net and SQL Server

Posted by Krishnasamy2008 under Sql Server on 2/15/2011 | Points: 10 | Views : 9604 | Status : [Member] | Replies : 5
Hi Friends,

I have a huge xml file, below is just an example

<?xml version="1.0" encoding="utf-8"?>
<Accounts>
<Account>
<AcountNumber>12345</AcountNumber>
<EmailAddress>test1@hotmail.com</EmailAddress>
</Account>
<Account>
<AcountNumber>57867867</AcountNumber>
<EmailAddress>test2@rediffmail.com</EmailAddress>
</Account>
</Accounts>
So I would insert/update the above information in database. I want to hit the database once and insert or update the database on the basis of account number and email address etc.
I would like to know the process.
1. May be some c# or Vb code to do that(Already i did it)-No need for now.
2. May be pass the entire xml to the stored procedure and do the processing(I need for second options.In second options i have completed conversion of xml file only and pending for stored procedure.)

Thanks and Regards,
Krishna.K




Responses

Posted by: T.saravanan on: 2/15/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Krishna.K,

Your previous post also same,Already i answered that question..
Any way refer this answer you got some idea...
In your XML file remove following one...
<?xml version="1.0" encoding="utf-8"?> into 

<?xml version="1.0"?>



Declare @sXML xml
SET @sXML=
'<?xml version="1.0"?>
<Accounts>
<Account>
<AcountNumber>12345</AcountNumber>
<EmailAddress>test1@hotmail.com</EmailAddress>
</Account>
<Account>
<AcountNumber>57867867</AcountNumber>
<EmailAddress>test2@rediffmail.com</EmailAddress>
</Account>
</Accounts>'

SELECT
tab.col.value( 'AcountNumber[1]', 'INT'),
tab.col.value( 'EmailAddress[1]', 'nvarchar(50)')
FROM @sXML.nodes('/Accounts/Account') tab(col)


In above query you can get the xml value declare one temp table to pass the value into that temp table.After using that table what do you want done it in your procedure.

Try this...
Any doubt ask me...

Cheers :)

Thanks,
T.Saravanan

Krishnasamy2008, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Madhu.b.rokkam on: 2/15/2011 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi,

You can do this from the DB side by writing stored proc that takes xml input and process that in one shot in DB side.


Thanks and Regards
Madhu

Krishnasamy2008, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Naimishforu on: 2/15/2011 [Member] [MVP] Bronze | Points: 25

Up
0
Down
u using parameterised queries??

Thanks

Thanks,
Naimish
http://dotnetinterview.zxq.net/
http://naimishpandya.blogspot.in/

Krishnasamy2008, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Karthikanbarasan on: 2/15/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down
Hi,

You can use the XML as a stored procedure parameter and loop through XML in the stored procedure to insert it to the db

Thanks
Karthik
www.f5Debug.net

Krishnasamy2008, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Madhu.b.rokkam on: 2/15/2011 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Check this link

http://msdn.microsoft.com/en-us/library/aa276847%28v=sql.80%29.aspx

where the sample is for select statement same way you can update to db also.

Thanks and Regards
Madhu

Krishnasamy2008, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response