SSIS(sql server integration service) xml data flow

Question

Hi,

I have an xml file the content which i have to write to a Database table using ssis pacakge dataflows.

I am using xml source and oledb destination.

My issue now is this xml file generate multiple outputs .(event,produt,offer,form) etc. But i need to write all in one data row(more than one if 2 products are there for the event) in the database.

But i do not know how to use this multiple outputs and make a single row for a event.

I hav read numerous articles about this subject but not able to take a decision.what is the right way of doing this.

1) xml source ? (if i use this how do i merge the multiple outputs)

2) or a script task using xml objects read and write to the DB.

or anything new ?

Please provide me some solutions .Its urgent.

xml sample file

<root> <event id="1000"><sponsor id="126" type="1">ABc.</sponsor><start_date>2009-06-07</start_date> <Some_date>2010-04-30</some_date><valid_region id="8">region</valid_region> <limit>test</limit> <contact_info>1 contact</contact_info> <offer id="10"> <offer_text>offertest</offer_text> <product id="11"><name>product1</name> </product><product id="12"><name>product1</name> </product></offer><form id="34">  <form_size>187</claim_form_size> </form></event</root>

Thanks

SNA

Answer 1

An entirely different approach is to write your XML string into a table in a XML data type column. You could then use the xml_preparedocument and OPENXML to query the XML in your database. I use this regularly to download XML files and extract only the nodes I want for my current reports. This gives me the flexibility of extracting more nodes when I need them in the future without having to do it right now. It also makes it easy to break up your ETL so that you load different destinations with the appropriate data. For instance, if part of the XML is basically a header and the rest of the XML is basically detail transactions, then you could write a stored procedure that extracts the header to one table and the details to another table. Let me know if you want further details on how to do this and I can provide you with sample code.