How to use XPath with a variable in Oracle XMLTable?

Question

How to use XPath with a variable like this '/abc/[contract = $count]/initialValue' in Oracle XMLTable() ?

For eg:

XMLTable('root/level1', PASSING xmlContent 
COLUMNS initial_value varchar2(100) PATH '/abc/[contract = $count]/initialValue' 
)

How to substitute/evaluate $count runtime ?

where are you setting $count? is it a variable in the containing pl/sql block? need a bit more context for it. your xpath also looks wrong so please so us the structure of it (is contract + initialValue at the same level?) — DazzaL, Dec 19 '12 at 08:12
Assume this is a dynamic XQuery and Xpath validnes doesn't matter here. ie. Dynamic XQuey supports substituting variables at runtime. For eg: let's say following is a substituted/generated XQuery "/abc/[contract = 123]/initialValue" from "/abc/[contract = $count]/initialValue". I was looking to use dynamic XQuery with XMLTable(). Unfortunately Oracle documentation says it doesn't allow XML_passing_clause for COLUMNS. — sojin, Dec 19 '12 at 08:32

score 7 · Accepted Answer · answered Dec 19 '12 at 09:01

You can pass variables in, just define them in your passing clause:

with table1 AS
  (select xmltype(
  '<abc>
     <def>
        <contract>1</contract>
        <oper>SFO</oper>
        <lmt>limit1</lmt>
     </def>
    <def>
       <contract>2</contract>
       <oper>boston</oper>
       <lmt >limit2</lmt>
    </def>
 </abc>'
 ) xmlcol from dual
 )
 SELECT u.*
   FROM table1
   ,    XMLTable('/abc/def[contract = $count]'
                 PASSING xmlcol, 1 as "count"
                 COLUMNS contract integer path 'contract',
                         oper     VARCHAR2(20) PATH 'oper' ) u 

  CONTRACT OPER               
---------- --------------------
         1 SFO

Aah, thanks. Missed the point I can pass XML clause in first argument. — sojin, Dec 19 '12 at 11:19

DazzaL · Answer 2 · 2012-12-19T08:31:05.250

if the "count" variable is being defined in an outer pl/sql block, then use table() instead as xmltable paths have to be a string literal and won't allow splicing variables in.

i've assumed your xml structure is

<root>
  <level1>
     <abc>
        <contract>3</contract>
        <initialValue>4</initialValue>
      </abc> 
  </level1>
</root>


SQL> declare
  2    v_count  number := 3;
  3  begin
  4    for r_row in (select extractvalue(value(my_table), '/level1/abc[contract = "' || v_count || '"]/initialValue') xml
  5                    from table(
  6                           xmlsequence(
  7                             extract(XMLType('<root>
  8                      <level1>
  9                         <abc>
 10                            <contract>3</contract>
 11                                          <initialValue>4</initialValue>
 12                          </abc>
 13                      </level1>
 14                      <level1>
 15                         <abc>
 16                            <contract>3</contract>
 17                            <initialValue>7</initialValue>
 18                          </abc>
 19                      </level1>
 20                      <level1>
 21                         <abc>
 22                            <contract>4</contract>
 23                            <initialValue>24</initialValue>
 24                          </abc>
 25                      </level1>
 26                    </root>'),
 27                       '/root/level1'))) my_table)
 28    loop
 29      dbms_output.put_line(r_row.xml);
 30    end loop;
 31  end;
 32  /
4
7

the way to do it with xmltable, is not to put it into the XPAth but filter in the where clause afterwards:

SQL> declare
  2    v_count  number := 3;
  3  begin
  4    for r_row in (select *
  5                    from XMLTable( 'root/level1'  passing xmltype('<root>
  6                      <level1>
  7                         <abc>
  8                            <contract>3</contract>
  9                                          <initialValue>4</initialValue>
 10                          </abc>
 11                      </level1>
 12                      <level1>
 13                         <abc>
 14                            <contract>3</contract>
 15                            <initialValue>7</initialValue>
 16                          </abc>
 17                      </level1>
 18                      <level1>
 19                         <abc>
 20                            <contract>4</contract>
 21                            <initialValue>24</initialValue>
 22                          </abc>
 23                      </level1>
 24                    </root>')
 25                    columns initial_value varchar2(100) PATH 'abc/initialValue',
 26                            contract      varchar2(100) PATH 'abc/contract')
 27                   where contract = v_count)
 28    loop
 29      dbms_output.put_line(r_row.initial_value);
 30    end loop;
 31  end;
 32  /
4
7

How to use XPath with a variable in Oracle XMLTable?

2 Answers2

Linked