Transform xml into rational table slow

Toad for Oracle & DevOps Toolkit
1

my xml file looks like this

has about 3000 “recors” (tag )

and is really simple

<?xml version="1.0" encoding="ISO-8859-1"?>

<adapt_xml>

34093

…
34
<liste_photos>
adf_photo_2.jpg
adf_photo_7.jpg
adf_photo_4.jpg
…
</liste_photos>
…

</row

34093

…
34
<liste_photos>
adf_photo_2.jpg
adf_photo_7.jpg
adf_photo_4.jpg
…
</liste_photos>
…

34093 ... 34 adf_photo_2.jpg adf_photo_7.jpg adf_photo_4.jpg .... ... 34093 ... 34 adf_photo_2.jpg adf_photo_7.jpg adf_photo_4.jpg .... ... 34093 ... 34 adf_photo_2.jpg adf_photo_7.jpg adf_photo_4.jpg .... ... ...

the xml is stored in a xml table in Oracle (Version 11.2…)

i have to extract the values an store them in a relational table

for that I do the following statements

this is fast

SELECT
x1.field1,
x1.field2,
x1.field3,
…
x1.field50
FROM tmp_xml,
XMLTable(
‘//row’
passing OBJECT_VALUE
columns
field1 path ‘field1’,
field2 path ‘field2’,
field3 path ‘field3’,
…
field50 path ‘field50’
) x1

this is really slow

SELECT
x1.field1,
x1.field2,
x1.field3,
…
x1.field50,
x1.photo_1,
x1.photo_2,
x1.photo_3,
x1.photo_4,
x1.photo_5,
x1.photo_6,
x1.photo_7,
x1.photo_8,
x1.photo_9,
x1.photo_10,
x1.photo_11,
x1.photo_12,
x1.photo_13
FROM tmp_xml,
XMLTable(
‘//row’
passing OBJECT_VALUE
columns
field1 path ‘field1’,
field2 path ‘field2’,
field3 path ‘field3’,
…
field50 path ‘field50’,
photo_1 path ‘liste_photos/photo[1]’,
photo_2 path ‘liste_photos/photo[2]’,
photo_3 path ‘liste_photos/photo[3]’,
photo_4 path ‘liste_photos/photo[4]’,
photo_5 path ‘liste_photos/photo[5]’,
photo_6 path ‘liste_photos/photo[6]’,
photo_7 path ‘liste_photos/photo[7]’,
photo_8 path ‘liste_photos/photo[8]’,
photo_9 path ‘liste_photos/photo[9]’,
photo_10 path ‘liste_photos/photo[10]’,
photo_11 path ‘liste_photos/photo[11]’,
photo_12 path ‘liste_photos/photo[13]’,
photo_13 path ‘liste_photos/photo[13]’
) x1

this is slow as well

SELECT
x1.field1,
x1.field2,
x1.field3,
…
x1.field50,
x1.liste_photos,
FROM tmp_xml,
XMLTable(
‘//row’
passing OBJECT_VALUE
columns
field1 path ‘field1’,
field2 path ‘field2’,
field3 path ‘field3’,
…
field50 path ‘field50’,
liste_photos xmltype path ‘liste_photos’
) x1

I tried to crate some xmltype index
but no success

the select of the field1 … field50 is fast

but if I select the photo list the performance is going down

thanks

Martin

2

Hi Martin,

Could you please create the table " XML_FORM_DATA "?

And upload the XML file on the column name XML_DATA.

Column_Name Data_type

ID INTEGER

DATE_CREATED DATE

XML_DATA XMLTYPE

After creation of the table, upload the XML_file to XML_DATA table.

For Example, Upload as :-

566204191 22/03/2012 <?xml version="1.0" encoding="UTF-8"?>…

Then fetch the xml’s records as per your requirement with the below query.

This is the extraction package(dot extract)

SELECT a.XML_DATA.extract(’/adapt_xml/row/field1/text()’).getstringval() ,

a.XML_DATA.extract(’/adapt_xml/liste_photos/photo/text()’).getstringval()

FROM XML_FORM_DATA a;

I hope this will help you !!

Regards

Dharmendra Verma