Oracle XML parsing

Question

Is there any way to extract all the child elements of xml without hard coding the path?

Example:

<forms>
<category1>
<Type1>80</Type1>
<Type2>90</Type2>
<Type3>100</Type3>
</category1>
<category2>
<Type4>80</Type4>
<Type5>90</Type5>
<Type6>100</Type6>
</category2>
</forms>

I need to get the all the child elements values by not hard coding the path.

I tried parsing it in oracle. but not able to do it without hard coding the nodes.This xml is stored in a column in a table.

 xmltable(
           '*'
           passing xmltype(replace(fm_Data,'xmlns=''generic''',''))
           columns parent_id   for ordinality
                 , child_list  xmltype path '*'
         ) x1
       , xmltable(
          '*'
          passing x1.child_list
          columns child_name varchar2(30) path 'name()',
          child_value1 varchar2(30) path 'text()',
          child_list2 xmltype path '*'
        ) x2

This gives me the root element and one child. but since i am not sure how many childs will be there. need a generic solution for this.

Regards Sreekanth

Have you tried anything? Do you even know how to parse XML? Show us sample code. Also how does this relate to Oracle? — David Brossard, Dec 29 '15 at 02:33
@Thilo is absolutely right, if you start with , end with . Same with your categories... — David Brossard, Dec 29 '15 at 05:58
Possible duplicate http://stackoverflow.com/a/15899456/99691 — gordy, Dec 29 '15 at 06:23

score 0 · Answer 1 · answered Dec 29 '15 at 10:03

select * from xmltable('//*[not(*)]' passing xmltype('<forms>
                        <category1>
                        <Type1>80</Type1>
                        <Type2>90</Type2>
                        <Type3>100</Type3>
                        </category1>
                        <category2>
                        <Type4>80</Type4>  
                        <Type5>90</Type5>
                        <Type6>100</Type6>
                        </category2>
                        </forms>')  
    columns child_name varchar2(30) path 'name()' ,
            child_value varchar2(30) path 'text()');

This code returns name and value of tag only if tag is 'leaf'.

How it's work.

//* -- xquery query returns each node on each level.

//*[not(*)] - code in square bracket called is predicate. Predicates are used to filter the result to contain only nodes meets specific criteria. In this example it returns only nodes without child.

If you want to know node's path you have to use xquery flwor expression.

select * from xmltable(' for $i in $doc//*[not(*)] 
                   return <result>
                             <name>{$i/name(.)}</name>
                             <value>{$i/text()}</value>
                             <path>{string-join($i/ancestor-or-self::*/name(.),''/'')}</path>
                          </result>'
                 passing  xmltype('<forms>
                                    <category1>
                                        <Type1>80</Type1>
                                        <Type2>90</Type2>
                                        <Type3>100</Type3>
                                    </category1>
                                    <category2>
                                        <Type4>80</Type4>
                                        <Type5>90</Type5>
                                        <Type6>100</Type6>
                                    </category2>
                        </forms>') as "doc"
                               columns name_    varchar2(30) path 'name'
                                       ,value_  varchar2(30) path 'value'
                                       ,path_   varchar2(100) path 'path' 
                        )

Oracle XML parsing

1 Answers1