2 Replies Latest reply: Aug 24, 2012 12:56 PM by damorgan RSS

    XQuery String dynamic

    Ric79
      Hi all,
      let us suppose this query inside the procedure MYEST(XQUERYString IN VARCHAR2)
      SELECT warehouse_name warehouse,
         warehouse2."Water", warehouse2."Rail"
         FROM warehouses,
         XMLTABLE('/Warehouse'
            PASSING warehouses.warehouse_spec
            COLUMNS 
               "Water" varchar2(6) PATH '/Warehouse/WaterAccess',
               "Rail" varchar2(6) PATH '/Warehouse/RailAccess') 
            warehouse2;
      I tried to substitute
       '/Warehouse'
      with XQUERYString with no success. Is it possible to make XMLTABLE a bit dynamic?

      Riccardo
        • 1. Re: XQuery String dynamic
          odie_63
          This works for me, what are you doing differently?
          SQL> declare
            2    doc  xmltype := xmltype('<root>ABC</root>');
            3    xq   varchar2(2000) := '/root';
            4    res  varchar2(30);
            5  begin
            6    select root
            7    into res
            8    from xmltable(xq passing doc columns root varchar2(30) path '.')
            9    ;
           10    dbms_output.put_line(res);
           11  end;
           12  /
           
          ABC
           
          PL/SQL procedure successfully completed
           
          NB : using dynamic XQuery is discouraged as it forces functional evaluation (and thus disables optimization).
          • 2. Re: XQuery String dynamic
            damorgan
            Let's see ... neither one of you posted a version number ... let's start there.
            SELECT * FROM v$version;