Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
1 reply Latest Post - ‏2013-04-05T10:35:29Z by SystemAdmin
SystemAdmin
SystemAdmin
230 Posts
ACCEPTED ANSWER

Pinned topic xpath update expression..

‏2013-03-29T12:27:51Z |
Hi,

Here is the xmldocument.
<Info>
<SE>
<ID>11111</ID>
<Status>Active</Status>
<Status2>Delete</Status2>
</SE>
<SE>
<SE>
<ID>22222</ID>
<Status>Archieve</Status>
<Status2>Delete</Status2>
</SE>
</Infor>
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;

Thanks,
Tilak
Updated on 2013-04-05T10:35:29Z at 2013-04-05T10:35:29Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    230 Posts
    ACCEPTED ANSWER

    Re: xpath update expression..

    ‏2013-04-05T10:35:29Z  in response to SystemAdmin
    I think i got what i was looking for :-)

    Here is how it's done.

    
    UPDATE TGCMP00.USER_INFO  set ACTIVITY_XML = XMLQUERY(
    'transform copy $xml := $ACTIVITY_XML modify 
    
    for $j in $xml/Info/SE where $j/ID[.  eq $SE] 
    
    return ( 
    
    do replace value of ($j/Status)  with  $Active1, 
    
    do replace value of ($j/Status2) with $Active2 ) 
    
    return $xml
    ' passing i_se as 
    "SE" , i_active1 as 
    "Active1" , i_active2 as 
    "Active2" ) where guid_id = i_guid