September 16, 2008 at 6:05 am
I am using SQL Server 2005 SP2.
I got XML file from client with invalid xsd file. The client is not understanding the problems with invalid xsd file. Therefore we are only left with an option of loading the data without xsd file. 🙁 In order to achieve this task i thought to load the file using OPENXML. thats the only way i found after playing around with xml tools like stylus studio and xml spy which crash everytime i try to play with file.
The problem is that XML data file is nearly 150 MB
The file size is enormously huge. OPENXML eats up memory and its been more than 6 hours now and it is still running. I hope it will finish sometime i reach office tomorrow morning.
Is there any better way to achieve this goal. This is really taking long to get results.
September 16, 2008 at 7:28 am
Have you tried using SSIS?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 16, 2008 at 7:39 am
Hi Jack
I am not good at SSIS specially playing with XML Files. I tried using XML task but was unable to capture the data from xml file as the file contains data about many tables and there are parent child table relationships.
any help on this issue is appreciated
September 16, 2008 at 8:08 am
I would not claim to be good at SSIS either, but I would assume there is a way to do what you need and that it will be faster than OPENXML in SQL. If you could attach a sample of the XML file it would be easier to give you some help. We obviously don't want all 150 MB.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 16, 2008 at 11:32 pm
finally, i got it. yeah... 🙂
i took help of ssis to load this huge file. was amazed to see the power of ssis. just took 3 mins to load 150 mb file into 18 tables.
the steps i followed are,
1. used xml source object and gave the file path. generated auto xsd from ssis.
2. map data to tables.
3. found few discrepencies in xsd file as it was auto generated and was missing some columns in couple of tables. therefore have to manually insert attributes in xsd file.
4. rerun the process
may be you dont have to run step 3 and 4. but be careful about missing data when you load the file and do validate it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply