This content has been marked as final. Show 6 replies
Please provide a test case.
Hello, I don't know how do I use a XMLTable to delete and update multiple records but I've created a procedure as follows:
CREATE OR REPLACE PROCEDURE PROD.DeleteFDataBlob(V_FINDTL BLOB) AS
DELETE FROM TBLFINANCIALDATA
WHERE FK_COMPANYREGID = FK_COMPANYREGID
passing SYS.XMLTYPE(V_FINDTL, nls_charset_id('AL32UTF8'))
columns FK_COMPANYREGID varchar2(30) path 'FK_COMPANYREGID'
But I'm having compilation errors:
[Error] Syntax check (7: 1): Found: 'SELECT' Expecting: ; -or- LOG -or- RETURN RETURNING -or- OR -or- AND -or- + - || -or- * / MOD REMAINDER -or- ** -or- (+) AT DAY MULTISET YEAR -or- % . [ -or- @ -or- ( string
Could you, please, help me.
I don't know how do I use a XMLTable to delete and update multiple recordsXMLTable doesn't delete or update anything, it's used to project XML data as relational data.
From what I understand of your requirement, I guess you have to extract a list of IDs from V_FINDTL and use that list to filter a DELETE?
Maybe something like this then?
DELETE FROM tblfinancialdata WHERE fk_companyregid IN ( SELECT fk_companyregid FROM XMLTable( '/FINANCIALDATA/FINANCIALDATADETAIL' passing xmltype(v_findtl, nls_charset_id('AL32UTF8')) columns fk_companyregid varchar2(30) path 'FK_COMPANYREGID' ) ) ;
Thank you. It is what I was looking for.
And, one question regarding this, please. Could you give me an advice if possible how do I update multiple records by XML ?
Thank you for your help.
Give an example please.
Thank you, I will create new thread.