XMLType pl/sql add child

Question

I have problem with correct adding childs from pl/sql into xml structure like as below.

  
  
    
      
      
    
    
      
        1
        Christopher
      
      
        1
        Alice
      
      
        1
        John
      
    
  
  
    
      
      
    
    
      
        1
        Arthur

I want to add child to CHILDRENS where CHILDRENS_DEF = lname=mushroom. When I do something like that:

UPDATE xml_childrens
SET  CLOBXMLCOL = INSERTCHILDXML(CLOBXMLCOL, 'DATA/CHILDRENS','CHILD_DATA', 
                XMLType('
                            2
                            Adele
                         '))

I add new child for all families. How I can add CHILD_DATA only for lname=mushroom. Question no 2. How can I count CHILD_DATA for family?

Justin Cave · Accepted Answer

Set up the table and insert the data (not that I assume your actual data ends with an tag so that it's valid XML

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( '
  3    
  4      
  5        
  6        
  7      
  8      
  9        
 10          1
 11          Christopher
 12        
 13        
 14          1
 15          Alice
 16        
 17        
 18          1
 19          John
 20        
 21      
 22    
 23    
 24      
 25        
 26        
 27      
 28      
 29        
 30          1
 31          Arthur
 32        
 33      
 34    
 35* ' )
SQL> /

You can use a following-sibling in your XPath (though it would be much more logical to change the definition of your 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('
  6                              2
  7                              Adele
  8*                          '))
SQL> /

1 row updated.

SQL> select * from xml_children;

XML_DATA
--------------------------------------------------------------------------------

  
    
      
      
    
    
      
        1
        Christopher
      
      
        1
        Alice
      
      
        1
        John
      
    
  
  
    
      
      
    
    
      
        1
        Arthur
      
      
        2
        Adele
      
    
  

1 row created.

XMLType pl/sql add child

Answers (1)

Related Questions