XMLType pl/sql update child

问题:

i have XML structure like below in XMLType column (Oracle 10g)

  <DATA>
  <CHILDRENS>
    <CHILDRENS_DEF lname="smith">
      <CHILD_KOL no="1" name="Id" desc="Id" typ="NUMBER"/>
      <CHILD_KOL no="2" name="Fname" desc="Fname" typ="VARCHAR2"/>
    </CHILDRENS_DEF>
    <CHILDRENS_DATA>
      <CHILD_DATA no="1">
        <CHILD_COL_VALUE no="1" name="Id">1</CHILD_COL_VALUE>
        <CHILD_COL_VALUE no="2" name="Fname">Christopher</CHILD_COL_VALUE>
      </CHILD_DATA>
      <CHILD_DATA no="2">
        <CHILD_COL_VALUE no="1" name="Id">1</CHILD_COL_VALUE>
        <CHILD_COL_VALUE no="2" name="Fname">Alice</CHILD_COL_VALUE>
      </CHILD_DATA>
      <CHILD_DATA no="3">
        <CHILD_COL_VALUE no="1" name="Id">1</CHILD_COL_VALUE>
        <CHILD_COL_VALUE no="2" name="Fname">John</CHILD_COL_VALUE>
      </CHILD_DATA>
    </CHILDRENS_DATA>
  </CHILDRENS>
  <CHILDRENS>
    <CHILDRENS_DEF lname="mushroom">
      <CHILD_KOL no="1" name="Id" desc="Id" typ="NUMBER"/>
      <CHILD_KOL no="2" name="Fname" desc="Fname" typ="VARCHAR2"/>
    </CHILDRENS_DEF>
    <CHILDRENS_DATA>
      <CHILD_DATA no="1">
        <CHILD_COL_VALUE no="1" name="Id">1</CHILD_COL_VALUE>
        <CHILD_COL_VALUE no="2" name="Fname">Arthur</CHILD_COL_VALUE>
      </CHILD_DATA>
    </CHILDRENS_DATA>
  </CHILDRENS>
</DATA>

How I can update child name, where CHILDRENS_DEF lname=smith and CHILD_DATA no=2 ??

回答1:

You can use updateXML,
the XPATH expression should look somewhat like this:

DATA/CHILDRENS[CHILDRENS_DEF/@lname="smith"]/CHILDRENS_DATA/CHILD_DATA[@no="2"]/CHILD_COL_VALUE[@name="Fname"]/text()

If you want to update the column in the table it should look like this:

UPDATE <<your_table>>
SET <<xml_col_name>> = updateXML(<<xml_col_name>>,
                                 'DATA/CHILDRENS[CHILDRENS_DEF/@lname="smith"]/CHILDRENS_DATA/CHILD_DATA[@no="2"]/CHILD_COL_VALUE[@name="Fname"]/text()',
                                 'AB');