| Author |
Topic |
|
khalik
Constraint Violating Yak Guru
443 Posts |
 Posted - 2009-01-13 : 12:10:24
|
| hi My app has to updated the DB regularly from external source.i have dataset which has around 100 table, and each table with huge no of rows. to avoid looping and saving time i planned to use xml but if have to pass paramater then max len supported is 8000is there a way to store dataset to a xml file and then process xml file from DB in one shot.objective is to complete db operation for one table at one time or DS one time. db will be on local system and using 2005. regardsKhalik======================================Ask to your self before u ask someone |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-13 : 12:15:11
|
| if you're using 2005 you should be able to use varchar(max) parameter. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-13 : 12:17:00
|
You can pass XML as parameter too.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-13 : 12:17:28
|
| also you can use xml type parameters and pass xml value through it. then in sp, use nodes(),value() ... functions to extract data from xml and insert to your table. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-13 : 12:22:52
|
| Also this:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=117490 |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-01-13 : 12:29:34
|
| Thanks guys, that was quick.As i said that i have many table with huge structure and data. passing xml type parameters is the only best option?Please suggest.======================================Ask to your self before u ask someone |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-13 : 12:31:52
|
| you mean you need to create tables and populate data from single xml document? |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-01-13 : 12:34:52
|
| no the other way round. data which is in xml file send it to DB. insert or update========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-13 : 12:37:46
|
quote:
Originally posted by khalik
no the other way round. data which is in xml file send it to DB. insert or update========================================Project Manager who loves to code.===============Ask to your self before u ask someone
data for one table or all table together? if latter whats the feild enables you to distinguish each tables data? also will table info be known before (which all tables data are included) or does that need to be determined dynamically? |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-01-13 : 12:44:34
|
| i need a better efficent solution.all table together sounds good. if it good i can find out which all tables data is passed and whole structure is passed i think i can manage..the datset return to xml file was more that 120MB so please suggestjust found this link http://www.codeproject.com/KB/database/PassingArraysIntoSPs.aspx========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-13 : 12:49:23
|
| i still cant understand why you need to update all tables simultaneously through same xml |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-13 : 12:51:23
|
I am curious how these 120 MB will be transferred to database.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-01-14 : 09:26:18
|
| I am not updating all tables with same xml data.. 100 table will have 100 datatables in the dataset.what i ment was that i have a logic to figure out which is the tablelet me simple.i have a dataset with 100+ table data. what will the efficent way to get these values to db.========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-01-14 : 09:50:10
|
| One issue you should be aware of is memory exception errors when dealing with large xml files. Obviously if you have very high spec servers this may not be an issue - but we have encountered problems dealing with xml files of around 20MB with Windows Server 2003, 8 processors and 8GB RAM /PAE and 3GB switches etc.This google search shows ours was not an isolated case:http://www.google.co.uk/search?hl=en&q=%22sql+server+2005%22+xml+%22System.OutOfMemoryException%22++file+size&meta=Personally I think large XML can create more problems than it solves. If it is essential to use xml, I would try to break it up and save across a number of rows / columns inside a table. Then query the table to retrieve a smaller amount of xml, rather than passing in a large xml file. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-01-14 : 10:01:06
|
| It not that i want to process all at one shot, i can process table by table. if that a better approach using xml type parameters is the only option or any better way. i can save the file to local drive and db process it is that do a ble?========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-01-15 : 07:00:19
|
| can we close this thread on a Conclusion. what to people vote for....?========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-01-15 : 09:23:07
|
| Not knowing enough about the specifics I can't say what method is best in your case. But I would look for alternatives to using xml files if it was me, such as dumping data to temporary tables/ save as csv files / set up a linked server etc.There is also the issue of generating the xml file in the first place - I've seen java applications crash after 2 hours when building xml files of approx. 20MB. |
|
|
|
|
Huge xml data update to DB