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

Adobe Summit 2023 [19th to 23rd March, Las Vegas and Virtual] | Complete AEM Session & Lab list

JDBC usage techniques for multi insert/update operations


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


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:

More info here:



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.

The ultimate experience is back.

Join us in Vegas to build skills, learn from the world's top brands, and be inspired.

Register Now