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.
3 replies Latest Post - ‏2012-02-05T20:52:14Z by MatthiasNicola
KKCrazy
KKCrazy
5 Posts
ACCEPTED ANSWER

Pinned topic Delete the Parent Element in an XML through Stored Procedure

‏2012-02-04T17:38:58Z |
Hi,

I am a bit new to this DB2 development.

Could some one help me in achieving the below requirement.

Sample XML


<main>
<info>
<name>
Janaki
</name>
<info>
<info>
<name>
Steve
</name>
<info>
<info>
<name>
Stan
</name>
<info>
<main>

The requirement is that we don't need the <info> element any more. we need to delete that.

Output required:

<main>
<name>
Janaki
</name>
<name>
Steve
</name>
<name>
Stan
</name>
<main>

I have written the below logic, but it deletes all the elements from <main>

xmlquery('copy $new := $INFO
modify do delete $new/main/name
return $new') ;

Can someone help me on this ASAP. Its kind of a urgent requirement for me.

Thanks
KK
Updated on 2012-02-05T20:52:14Z at 2012-02-05T20:52:14Z by MatthiasNicola
  • MatthiasNicola
    MatthiasNicola
    321 Posts
    ACCEPTED ANSWER

    Re: Delete the Parent Element in an XML through Stored Procedure

    ‏2012-02-04T18:51:36Z  in response to KKCrazy
    Hi KK,

    here are two different queries that both produce the result that you want. You can use these XMLQUERY functions also in the SET clause of an UPDATE statement if you want to change the document permanently.

    
    create table test(doc XML);   insert into test values(
    ' <main> <info><name>Janaki</name></info> <info><name>Steve</name></info> <info><name>Stan</name></info> </main> 
    ');   SELECT XMLQUERY(
    '<main>{$DOC/main/info/name}</main>') FROM test;   <main><name>Janaki</name><name>Steve</name><name>Stan</name></main>   1 record(s) selected.     SELECT XMLQUERY(
    'copy $new := $DOC modify ( 
    
    do insert $new/main/info/name into $new/main, 
    
    do delete $new/main/info ) 
    
    return $new
    ') FROM test;   <main><name>Janaki</name><name>Steve</name><name>Stan</name></main>   1 record(s) selected.
    


    Does that help?

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    • KKCrazy
      KKCrazy
      5 Posts
      ACCEPTED ANSWER

      Re: Delete the Parent Element in an XML through Stored Procedure

      ‏2012-02-05T11:40:39Z  in response to MatthiasNicola
      Its Amazing.
      It just worked in one shot and solved my so long problem.

      Thank you very much Matthias. I really appreciate your response on this.

      I would like to keep any more issues I face during my new journey in the DB2 PURE XML.

      Once again, Thank you very much.

      Thanks
      KK