How to load data from XML file into database

Hi,

You can do this using either an updategram, or a stored procedure. Updategrams are best when working with inserting multiple records. The basic way to insert data using an update gram is:

Create an xSD schema that represents the structure in the XML file.

Create an XSD schema that matches the SQL database table using the SQL adapter schema wizard.

Create a map that transforms the inbound file to the updategram schema

Deploy the project artifacts to BizTalk server

Create a message channel to receive the inbound file, transform the message, and insert the data using the SQL adapter.

There's a walkthrough for using both updategrams and stored procedures in the following links:

http://msdn2.microsoft.com/en-us/library/aa577899.aspx

http://msdn2.microsoft.com/en-us/library/aa560708.aspx

Regards,

Alan Smith

AlanJamesSmith at 2007-10-2 >

Hi,

Thanks for the above help.

I could load the XML file using File adapter and move to MSSQL database through the SQL Adapter, using simple orcestrations & map.

BUT, only one record from the XML file is loaded into the database table.

How can I load all the records in XML to database table?..

Do i need to use some functiod in Orchestration ? or Do i need to modify the MAP ?

Thanks in Advance

PeterTrell at 2007-10-2 >

One way to insert multiple rows into a table using SQL Adapter is using Updategrams. If this does not work for you then you have to use SQL Adapter with Stored Procedures. You either send one row at a time and this will cause the Stored Proc to be called multiple times or group multiple rows in one XML message and let the stored proc to insert all the rows in that “envelope”. Something like this:

<envelope>

<row … />

<row … />

…

</envelope>

[Please mark the response as "Answer" if it solves your problem.]

NicolaeDaian-MSFT at 2007-10-2 >

Unfortunatelly I don't know about any similar online project, but i still can help you by answering you questions. What solution did you pick up? Updategrams or one XML "envelope" message? What are the problems you are facing?

Nicu

NicolaeDaian-MSFT at 2007-10-2 >

Let me try to understand your problem: you have an orchestration scenario, the orchestration gets the XML message from a FILE receive location (for example), sends the message to SQL, gets a response from SQL (you do not want this response any more) and then sends something to a FILE send port (for example). Is that right?

If this is the case, inside the orchestration you should use a one way send port instead of a two ways send port and of course bind it to a one way send port. This way the message will be sent to SQL and no answer will be sent back.

Regarding the split of message send to the FILE send port in PASS or FAIL, you should use an IF shape inside the orchestration and one send shape on each IF branches. Accordingly you will have two FILE send ports sending messages to two folders.

Please let me know if this is your scenario and if it works. If your scenario is different please describe it as detailed as possible, send over the SP, etc.

[Please mark the response as "Answer" if it solves your problem.]

Nicu

NicolaeDaian-MSFT at 2007-10-2 >