Oracle PL/SQL/XML/XMLType
Содержание
- 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
<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>
