XMLType pl/sql更新子级 [英] XMLType pl/sql update child

问题描述

我在XMLType列(Oracle 10g)中具有如下所示的XML结构

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>

如何更新子名称，其中CHILDRENS_DEF lname = smith和CHILD_DATA no = 2?

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

推荐答案

您可以使用

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');

这篇关于XMLType pl/sql更新子级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！