16346

Oracle XMLQuery passing parameter value

Question:

Need help in my XMLQuery.

getting the loop seq

SELECT extractvalue(update_xmldoc, 'count(/invoice/AR_ITEMS/ITEMS[NAME="Voice" and ITEM_TOTAL!=0])') INTO item_check FROM xml_billrun_files WHERE seq_id = seq AND docname = REPLACE(fn,'.','_HULK.') ORDER BY TIMESTAMP DESC;

then item_check value will pass to the loop

FOR lp IN 1..item_check LOOP SELECT tst, bill_info_id, bill_no, dur_net_amt, dur_vat_amt, dur_pro_net_amt, dur_pro_vat_amt, sys_descr INTO xmlU, bp, bill, net_amt1, vat_amt1, net_amt2, vat_amt2, disc_name FROM (SELECT row_number() over (partition BY t.docname order by t.timestamp DESC) rn, t.docname, XMLQuery('for $i in distinct-values(/invoice/AR_ITEMS['||lp||']/ITEMS/EVENTS/BAL_IMPACTS/DISCOUNT_INFO) where $i = "Plan499 Corp Disc" or $i = "Plan899 Corp Disc" or $i = "Plan1099 Corp Disc" or $i = "Plan1599 Corp Disc" return $i' passing original_xmldoc returning content ).getStringVal() sys_descr

i just post a part of my script.

the script is good but when i pass the value of lp inside the XMLQuery i get the error

<blockquote>

Error(528,66): PL/SQL: ORA-19109: RETURNING keyword expected

</blockquote>

Okay, so below is an example of my script.

DECLARE item_check NUMBER; fn VARCHAR2(100) := 'test.xml'; seq NUMBER := 1; BEGIN SELECT extractvalue(update_xmldoc, 'count(/invoice/AR_ITEMS/ITEMS[NAME="Voice" and ITEM_TOTAL!=0])') INTO item_check FROM xml_billrun_files WHERE seq_id = seq AND docname = REPLACE(fn,'.','_HULK.') ORDER BY TIMESTAMP DESC; IF item_check <> 0 THEN FOR lp IN 1..item_check LOOP SELECT tst INTO xmlU FROM (SELECT row_number() over (partition BY t.docname order by t.timestamp DESC) rn, t.docname, XMLQuery('for $i in distinct-values(/invoice/AR_ITEMS['||lp||']/ITEMS/EVENTS/BAL_IMPACTS/DISCOUNT_INFO) where $i = "Plan499 Corp Disc" or $i = "Plan899 Corp Disc" or $i = "Plan1099 Corp Disc" or $i = "Plan1599 Corp Disc" return $i' passing original_xmldoc returning content ).getStringVal() sys_descr, SUM(SUBSTR(x.chrg_duration,1,instr(x.chrg_duration,':',1)-1)) over (partition BY t.docname) chrg_duration, CASE WHEN extractvalue(xmltype('<DISCOUNT_INFO>'||XMLQuery('for $i in distinct-values(/invoice/AR_ITEMS/ITEMS/EVENTS/BAL_IMPACTS/DISCOUNT_INFO) where $i = "Plan499 Corp Disc" or $i = "Plan899 Corp Disc" or $i = "Plan1099 Corp Disc" or $i = "Plan1599 Corp Disc" return $i' passing original_xmldoc returning content )||'</DISCOUNT_INFO>'),'DISCOUNT_INFO') = 'Plan499 Corp Disc' AND SUM(SUBSTR(x.chrg_duration,1,instr(x.chrg_duration,':',1)-1)) over (partition BY t.docname) <= prorate_mins THEN insertchildxml( insertchildxml( updatexml( updatexml( updatexml(t.update_xmldoc,'/invoice/BILLINFO/TOTAL_DUE/text()', extractvalue(t.update_xmldoc,'/invoice/BILLINFO/TOTAL_DUE') - SUM(x.amount) over (partition BY t.docname)),'/invoice/BILLINFO/CURRENT_TOTAL/text()', extractvalue(t.update_xmldoc,'/invoice/BILLINFO/CURRENT_TOTAL') - (SUM(x.amount) over (partition BY t.docname))),'/invoice/BILLINFO/DISCOUNT_VALUE', xmltype('<DISCOUNT_VALUE>'||(NVL(extractvalue(t.update_xmldoc,'/invoice/BILLINFO/DISCOUNT_VALUE'),0) - ROUND((SUM(x.amount) over (partition BY t.docname))/1.12,2))||'</DISCOUNT_VALUE>')), '/invoice/AR_ITEMS/ITEMS[NAME="Voice"]/USAGE_RECORDS[STREAM_NAME="NDD"]', 'DURATION_NET_DISCOUNT', xmlelement("DURATION_NET_DISCOUNT", ROUND((SUM(x.amount) over (partition BY t.docname)*-1)/1.12,2))),'/invoice/AR_ITEMS/ITEMS[NAME="Voice"]/USAGE_RECORDS[STREAM_NAME="NDD"]','DURATION_VAT_DISCOUNT', xmlelement("DURATION_VAT_DISCOUNT",ROUND(((SUM(x.amount) over (partition BY t.docname)*-1)/1.12)*.12,2))) END tst FROM xml_billrun_files t , xmltable('/invoice/AR_ITEMS/ITEMS/USAGE_RECORDS/SESSION_INFO' passing t.update_xmldoc columns chrg_duration VARCHAR2(20) path 'DURATION', amount NUMBER path 'AMOUNT') x WHERE seq_id = seq AND t.docname = REPLACE(fn,'.','_HULK.') ) WHERE rn = 1; UPDATE xml_billrun_files a SET update_xmldoc = xmlU WHERE seq_id = seq AND docname = REPLACE(fn,'.','_HULK.'); COMMIT; END LOOP; END IF; END;

Answer1:

The problem is the string concatenation in the XPath. It doesn't like you doing ... AR_ITEMS['||lp||']/ITEMS ....

You can pass the value of your PL/SQL lp variable in through the passing clause, which allows multiple comma-separated arguments; give it an identifier and then refer to that directly in the XPath. I've stuck used "lp" and $lp; they don't have to match the PL/SQL variable name, but it might be clearer if they do.

XMLQuery('for $i in distinct-values(/invoice/AR_ITEMS[$lp]/ITEMS/EVENTS/BAL_IMPACTS/DISCOUNT_INFO) where $i = "Plan499 Corp Disc" or $i = "Plan899 Corp Disc" or $i = "Plan1099 Corp Disc" or $i = "Plan1599 Corp Disc" return $i' passing original_xmldoc, cast(lp as number) as "lp" returning content ).getStringVal() sys_descr,

The PL/SQL loop index variable lp is a pls_integer which the passing clause doesn't like; if you pass that directly you get ORA-00932, so you need to cast it to the number data type.

Recommend

  • Melt and Merge on Substring - Python & Pandas
  • Connect to SOAP using JAX WS
  • NodeJS Sendrgrid Issue in sending email to multiple recepients
  • How to check array of dates are consecutive from todays date? [duplicate]
  • Create a sticky footer in a responsive manner
  • Comparative genomics: how to compare ranges of sequences
  • Extract IP address from HTML document
  • wrap content in JavaFX
  • How can I add a class with jquery once dynamic loading is finished?
  • How select second line from top 2 or something similar?
  • Threading lock in python not working as desired
  • Row_number() function for Informix
  • Is there a way to pivot a customer ID and a their most recent order dates?
  • Taking mean across rows grouped by a variable in numpy
  • Grouping by blank nodes
  • Does sql server minds the way records where inserted?
  • Query to get the Top 2 from each group
  • How to implement limit with Nhibernate and Sybase
  • Oracle ListaGG, Top 3 most frequent values, given in one column, grouped by ID
  • Azure table query partial partitionkey guid match
  • If statement skipping right to else after being called once?
  • Merging rows to columns
  • Can someone please explain to me in the most layman terms how to use EventArgs?
  • Eloquent update method change created_at timestamp
  • how to display data from 1st point on words on y axis for line chart in d3.js
  • Row Count Is Returning the incorrect number using RaptureXML
  • Breeze - Deleted Items nav properties bug
  • Illegal mix of collations for operation for date/time comparison
  • javaw.exe and eclipse startup problems
  • WinForms: two way TextBox problem
  • Convert array of 8 bytes to signed long in C++
  • Hazelcast - OperationTimeoutException
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • Understanding cpu registers
  • need help with bizarre java.net.HttpURLConnection behavior
  • apache spark aggregate function using min value
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices
  • How can I use threading to 'tick' a timer to be accessed by other threads?