Topic
  • 3 replies
  • Latest Post - ‏2012-02-05T20:52:14Z by MatthiasNicola
KKCrazy
KKCrazy
5 Posts

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
    322 Posts

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

    ‏2012-02-04T18:51:36Z  
    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

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

    ‏2012-02-05T11:40:39Z  
    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.

    <pre class="jive-pre"> 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. </pre>

    Does that help?

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    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
  • MatthiasNicola
    MatthiasNicola
    322 Posts

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

    ‏2012-02-05T20:52:14Z  
    • KKCrazy
    • ‏2012-02-05T11:40:39Z
    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
    You're welcome. I'm glad it worked for you.

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/