2 Replies Latest reply: Jul 12, 2013 11:21 AM by paul zip RSS

    Merge parent attribute to child nodes using XMLTable / XQuery.

    paul zip

      I have the following XMLtable query, where I'm trying to transpose the parent attribute to all child rows OR return parent attribute :

       

      select *

      from XMLTable(

      'for $i in /ROOT/ITEM

      return element r {

         $i/../@ALL,

         $i

      }'

      passing XMLType(

          '<ROOT ALL="Y">

             <ITEM>1</ITEM>

             <ITEM>2</ITEM>

           </ROOT>')

      columns

        "ALL" char(1) path '@ALL',

        ITEM  integer path 'ITEM'

      );

       

      | ALL | ITEM |

      --------------

      | Y   | 1    |

      | Y   | 2    |

       

      This works fine, but fails if I pass in the following... 

       

      passing XMLTYPE('<ROOT ALL="Y"/>')


      I want the following :


      | ALL | ITEM |

      --------------

      | Y   |      |

       

      I tried changing the Xpath to /ROOT/ITEM|/ROOT/[@ALL and not(ITEM)] but get syntax errors.  Am I missing something obvious?

        • 1. Re: Merge parent attribute to child nodes using XMLTable / XQuery.
          odie_63

          I tried changing the Xpath to /ROOT/ITEM|/ROOT/[@ALL and not(ITEM)] but get syntax errors.  Am I missing something obvious?

          Which errors?

           

          The more obvious I see is an extra forward slash after the second ROOT step :

          /ROOT/ITEM|/ROOT/[@ALL and not(ITEM)]

           

          Personally, I would either do it like this :


          select *

          from XMLTable(

          '(

             for $i in /ROOT/ITEM

             return element r {

               $i/../@ALL

             , $i

             }

          ) | /ROOT[not(ITEM)]'

          passing XMLType(

              '<ROOT ALL="Y">

                 <ITEM>1</ITEM>

                 <ITEM>2</ITEM>

               </ROOT>')

          columns

            "ALL" char(1) path '@ALL',

            ITEM  integer path 'ITEM'

          );

           

          or using a forward axis and an outer join :


          select x1."ALL", x2.ITEM

          from XMLTable('/ROOT'

                 passing XMLType('<ROOT ALL="Y"/>')

                 columns "ALL" char(1) path '@ALL'

                       , ITEMS xmltype path 'ITEM'

               ) x1

             left outer join

               XMLTable('/ITEM'

                 passing x1.ITEMS

                 columns ITEM integer path '.'

               ) x2

             on 1 = 1

          ;

          • 2. Re: Merge parent attribute to child nodes using XMLTable / XQuery.
            paul zip

            I really like the first solution, I didn't realise the syntax would allow that.

             

            The error I was getting was a stray parenthesis I had left into the code my end but not copied in the example.

             

            As always odie_63, your time and expertise is much appreciated.  Many thanks.