Here is the xmldocument.
My requirement here is that i will need to update the <Status> and <Status2> for a particular ID only.
Basically i'm planning to write a stored procedure, wherein I will get the ID, Status and Status2 as Inputs.
I just need to update the Status and Status2 of the input ID alone.
So, i have coded the below SQL, which is working as expected.
All i want to know here is how do i pass the ID, Status and Status2 as a host variable when i embed this SQL in the Stored Procedure?
Any help would be appreciated.
UPDATE TGCMP00.USER_INFO set ACTIVITY_XML = XMLQUERY( 'transform copy $xml := $ACTIVITY_XML modify for $j in $xml/Info/SE where $j/ID[. eq "1"] return ( do replace value of ($j/Status) with "Active", do replace value of ($j/Status2) with "Delete" ) return $xml ') where guid_id = 'pguid1'
Just for information, here is an example SQL piece where i am passing it externally(using passing and $SE). But since in the above code, i have 3 such things to be passed externally (ID, Status and Status2), so was just thinking how to do it.
UPDATE TGCMP00.USER_INFO SET ACTIVITY_XML = XMLQUERY( 'transform copy $xml := $ACTIVITY_XML modify do insert $SE as last into $xml/Info return $xml ' passing activity_xml_text as "SE" ) WHERE GUID_ID = i_guid;