2 Replies Latest reply: Apr 18, 2012 1:46 PM by 931239 RSS

    Oracle XMLType deleteXML problem

    931239
      Hi,
      I am new using oracle xmltype, got a idiot problem when call deleteXML with xpath. I am hoping I can get help here. Code below describes how I did and what is the problem/

      BTW, My oracle is
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

      Thanks in advance.

      Zhuang Zuo
      software developer
      zhuang.zuo@pioneer.com

      ===================================
      -- step 1. create a table
      create table XML_TABLE
      (
      uname varchar2(50),
      xml_col xmltype
      )
      ;

      -- step 2. insert data
      insert into xml_table
      (uname, xml_col)
      values (
      'z1',
      xmltype
      (
      '
      <root>
      <nodes>
      <node attr="a" />
      <node attr="b" />
      </nodes>
      </root>
      '
      )
      );

      -- step3. query data
      select uname,
      extract (xml_col, '/root') root,
      extract (xml_col, '/root/nodes') nodes,
      extract (xml_col, '/root/nodes/node') node,
      extract (xml_col, '/root/nodes/node[@attr="a"]') node_a,
      extract (xml_col, '/root/nodes/node[@attr="b"]') node_b
      from xml_table
      ;

      /* ***
      all above return data as expected.
      -- "root" returns:
      <root>
      <nodes>
      <node attr="a"/>
      <node attr="b"/>
      </nodes>
      </root>

      -- "nodes" returns:
      <nodes>
      <node attr="a"/>
      <node attr="b"/>
      </nodes>

      -- "node" returns:
      <node attr="a"/>
      <node attr="b"/>

      -- "node_a" returns:
      <node attr="a"/>

      -- "node_b" returns:
      <node attr="b"/>

      -- no problem so far
      ****/


      -- step 4. delete a node with xpath using update/deleteXML
      update xml_table
      set xml_col = deleteXML (xml_col, '/root/nodes/node[@attr="a"]')
      where existsnode (xml_col, '/root/nodes/node[@attr="a"]') = 1
      ;

      /* ***
      problem occurs here, showing in next step
      * ***/

      -- step5. repeat step3, query data using same sql in step 3
      /* **
      --"root" returns data as expected:
      <root>
      <nodes>
      <node attr="b"/>
      </nodes>
      </root>

      --"nodes" returns data as expected:
      <nodes>
      <node attr="b"/>
      </nodes>

      --"node_a" return NO data, as expected

      --however, "node", "node_b" return NO data either, even though "root" and "nodes" return correct data

      why?
      * **/

      PLEASE HELP ....

      Edited by: user10382724 on Apr 17, 2012 8:54 AM