в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:06:02Z

1 версия

Новая страница

== Insert XML document to database==

<p>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:</p>



<source lang="sql">
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.</source>

== Reference one element==



<source lang="sql">
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.</source>

== XMLTYPE with xml schema==



<source lang="sql">
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.</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/XML/Insert - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия