XMLType pl/sql添加子对象

Question

如下所示，我无法正确地将childs从pl/sql添加到xml结构中。

  <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=mushroom的孩子中。当我这样做的时候：

UPDATE xml_childrens
SET  CLOBXMLCOL = INSERTCHILDXML(CLOBXMLCOL, 'DATA/CHILDRENS','CHILD_DATA', 
                XMLType('<CHILD_DATA no="2">
                            <CHILD_COL_VALUE no="1" name="Id">2</CHILD_COL_VALUE>
                            <CHILD_COL_VALUE no="2" name="Fname">Adele</CHILD_COL_VALUE>
                         </CHILD_DATA>'))

我为所有家庭添加新的子项。如何仅为lname=mushroom添加CHILD_DATA。问题2.我怎么才能把CHILD_DATA算作家庭成员呢？

Answer 1

设置表并插入数据(我并不是假设您的实际数据以</DATA>标记结尾，这样它就是有效的

SQL> create table xml_children (
  2    xml_data xmltype
  3  );

Table created.


SQL> ed
Wrote file afiedt.buf

  1  insert into xml_children
  2   values( '<DATA>
  3    <CHILDRENS>
  4      <CHILDRENS_DEF lname="smith">
  5        <CHILD_KOL no="1" name="Id" desc="Id" typ="NUMBER"/>
  6        <CHILD_KOL no="2" name="Fname" desc="Fname" typ="VARCHAR2"/>
  7      </CHILDRENS_DEF>
  8      <CHILDRENS_DATA>
  9        <CHILD_DATA no="1">
 10          <CHILD_COL_VALUE no="1" name="Id">1</CHILD_COL_VALUE>
 11          <CHILD_COL_VALUE no="2" name="Fname">Christopher</CHILD_COL_VALUE>
 12        </CHILD_DATA>
 13        <CHILD_DATA no="2">
 14          <CHILD_COL_VALUE no="1" name="Id">1</CHILD_COL_VALUE>
 15          <CHILD_COL_VALUE no="2" name="Fname">Alice</CHILD_COL_VALUE>
 16        </CHILD_DATA>
 17        <CHILD_DATA no="3">
 18          <CHILD_COL_VALUE no="1" name="Id">1</CHILD_COL_VALUE>
 19          <CHILD_COL_VALUE no="2" name="Fname">John</CHILD_COL_VALUE>
 20        </CHILD_DATA>
 21      </CHILDRENS_DATA>
 22    </CHILDRENS>
 23    <CHILDRENS>
 24      <CHILDRENS_DEF lname="mushroom">
 25        <CHILD_KOL no="1" name="Id" desc="Id" typ="NUMBER"/>
 26        <CHILD_KOL no="2" name="Fname" desc="Fname" typ="VARCHAR2"/>
 27      </CHILDRENS_DEF>
 28      <CHILDRENS_DATA>
 29        <CHILD_DATA no="1">
 30          <CHILD_COL_VALUE no="1" name="Id">1</CHILD_COL_VALUE>
 31          <CHILD_COL_VALUE no="2" name="Fname">Arthur</CHILD_COL_VALUE>
 32        </CHILD_DATA>
 33      </CHILDRENS_DATA>
 34    </CHILDRENS>
 35* </DATA>' )
SQL> /

您可以在XPath中使用following-sibling (尽管更改XML的定义会更符合逻辑)

SQL> ed
Wrote file afiedt.buf

  1  update xml_children
  2     set xml_data = InsertChildXML( xml_data,
  3                                    '/DATA/CHILDRENS/CHILDRENS_DEF[@lname="mushroom"]/following-sibling::*',
  4                                    'CHILD_DATA',
  5                                    XMLType('<CHILD_DATA no="2">
  6                              <CHILD_COL_VALUE no="1" name="Id">2</CHILD_COL_VALUE>
  7                              <CHILD_COL_VALUE no="2" name="Fname">Adele</CHILD_COL_VALUE>
  8*                          </CHILD_DATA>'))
SQL> /

1 row updated.

SQL> select * from xml_children;

XML_DATA
--------------------------------------------------------------------------------
<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>
      <CHILD_DATA no="2">
        <CHILD_COL_VALUE no="1" name="Id">2</CHILD_COL_VALUE>
        <CHILD_COL_VALUE no="2" name="Fname">Adele</CHILD_COL_VALUE>
      </CHILD_DATA>
    </CHILDRENS_DATA>
  </CHILDRENS>
</DATA>
1 row created.