怎么在oracle xmltype里添加属性？用oracle 自带函数？求解答

Question

oraclexml

Answer 1

使用insertchildxml(xmltype_column, new_attribute, new_attribute_name);SQL> CREATE TABLE xml_test(xmldoc sys.xmltype);
 
Table created
SQL> INSERT INTO xml_test VALUES(sys.xmlType.createXML('<name><a id="1" attribute1="attribute1 value">abc</a></name>'));
 
1 row inserted
SQL> select i.xmldoc.extract('//name/a[@id=1]/@attribute1').getStringVal() from xml_test i;
 
I.XMLDOC.EXTRACT('//NAME/A[@ID
--------------------------------------------------------------------------------
attribute1 value
SQL> update xml_test set xmldoc=insertchildxml(xmldoc,'//name/a[@id=1]','@new_attribute','new attribute value');
 
1 row updated
SQL> select i.xmldoc.extract('//name/a[@id=1]/@attribute1').getStringVal(), i.xmldoc.extract('//name/a[@id=1]/@new_attribute').getStringVal() from xml_test i;
 
I.XMLDOC.EXTRACT('//NAME/A[@ID                                                   I.XMLDOC.EXTRACT('//NAME/A[@ID
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
attribute1 value                                                                 new attribute value
 
SQL> drop table xml_test;
 
Table dropped
 
SQL>