Using repeating nodes from an input xml file to repeat a stored procedure

Anonymous,

here are some options which rely on splitting *before* you get to the SQL insertion:

• in a loop in an orchestration: a small assembly which knows treats the document as an XmlDocument and extracts each customer node. You need to keep a cursor somewhere. I have used this method and it works nicely. You could do this in an expression shape directly – but it’s a pig to test.
• also in a loop: a map which knows how to map the customer node – you pass in the index. I’ve done this as a demo, but it was not pretty.
• in a pipeline: you can make the customer be the contents of an envelope. BizTalk will spit out separate documents for each customer. This may work nicely for you, but it depends a bit on your case. Fiddly to set up, but no coding.

I’m pretty sure you can get your SQL adapter to this for you – but I don’t have any great experience with it. Also, if you need the customer separately for some other reason, then you still have to extract them.

 John D.

You need two things to do:

1. Ensure your stored procedure (CREATE_CUSTOMER) node has an attribute maxOccurs=”unbounded”.

2. In the map use Looping functoid between Customer node in the source xml and CREATE_CUSTOMER.

And it doesn’t even necessary to use an orchestration in this case, but you can do it if you like.

Regards,

Nick