Importing into an SQL db via XML on regular basis

[prodigymad]

Hello all,

I am working for an IT firm that uses an SQL database as the backend to a VB application. We setup financial imports into the database via the VB program but we are now getting more advanced requirements.

We have started using stored procedures to do INSERT & UPDATE statements but the new one is to import XML files from other financial organisations. We have the XSL schemas, etc and currently we parse the file into a CSV file and then import this.

I have read about the OPENXML and the sp_xml_preparedocument script. I can't get my head round how to use this for my requirements. We need the schema to be stored somewhere and then everyday a file will be imported as XML. I hope this makes sense!

 

[Sheco]

Are you using DTS ?

 

[prodigymad]

No, basically we define the fields from a drop down list in the VB app and the VB basically translates all this into INSERT statements.

I am not too bothered about doing it this way as we can call stored procedures from this VB so as long as I can get all the INSERT & UPDATES in there then it should be ok.

According to the documents on MSDN it "parses the text using the MSXML parser". This indicates that the text is stored somewhere. What we have is an XML file that contains the info and we need the OPENXML to look in the file for the XML data.