Parse XML in oracle when same dom structure is repeated multiple time's

Question

I know how to parse an xml in oracle which goes something like this

select extractvalue(column_value, 'CIRS/CIR/@applicantId') applicantId
into applicantId
 from CIBIL_BINARY_INPUT ,table(xmlsequence(xmltype(ded_enq_xml)))

Now i have an xml as shown below

<library>
    <book>
    <name>Harry potter</name>
    <author>Harry potter</author>
    </book>
    <book>
    <name> watson </name>
    <author>Harry watson </author>
    </book>
    <book>
    <name> john </name>
    <author> potter</author>
    </book>
    </library>

As you can see the book node is repeated multiple times

How can i extract it so that i can insert it as 3 different 'book' rows in my library table.

Please suggest.

score 0 · Answer 1 · answered May 13 '14 at 13:45

Seems like your first problem is to extract multiple books from xml. This can be solved like this:

select
    extractvalue(column_value, '/book/name') name,
    extractvalue(column_value, '/book/author') author
from
  table(xmlsequence(extract(xmltype('<library><book>...</book><book>...</book>...</library>'), '/library/book')));

Hope this helps.

Parse XML in oracle when same dom structure is repeated multiple time's

1 Answers1