September 30, 2010 at 9:52 am
I am using ssis 2005 in order to migrate data from my source to target database.
I have a source table with one of the column with XML data type.
For example, if the table contains student test information,
it has one row for each student with their ID's. of data type numeric, and marks columns which contains marks scored for every subject in a single row which is of datatype XML.
I need to extract the marks scored by students in each subject and store it in a table against each subject.
so essensitally i would have
id: 100 marks: <maths>99</maths> <science> 98</science> < english>100</english>
How do i read the XML column using ssis and split them into individual columns for each subject score.
October 1, 2010 at 8:47 am
XPath/Xquery in your source query?
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
October 1, 2010 at 9:08 am
that means i will have to write queries to specifically search for those nodes right?
If i have new nodes, they i need to add them in query again. Does ssis has any dynamic way of reading the column of xml data type and then putting them in columns?
Like direct tree to table structure.
October 1, 2010 at 9:23 am
Yes you would need a query for each node, but you'll have to know the nodes in order to map them to the destination columns, right?
I don't know of an SSIS component that converts XML to a file. At least not that I know of. I think you'd need to use a Script Component.
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
October 1, 2010 at 9:30 am
I have the source table, where in the XML doesn follow any standard template. I am working on using the scripting language in order to fetch each node with its value.
At the same time, I am concerned with the performance of the mapping too.
Thanks Jack.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply