I want to use SAX parser in SQL Server to load a table from a complex xml file. Inputs anyone?
I want to get some references about how to accomplish:
To load a SQL Server table by parsing a complex XML file(the source) in SSIS.
Thanks,
Sayan
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$2 Answers
Sorry if that's not more helpful, but seemed like it had to be said. Good luck!
Tried researching "sax parsers in sql server" but while there appear to be many articles, they always seem to go to premium magazine subscription sources and thus don't make good links.
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$It would really depend on the structure of the XML. If the columns were added to a parent node, which is common, parsing code would not need to cache much information. But, since you treat the XML as a stream when using a SAX parser, you would need to cache each stream until you have enough data for an insert. This would mean multiple small inserts into SQL server which would not be efficient as a bulk insert especially if you have a seperate machine or cluster for the DB and if the inserts are transaction based.
If you have large files, there would be a point where using SAX would outweigh the overhead of small SQL inserts, compared to the memory usage of DOM.
Here is some information on both DOM and SAX in SQL server.
http://www.informit.com/articles/article.aspx?p=102307&seqNum=1
Apparently there isn't that much information to find, and since I am not familiar with SQL server, take my advice as generic based on Database and XML experience.
Web Developer for http://www.fijiwebdesign.com/
DOM and SAX in SQL server - http://www.informit.com/articles/article.aspx?p=102307&seqNum=1
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$
Thanks, yes I guess in my case DOM would be a better option because the XML is really complex.
I don't know, B -- in order to determine the number of columns, you pretty much have to stream through the entire file to find the one at the end (by definition). And if your table creation logic is such that you can't really do anything until you have that number (which is where my lack of SQL Server knowledge shows), you would essentially have to cache everything. And if you do that, you're basically doing the job the DOM would have done in the first place.
I look at SAX for cases where I can reasonably say, "I will know what to do with this information when I get it." For each time I have to say, "I'm not sure what to do with it, I'll have to save it over here until I get more information..." I lean more towards DOM.
I am a newbie in XML, so know almost nothing, but hey, I get your point....yes now I reckon the question was shaky right from the start. Thanks for showing the way!