Expand my Community achievements bar.

Radically easy to access on brand approved content for distribution and omnichannel performant delivery. AEM Assets Content Hub and Dynamic Media with OpenAPI capabilities is now GA.

JDBC usage techniques for multi insert/update operations

Avatar

Former Community Member
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

Avatar

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

Avatar

Former Community Member
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.