Oracle PL/SQL/XML/XMLType — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 1 Call existsNode() function from xmltype data
- 2 Create a table to hold the simple "Hello World" XML document
- 3 Create table myTable of xmltype
- 4 Creation of tables using XMLType data type and set the xml schema
- 5 dbms_metadata.get_xml
- 6 Extract can be used in WHERE clause to search xml document
- 7 Insert xml document to a XMLType column with xmltype function
- 8 PL/SQL ability to access text within an XML document in the database
- 9 Query xmltype column
- 10 Store an XMLType type data in clob
- 11 Update (i.e. replace) the entire XMLType column with new content
- 12 Use Aggregrate functions on XML data
- 13 Use Text() function to get text value from xml string
- 14 Use value function with single xmltype table
- 15 Use xmltype to convert xml string to xmltype data
- 16 Without the text() operator, returns node name + text value
- 17 XMLtype column
- 18 XMLTYPE with xml schema
Call existsNode() function from xmltype data
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>
Create a table to hold the simple "Hello World" XML document
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>
Create table myTable of xmltype
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.
Creation of tables using XMLType data type and set the xml schema
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 /
dbms_metadata.get_xml
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>
Extract can be used in WHERE clause to search xml document
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>
Insert xml document to a XMLType column with xmltype function
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>
PL/SQL ability to access text within an XML document in the database
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>
Query xmltype column
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.
Store an XMLType type data in clob
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>
Update (i.e. replace) the entire XMLType column with new content
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.
Use Aggregrate functions on XML data
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>
Use Text() function to get text value from xml string
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>
Use value function with single xmltype table
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>
Use xmltype to convert xml string to xmltype data
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>
Without the text() operator, returns node name + text value
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.
XMLtype column
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>
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.