CODEWITHSUNDEEP
xmloracle-database
abhi
abhi

Reputation: 621

What would be a efficient way of Storing an XML to oracle tables parent child

What would be an efficient way of storing a XML to Oracle tables parent child

My tables are order_header and order_item. Against one header there can be many items.

The structure of my XML is 

<ROWSET>
 <ROW>
  <CODE>1000385</CODE>
  <ORDER_ITEMS>
   <ORDER_ITEM>
    <ES_ORDER_HEADER__CODE>1000385</ES_ORDER_HEADER__CODE>
    <QUANTITY_SHIPPED>0</QUANTITY_SHIPPED>
    <ITEM_NUMBER>1</ITEM_NUMBER>
   </ORDER_ITEM>
   <ORDER_ITEM>
    <ES_ORDER_HEADER__CODE>1000385</ES_ORDER_HEADER__CODE>
    <QUANTITY_SHIPPED>0</QUANTITY_SHIPPED>
    <ITEM_NUMBER>2</ITEM_NUMBER>
   </ORDER_ITEM>
  </ORDER_ITEMS>
 </ROW>
</ROWSET>

In Step 1 I have generated this XML. Now the requirement is to save the same xml in the two tables.

Upvotes: 2

Views: 204

Answers (1)

Rob van Wijk
Rob van Wijk

Reputation: 17705

This blogpost of mine describes how to do it in SQL only (read: most efficient way):

http://rwijk.blogspot.com/2010/03/shredding-xml-into-multiple-tables-in.html

Regards,
Rob.

Upvotes: 1

Related Questions