Best XML Structure For Import Into SQL 2005

Hi,

I am writing a contingency plan for incase our SQL Server (2005) goes down. Basically when our applicatino is about to write data to the SQL Server it checks the connection. If the connection passes it outputs to SQL, if it fails it generates an XML document detailing the data so that the data can be imported later.

Each XML document needs to define which database and which table needs to be updated/inserted.

So the SQL needs to be able to read a number of XML documents and update the relevant tables according dependent on the data held within the XML; some examples:

<Output>
<Record>
<Database>DW</Database>
<Table>dbo.tblActivity</Table>
<DateTime>11-05-2007 10:03:33.099</DateTime>
<Data>
<strActivityPK>9353|HCR008</strActivityPK>
<strActivityCode>HCR008</strActivityCode>
<strActivityDescription>HCR Complete</strActivityDescription>
<datTarget>2007-05-18</datTarget>
<datActual />
<intMatterIntCode>9353</intMatterIntCode>
<intRecurringActivityFlag>0</intRecurringActivityFlag>
<intETLFlag>0</intETLFlag>
</Data>
</Record>
</Output>

I need SSIS to open this XML, read the Table element and insert or update dbo.tblActivity (in this case) with the data defined in the Data element. If updating, the update should only take place if the DateTime is more recent than a field (datLastUpdate) on the corresponding row in the database.

Can anyone offer some guidance (for example is the XML optimally structured for import by SQL) and advice on how (which tasks to use) to go about setting this SSIS package up.

Ps. thanks to Darren I already have the file selection and open process sorted using a Foreach and some enumeration to select each XML file. It is what to do with the XML afterwards I need get working...


Thanks,
Drammy