3 Replies Latest reply: Sep 21, 2013 3:09 AM by odie_63 RSS

    Xquery parent attribute

    user4423142

      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

          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

            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

              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)