в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:55:30Z

1 версия

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

==Call existsNode() function from xmltype data==



<source lang="sql">

SQL>
SQL> CREATE TABLE myTable
2 (myID NUMBER PRIMARY KEY,
3 myValue XMLTYPE
4 )XMLTYPE myValue STORE AS CLOB
5 /
Table created.
SQL>
SQL> select COUNT(*)
2 from myTable d
3 where d.myValue.existsNode("/ROWSET") = 1
4 /
COUNT(*)
----------
0
1 row selected.
SQL> drop table myTable;
Table dropped.
SQL>
SQL>

</source>

==Create a table to hold the simple "Hello World" XML document==



<source lang="sql">

SQL> CREATE TABLE myTable
2 (id NUMBER PRIMARY KEY
3 ,doc XMLType NOT NULL)
4 XMLTYPE doc STORE AS CLOB
5 /
Table created.
SQL>
SQL>
SQL>
SQL> DECLARE
2 v_doc XMLType;
3 v_text varchar2(100);
4 BEGIN
5 select doc into v_doc from myTable
6 where id = 2;
7
8 v_text := v_doc.extract("/message/body/text()" ).getstringval;
9
10 dbms_output.put_line(v_text);
11 END;
12 /
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

</source>

==Create table myTable of xmltype==



<source lang="sql">

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

SYS_NC_ROWINFO$
------------------------------------------------------
<customer>
<name>name value</name>
<telephone>123 555-1234</telephone>
1 row selected.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.

</source>

==Creation of tables using XMLType data type and set the xml schema==



<source lang="sql">

SQL>
SQL> CREATE TABLE xml_or (
2 id NUMBER PRIMARY KEY,
3 doc XMLTYPE)
4 XMLTYPE doc STORE AS OBJECT RELATIONAL
5 XMLSCHEMA "http://127.0.0.1/xdoc.xsd"
6 ELEMENT "doc"
7 /

</source>

==dbms_metadata.get_xml==



<source lang="sql">

SQL> set long 32000
SQL> set heading off
SQL> set pagesize 32000
SQL> spool xml
SQL>
SQL> select dbms_metadata.get_xml("TABLE", "emp", "sqle") from dual;
SQL>
SQL> spool off
SQL> Set heading on
SQL> set pagesize 20
SQL>
SQL>

</source>

==Extract can be used in WHERE clause to search xml document==



<source lang="sql">

SQL> CREATE TABLE myTable(
2 id NUMBER PRIMARY KEY
3 ,doc XMLType NOT NULL
4 )
5 XMLTYPE doc STORE AS CLOB
6 /
Table created.
SQL>
SQL>
SQL>
SQL> select doc
2 from myTable
3 where extract(doc, "/message/greeting/text()")
4 like "%it may concern%"
5 /
no rows selected
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

</source>

==Insert xml document to a XMLType column with xmltype function==



<source lang="sql">

SQL> CREATE TABLE myTable(
2 id NUMBER PRIMARY KEY,
3 emps XMLType NOT NULL
4 );
Table created.
SQL>
SQL> INSERT INTO myTable VALUES (1, xmltype("<?xml version="1.0" standalone="no"
?>
2 <emps>
3 <emp>
4 <home_address>address 1</home_address>
5 </emp>
6 </emps>")
7 );
1 row created.
SQL>
SQL>
SQL> select extract(emps, "/emps/emp/home_address/text()" )
2 from myTable
3 /

EXTRACT(EMPS,"/EMPS/EMP/HOME_ADDRESS/TEXT()")
------------------------------------------------------
address 1
1 row selected.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>
SQL>

</source>

==PL/SQL ability to access text within an XML document in the database==



<source lang="sql">

SQL> CREATE TABLE myTable
2 (id NUMBER PRIMARY KEY
3 ,doc XMLType NOT NULL)
4 XMLTYPE doc STORE AS CLOB
5 /
Table created.
SQL>
SQL>
SQL>
SQL> DECLARE
2 v_doc XMLType;
3 v_text varchar2(100);
4 BEGIN
5 select doc into v_doc from myTable
6 where id = 2;
7
8 v_text := v_doc.extract("/message/body/text()" ).getstringval;
9
10 dbms_output.put_line(v_text);
11 END;
12 /
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

</source>

==Query xmltype column==



<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>

==Store an XMLType type data in clob==



<source lang="sql">

SQL> CREATE TABLE myTable
2 (myID NUMBER PRIMARY KEY,
3 myValue XMLTYPE )
4 XMLTYPE myValue STORE AS CLOB
5 /
Table created.

SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>

</source>

==Update (i.e. replace) the entire XMLType column with new content==



<source lang="sql">

SQL> CREATE TABLE myTable
2 (id NUMBER PRIMARY KEY
3 ,doc XMLType NOT NULL)
4 XMLTYPE doc STORE AS CLOB
5 /
Table created.
SQL>
SQL>
SQL> update myTable
2 set doc = xmltype("<?xml version="1.0" standalone="yes"?>
3 <message type="1">
4 <greeting>Hi</greeting>
5 <body>body!</body>
6 </message>
7 ");
0 rows updated.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.

</source>

==Use Aggregrate functions on XML data==



<source lang="sql">

SQL> CREATE TABLE myTable
2 (myID NUMBER PRIMARY KEY,
3 myValue XMLTYPE )
4 XMLTYPE myValue STORE AS CLOB
5 /
Table created.

SQL>
SQL>
SQL> select count(extract(myValue,"/ROWSET") )
2 AS count
3 from myTable;
COUNT
----------
0
1 row selected.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

</source>

==Use Text() function to get text value from xml string==



<source lang="sql">

SQL> CREATE TABLE myTable(
2 id NUMBER PRIMARY KEY,
3 emps XMLType NOT NULL
4 );
Table created.
SQL>
SQL> INSERT INTO myTable VALUES (1, xmltype("<?xml version="1.0" standalone="no"
?>
2 <emps>
3 <emp>
4 <home_address>address 1</home_address>
5 </emp>
6 </emps>")
7 );
1 row created.
SQL>
SQL>
SQL> select extract(emps, "/emps/emp/home_address/text()" )
2 from myTable
3 /

EXTRACT(EMPS,"/EMPS/EMP/HOME_ADDRESS/TEXT()")
------------------------------------------------------
address 1
1 row selected.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>
SQL>

</source>

==Use value function with single xmltype table==



<source lang="sql">

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

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

</source>

==Use xmltype to convert xml string to xmltype data==



<source lang="sql">

SQL>
SQL>
SQL> CREATE TABLE myTable(
2 id NUMBER PRIMARY KEY,
3 emps XMLType NOT NULL
4 );
Table created.
SQL>
SQL> INSERT INTO myTable VALUES (1, xmltype("<?xml version="1.0" standalone="no"
?>
2 <emps>
3 <emp>
4 <home_address>address 1</home_address>
5 </emp>
6 </emps>")
7 );
1 row created.
SQL>
SQL>
SQL> select extract(emps, "/emps/emp/home_address/text()" )
2 from myTable
3 /

EXTRACT(EMPS,"/EMPS/EMP/HOME_ADDRESS/TEXT()")
------------------------------------------------------
address 1
1 row selected.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>

</source>

==Without the text() operator, returns node name + text value==



<source lang="sql">

SQL> CREATE TABLE myTable(
2 id NUMBER PRIMARY KEY
3 ,doc XMLType NOT NULL
4 )
5 XMLTYPE doc STORE AS CLOB
6 /
Table created.
SQL>
SQL>
SQL> select extract(doc, "/message/greeting")
2 from myTable;
no rows selected
SQL>
SQL>
SQL> drop table myTable;
Table dropped.

</source>

==XMLtype column==



<source lang="sql">

SQL>
SQL>
SQL> CREATE TABLE myTable(
2 id NUMBER PRIMARY KEY,
3 emps XMLType NOT NULL
4 );
Table created.
SQL>
SQL> INSERT INTO myTable VALUES (1, xmltype("<?xml version="1.0" standalone="no"
?>
2 <emps>
3 <emp>
4 <home_address>address 1</home_address>
5 </emp>
6 </emps>")
7 );
1 row created.
SQL>
SQL>
SQL> select extract(emps, "/emps/emp/home_address/text()" )
2 from myTable
3 /

EXTRACT(EMPS,"/EMPS/EMP/HOME_ADDRESS/TEXT()")
------------------------------------------------------
address 1
1 row selected.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>

</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/XML/XMLType - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия