This discussion is archived
3 Replies Latest reply: Sep 21, 2013 1:09 AM by odie_63 RSS

Xquery parent attribute

user4423142 Newbie
Currently Being Moderated

Hi Everybody,

 

I use Oracle  Release 11.2.0.3.0

 

I have an XML document with 2 elements : section and sub_section. both have an attribute name. Section is the parent and can have multiple sub_section. For each sub-section I want to read the value of the attribute name and also the value of the attribute name of the parent. Here my request  :

 

select sub_section.section_name,sub_section.sub_section_name from formulations_p,

XMLTable('for $i in /formulation/section/sub_section return $i' passing sections columns

section_name varchar2(100) PATH '/parent::section/@name',

sub_section_name varchar2(100) PATH './@name') sub_section

where formulation_id=2;

 

I think : '/parent::section/@name' is not correct, in my case the data are empty.

Normally the request is more complicated, but I want to simplify. I think it is possible to use XMLTABLE, but I would like to know how to specify the notation to read the attribute name of the parent with this simple request.

 

Thanks in advance

  • 1. Re: Xquery parent attribute
    odie_63 Guru
    Currently Being Moderated

    Hi,

     

    The approach you tried is supported in the new 12c release.

     

    On earlier versions, you can use one of these two methods :

     

    1. Chained XMLTABLEs :

    select sec.section_name

         , sub.sub_section_name

    from formulations_p

       , XMLTable(

           'for $i in /formulation/section return $i'

           passing sections

           columns section_name  varchar2(100) PATH '@name'

                 , sub_sections  xmltype       PATH 'sub_section'

         ) sec

       , XMLTable(

           '/sub_section'

           passing sec.sub_sections

           columns sub_section_name varchar2(100) PATH '@name'

         ) sub

    where formulation_id = 2 ;

     

    2. Single XMLTABLE and a little more complex XQuery :

    select sub_section.section_name

         , sub_section.sub_section_name

    from formulations_p

       , XMLTable(

           'for $i in /formulation/section

              , $j in $i/sub_section

            return element r {

              element section_name { data($i/@name) }

              element sub_section_name { data($j/@name) }

            }'

           passing sections

           columns section_name     varchar2(100) PATH 'section_name'

                 , sub_section_name varchar2(100) PATH 'sub_section_name'

         ) sub_section

    where formulation_id = 2 ;

  • 2. Re: Xquery parent attribute
    user4423142 Newbie
    Currently Being Moderated

    Thank you very much, I'll check this.

     

    For you what is (what are) the best book (books) about XQUERY (XQUERY in general).

     

    Thanks again

  • 3. Re: Xquery parent attribute
    odie_63 Guru
    Currently Being Moderated

    Well, not really a book per se but I've always found this tutorial interesting : XQuery Tutorials

     

    Check out the official W3C XQuery page too, it compiles a lot of references : W3C XML Query (XQuery)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points