XML

Question

XML

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Personally I'd probably use script component and handle it in .NET. .NET handles XML pretty well.

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

brendan woulfe Hall of Fame Points: 3888 More actions · Answer 2

GSquared (6/12/2012)
Generally, I've been lazy and pulled the raw XML into a staging table with an XML datatype column, then used XQuery to shred the XML into relational format. I've neglected to educate myself properly on the XML tools in SSIS, while I've spent a lot of days teaching myself XQuery, so it's easier that way for me.
The one time I used SSIS directly to put XML into a table, it was flat XML into a flat table, and BIDS wizards figured it out on their own (essentially), when building the SSIS package. BIDS also built the XSD for me.
But with more complex XML, I just stage it and then shred it through T-SQL XQuery.

That's exactly what I do. Not only is it pretty straight-forward to setup, it's easy to understand. You can pass it off to someone else who should be able to figure out the process fairly well.

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 3

brendan woulfe (6/15/2012)
GSquared (6/12/2012)
Generally, I've been lazy and pulled the raw XML into a staging table with an XML datatype column, then used XQuery to shred the XML into relational format. I've neglected to educate myself properly on the XML tools in SSIS, while I've spent a lot of days teaching myself XQuery, so it's easier that way for me.
The one time I used SSIS directly to put XML into a table, it was flat XML into a flat table, and BIDS wizards figured it out on their own (essentially), when building the SSIS package. BIDS also built the XSD for me.
But with more complex XML, I just stage it and then shred it through T-SQL XQuery.
That's exactly what I do. Not only is it pretty straight-forward to setup, it's easy to understand. You can pass it off to someone else who should be able to figure out the process fairly well.

That's assuming there's someone to pass it off to. There isn't. I just need to figure out why the XML Source tool isn't behaving properly. In the meantime, I've got it all working fairly well in T-SQL by bulk loading it into a table with an XML column and shredding it from there.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams