I have a Table "Action" which has multiple columns. Some of columns are:
Id (Number)
Name (Varchar2)
Old_Action (XMLTYPE)
New_Action (XMLTYPE) {New Column added}
Action_Merged (Varchar2) {New Column added}
There is an existing oracle procedure that returns Clob data successfully as follows:
CREATE OR REPLACE PROCEDURE GET_ACTION (ID IN NUMBER, OUTPUT OUT CLOB)
AS
XMLOUTPUT XMLTYPE;
v_id Action.Id%TYPE;
v_query CLOB;
BEGIN
v_id :=ID;
v_query := 'SELECT XMLElement(
"CustomerFile",
(SELECT XMLAgg(
XMLElement(
"Customer
act.OLD_ACTION'.extract(''//CustomerType/*'',''xmlns="http://ift.tt/1qhYN2q"'')
))
FROM Action act
WHERE act.ID = ' || v_id || ' ))) FROM DUAL';
EXECUTE IMMEDIATE TO_CHAR(v_query) INTO XMLOUTPUT;
OUTPUT := XMLOUTPUT.getClobVal();
END GET_ACTION;
There is an enhancement request such that two new columns are added New_Action and Action_Merged. Initially only column Old_Action contains xmldata. But now there may be scenarios where New_Action columns will also contains xml data. Following are the conditions for data in both columns Old_Action and New_Action :
Old_Action New_Action Action_Merged
<old xml data> <null> <null>
<old xml data> <new xml data> <MergedIds>
Note:<old xml data> and <new xml data> are the xml containing Customer specific data with different xml nodes. <old xml data> and <new xml data> are just short form.
When New Action is merged, MergedIds are inserted into Action_Merged along with new XML data in New_Action column.
Problem is that If New_Action column contains data, then GET_ACTION procedure should return data from New_Action otherwise from Old_Action column in a single XML output. And there may be multiple records same Ids with Old_Action or New_Action
Sample data from Action table:
Id Name Old_Action New_Action Action_Merged
1 A <old xml data>
1 B <old xml data1> <new xml data> <1,2,3>
1 C <old xml data2> <new xml data1> <4,5,6>
2 D <old data>
and so on. Expected result from GET_ACTION procedure:
<CustomerFile>
<old xml data>
<new xml data>
<new xml data1>
</CustomerFile>
It means if Procedure is called for Id "1", then must return resulting xml containing old_xml_data for name A, new_xml_data for name B, and new_xml_data1 for name C.
Please help.
Thanks in advance.
No comments:
Post a Comment