SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using OpenXML Part II


Using OpenXML Part II

Author
Message
Vasant Raj
Vasant Raj
Mr or Mrs. 500
Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)

Group: General Forum Members
Points: 545 Visits: 137
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rVasant/usingopenxmlpartii.asp
Daniel Fabia
Daniel Fabia
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 1

Great article. Have you tried this in real world application especially in web application.

Thanks!


Vasant Raj
Vasant Raj
Mr or Mrs. 500
Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)

Group: General Forum Members
Points: 545 Visits: 137
thnx.
I have not tried this in a web application...
but i think it will work the same way... coz this will technique will be useful if you are calling sp's from one another...
Daniel Fabia
Daniel Fabia
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 1

I actually tried this in a .NET Web Application before. I want to insert data in one database call from the a web application.

My big problem with this is the OutOfMemory error/exception when assigning the XML string to a string variable. This problem occurred if the datasource is too large (50,000 thousands record, 30 columns). That string variable is suppose to be passed the stored procedure. Haven't solve this problem and I have to revert back to old way of looping and call a database insert.

What way do you use OpenXML?


Spunky-175138
Spunky-175138
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 3
Great idea!! I hadn't thought to try this. I wish i had read this a couple years back. It would really have been helpful in a few situations
Muthukkalavalli
Muthukkalavalli
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 1
I Need uploading an XML file to SQL Server... The scipt should use MS SQL Server Bulk import capability (OpenXML)...How can i do this.



SDM
SDM
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 401

Thank you. One of those "obvious when you think about it for more than a couple of minutes" situations, but aren't they they the best sometimes?

Good tip.





Vasant Raj
Vasant Raj
Mr or Mrs. 500
Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)

Group: General Forum Members
Points: 545 Visits: 137
You can store your XML data into your database tables by defining a column of VARCHAR type.
You use OPENXML if you want to query your XML data.
Antonio Hernandez
Antonio Hernandez
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 1
Excellent article, congratulations...

Antonio Hernández
SDM
SDM
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 401

Muthukkalavalli,

What you are probably after is the SQLXMLBulkLoad tool, available as a separate download. There's a How-to article at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_6bos.asp and several other resources at other pages. Search for SQLXMLBulkLoad.

Good luck





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search