2 Replies Latest reply on Nov 18, 2019 11:43 AM by Solomon Yakobson

    extract xmltype value to string

    heloo

      data

      -----------------

       

       

      <row id='1'><a4 m='86'>852</a4>
      <a4 m='86' s='2'>852</a4>
      <a4 m='87'>555</a4><a4 m='87' s='2'>666</a4>
      <a4 m='88'>55555</a4><a4 m='88' s='2'>66666</a4>
      <a4 m='89'>999</a4><a4 m='89' s='2'>999</a4>
      <a4 m='86'>852</a4><a4 m='86' s='3'>852</a4>
      <a4 m='87'>555</a4><a4 m='87' s='3'>333</a4>
      <a4 m='88'>55555</a4><a4 m='88' s='3'>33333</a4>
      <a4 m='89'>999</a4><a4 m='89' s='3'>999</a4>
      <a5>4_ABCDFFFF.02_XXX_0</a5>
      </row>


      <row id='2'><a4 m='86'>852</a4>
      <a4 m='86' s='2'>852</a4>
      <a4 m='87'>555</a4><a4 m='87' s='2'>666</a4>
      <a4 m='88'>55555</a4><a4 m='88' s='2'>66666</a4>
      <a4 m='89'>999</a4><a4 m='89' s='2'>999</a4>
      <a4 m='86'>852</a4><a4 m='86' s='3'>852</a4>
      <a4 m='87'>555</a4><a4 m='87' s='3'>333</a4>
      <a4 m='88'>55555</a4><a4 m='88' s='3'>33333</a4>
      <a4 m='89'>999</a4><a4 m='89' s='3'>999</a4>
      <a5>4_ABCDFFFF.02_XXX_ABC_MN_0</a5>
      </row>

       

       

      <row id='3'><a4 m='86'>852</a4>
      <a4 m='86' s='2'>852</a4>
      <a4 m='87'>555</a4><a4 m='87' s='2'>666</a4>
      <a4 m='88'>55555</a4><a4 m='88' s='2'>66666</a4>
      <a4 m='89'>999</a4><a4 m='89' s='2'>999</a4>
      <a4 m='86'>852</a4><a4 m='86' s='3'>852</a4>
      <a4 m='87'>555</a4><a4 m='87' s='3'>333</a4>
      <a4 m='88'>55555</a4><a4 m='88' s='3'>33333</a4>
      <a4 m='89'>999</a4><a4 m='89' s='3'>999</a4>
      <a5>0_dfeddfdfdfdfsd.02_feggx_999</a5>
      </row>

       

       

       

       

       

       

      UPDATE tbl

      SET XMLCOL = XMLTYPE(

      REPLACE(

      XMLCAST(XMLQUERY('/row/a4 m='87'/text()' PASSING XMLCOL RETURNING CONTENT) AS VARCHAR(8)),

      '20180102,'20180101'

      )

      )

      where XMLCAST(XMLQUERY('/row/a4 m='87'/text()' PASSING XMLCOL RETURNING CONTENT) AS VARCHAR(8))='20180102'

       

       

      How can i extract the node value and pass to the 1st parameter of replace function? as i am getting error for the above sql using Oracle 12c.

        • 1. Re: extract xmltype value to string
          Gaz in Oz

          I do not see any error, you forgot to post it.

          To update an xml node, you use update with xmlquery, like the example shown in the Oracle documentation here:

          https://docs.oracle.com/database/121/ADXDB/xdb04cre.htm#ADXDB6043

          • 2. Re: extract xmltype value to string
            Solomon Yakobson

            I gave you so many examples and you still not paying any attention on how to update XML in Oracle:

             

            UPDATE TBL

               SET XMLCOL = XMLQuery(

                                     'copy $NEWXML := $XML

                                        modify(

                                               for $A4 in $NEWXML/row/a4[@m="87" and text()="20180102"]

                                                 return (

                                                         replace value of node $A4 with $D

                                                        )

                                              )

                                        return $NEWXML'

                                     PASSING XMLCOL AS "XML",

                                             '20180101' AS "D"

                                     RETURNING CONTENT

                                    )

               WHERE XMLEXISTS(

                               '/row/a4[@m="87" and text()="20180102"]'

                               PASSING XMLCOL

                              )

            /

             

            SY.