Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

JDBC usage techniques for multi insert/update operations

Sentah
Level 2
Level 2
Hi,<br /><br />I have a scenario wherein I have the following xml format(simplified version)<br /><br /><student><br /><examHeader><br /> <examNo>EX123</examNo><br /> <examMajor>IndustrialChemistry</examMajor><br /> <studentNo>AS221</studentNo><br /> <status>submitted</status><br /></examHeader><br /><examDetails><br /> <Questionnaire><br /> <answers><br /> <answer><br /> <questionId>12</questionId><br /> <questionVersion>1</questionVersion><br /> <requestId>refnumber</requestId><br /> <response>Y</response><br /> <comment>Default</comment><br /> </answer><br /> </answers><br /></Questionnaire><br /><br /> <br />Now I need to update the header details into 1 table and the details content into multiple table for each question/answer combination.<br /><br />What would be the best way to achieve this ? interms of performance and also reusablilty wise ?<br /><br />I can think of an SQL execute component to perform this , but it would be a series of sql inserts as the tables have foreign key relationships.<br /><br />how do we extract the repeatable data and insert into database in an efficient way ?<br /><br />Any help would be greatly appreciated.
2 Replies
HowardTreisman
Level 9
Level 9
Hi Senthil

You have two options:

1. Create a counter, and a loop in your orchestration. Loop through the rows in your XML, and on each iteration, perform the SQL statement you require. This can be a bit fiddly, but does work.



2. This pattern comes up often enough to warrant us having built a component to automate it. We have a component that does this in a single step - it's called "XML2SQL". You can download it here:

http://www.avoka.com/apps/checkcookie?qpac=y&qpac_code=avokaESComponents&location=%2Fapps%2Fqpacdown...

More info here:

http://avoka.dnsalias.com/confluence/display/Public/XML+to+SQL+DSC



Howard

http://www.avoka.com
Sentah
Level 2
Level 2
Hi Howard,



Thanks for the suggestions. I was planning to write a custom component which would perform the tricky multiple update inserts as the first option wouldnt really fit into the SOA paradigm.



Looks like you already have one.