Oracle PL/SQL Tutorial/XML/Insert

Insert XML document to database

XMLTYPE has built-in functions to allow us to manipulate the data values being placed into the column defined as SYS.XMLTYPE. Data may be inserted into the table using the sys.xmltype.createxml procedure like this:

SQL>
SQL> --
SQL>
SQL>
SQL>
SQL> CREATE TABLE testxml (id NUMBER(3), dt SYS.XMLTYPE);
Table created.
SQL>
SQL> INSERT INTO testxml VALUES(111,
  2  sys.xmltype.createxml(
  3  "<?xml version="1.0"?>
  4  <customer>
  5  <name>Joe Smith</name>
  6  <title>Mathematician</title>
  7  </customer>"))
  8  /
1 row created.
SQL>
SQL> SET LONG 2000
SQL>
SQL> SELECT *
  2  FROM testxml
  3  /
ID        DT
-------------------
111       <?xml version="1.0"?><customer><name>Joe Smith</name><title>Mathematician</title></customer>

SQL>
SQL> drop table testxml;
Table dropped.

Reference one element

SQL> create table myTable of xmltype;
Table created.
SQL>
SQL> insert into myTable values(XMLTYPE("
  2    <customer>
  3     <name>AAA</name>
  4      <telephone>123 555-1234</telephone>
  5    </customer>"))
  6  /
1 row created.
SQL>
SQL> select * from myTable;

SYS_NC_ROWINFO$
------------------------------------------------------
  <customer>
   <name>AAA</name>
    <telephone>123 555-1234</telephone>
  </cu
1 row selected.
SQL>
SQL> update myTable c
  2  set value(c) = updateXML(value(c), "/customer/telephone/text()","888 555-1234")
  3  where existsnode(value(c),"/customer/name = "Chris Smith"") = 1
  4  /
0 rows updated.
SQL>
SQL> drop table myTable;
Table dropped.

XMLTYPE with xml schema

SQL> create table myTable(
  2     id number(9),
  3     myValue xmltype
  4  );
Table created.
SQL> begin
  2    dbms_xmlschema.registerSchema ("http://d.ru/myType.xsd",xdbURIType("/xsd/myType.xsd").getClob(),True,True,False,True);
  3  end;
  4  /

SQL>
SQL> insert into myTable values (67, XMLTYPE("<myType xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://d.ru/myType.xsd">
  2       <value1>1</value1>
  3       <value2>2</value2>
  4   </myType>"))
  5  /
1 row created.
SQL>
SQL> select * from myTable;

   emp
Number
------
MYVALUE
------------------------------------------------------
    67
<myType xmlns:xsi="http://www.w3.org/2001/XMLSchema-in
stance" xsi:noNamespaceSch
1 row selected.
SQL>
SQL> drop table myTable;
Table dropped.