BizTalk Server Orchestration - Looping and SQL Adaptor

Author

Looping and SQL Adaptor

Owen J.

2006-01-19, 6:07 pm

I need to insert multiple records into a SQL database. The schema for the
input file is similar to the following...

<Customers>
<Cust>
<Customer Name></Customer Name>
<Customer Address></Customer Address>
<Etc...></Etc...>
</Cust>
<Cust>
<Customer Name></Customer Name>
<Customer Address></Customer Address>
<Etc...></Etc...>
</Cust>
</Customers>

I need to insert each Cust record into a SQL database using the SQL adaptor.
I've tried creating a map and orchestration without using the SQL adaptor
just to test. So far I'm only getting the first record in the output file.
What is the best method of accomplishing what I need to do? I attempted to
use a loop functoid in the map and expected to see multiple records in the
output file, but this doesn't work. The loop functoid was applied to the
<Cust> field. Do I need to create a loop in the orchestration instead? Any
help would be greatly appreciated.

Neal Walters

2006-01-24, 5:59 pm

One solution is to create a stored proc that uses OpenXML. Pass the entire
XML block to the stored proc as a single XML string paramter. Then the
stored proc and use the sp_preparedocument (or some such similar name) and
then with OpenXML use Xpath statements

Here's a partial example from SQL online books (except you would pass the
xml as a parm) and change the Select to an Insert or Update.

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
EXEC sp_xml_removedocument @idoc

Neal Walters
http://Biztalk-Training.com