如何在xmltable中传递值

Question

如何在xmltable的xpath中传递变量值？

DECLARE
  v NUMBER := 0;
BEGIN
  SELECT *
  FROM xml_billrun_files t ,
    xmltable('/invoice/AR_ITEMS[@elem='||v||']/ITEMS/USAGE_RECORDS/SESSION_INFO' 
              passing t.update_xmldoc 
              columns chrg_duration VARCHAR2(20) path 'DURATION', 
                      amount NUMBER path 'AMOUNT') x;
END;

我试过这个。

DECLARE
  v NUMBER := 0;
BEGIN
  SELECT *
  FROM xml_billrun_files t ,
    xmltable('/invoice/AR_ITEMS[@elem=$i]/ITEMS/USAGE_RECORDS/SESSION_INFO' 
              passing t.update_xmldoc, xmltype(v) as "i"
              columns chrg_duration VARCHAR2(20) path 'DURATION', 
                      amount NUMBER path 'AMOUNT') x;
END;

但是它返回一个错误：

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of '0'
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 1
ORA-06512: at line 5
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.

谢谢你的帮助。

Answer 1

在my answer中，对于前面的问题，我在XMLQuery调用中错误地使用了xmltype(lp) as "lp"。不知道为什么没有抱怨，但实际上并没有限制比赛.

对于这个XMLTable调用，您可以直接传递数字，而无需转换/强制转换，因为它已经是一个数字：

  SELECT chrg_duration, amount
  into ...
  FROM xml_billrun_files t ,
    xmltable('/invoice/AR_ITEMS[@elem=$i]/ITEMS/USAGE_RECORDS/SESSION_INFO' 
              passing t.update_xmldoc, v as "i"
              columns chrg_duration VARCHAR2(20) path 'DURATION', 
                      amount NUMBER path 'AMOUNT') x;

如果在for循环中执行此操作，则索引是错误的数据类型，需要将其转换为数字：

  SELECT chrg_duration, amount
  into ...
  FROM xml_billrun_files t ,
    xmltable('/invoice/AR_ITEMS[@elem=$i]/ITEMS/USAGE_RECORDS/SESSION_INFO' 
              passing t.update_xmldoc, cast(v as number) as "i"
              columns chrg_duration VARCHAR2(20) path 'DURATION', 
                      amount NUMBER path 'AMOUNT') x;