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 ?
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
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
