4 Replies Latest reply on Aug 14, 2012 8:21 AM by BluShadow

    How to update a particular XML node without attribute.

    955609
      I am facing some issue while updating a XML node ('< `a><`b>NULL< //b><//a>'). In my XML b also exists with an attribute value too.

      '< `a >
      <` b>NULL< //b>
      <`b Attr="attr_val">Raj< //b>
      < //a>'

      So while using below updatexml, both the nodes are getting updated.

      UPDATE xml_temp
      SET FPML=UPDATEXML(FPML,'/a/b/text()','some_val');

      Can somebpdy please help to update node b of without attribute.
      Thanks in advance.

      P.S.: I have made XML tags invalid purposefully. Don't mind to that :).

      Edited by: 952606 on Aug 13, 2012 10:35 PM

      Edited by: 952606 on Aug 13, 2012 10:37 PM
        • 1. Re: How to update a particular XML node without attribute.
          ShankarViji
          Hi User,

          Welcome to Forum !!!!

          Please view the Link below

          http://www.oratechinfo.co.uk/sqlxml.html

          Thanks,
          Shankar
          • 2. Re: How to update a particular XML node without attribute.
            APC
            Which version of the database are you using? XML is one area where the available functionality changes quite considerably between versions.

            Cheers, APC
            • 3. Re: How to update a particular XML node without attribute.
              odie_63
              Hi,
              P.S.: I have made XML tags invalid purposefully. Don't mind to that :).
              Learn how to use &#x7B;code} tags... It's explained here (among other places) : {message:id=9360002}
              <a>
                 <b>NULL</b>
                 <b Attr="attr_val">Raj</b>
              </a>
              So while using below updatexml, both the nodes are getting updated.
              Of course they are. You have to apply an XPath predicate to select only the required element :
              UPDATE xml_temp
              SET fpml = UPDATEXML(fpml, '/a/b[not(@*)]/text()', 'some_val');
              • 4. Re: How to update a particular XML node without attribute.
                BluShadow
                As odie showed, or if it's a specific attribute name you are looking to avoid, then replace the "*" with the name e.g.
                SQL> ed
                Wrote file afiedt.buf
                
                  1  with t as (select xmltype('<a>
                  2    <b>NULL</b>
                  3    <b Attr="attr_val">Raj</b>
                  4  </a>') as xml from dual)
                  5  --
                  6  -- end of test data
                  7  --
                  8  select xml
                  9        ,updatexml(xml,'/a/b[not(@Attr)]/text()','some_val') as new_xml
                 10* from t
                SQL> /
                
                XML                                                NEW_XML
                -------------------------------------------------- ------------------------------------------------------
                <a>                                                <a><b>some_val</b><b Attr="attr_val">Raj</b></a>
                  <b>NULL</b>
                  <b Attr="attr_val">Raj</b>
                </a>