Oracle PL/SQL/XML/xpath
Содержание
- 1 extract(doc, "/message/greeting/text()")
- 2 Extract returns a COLLECTION of values (a repeating node)
- 3 Find rows that contain the ROWSET/ROW node
- 4 Oracle support XPath predicate expressions
- 5 Use extract function and xpath function to retrieve xml element data
- 6 Use extract function to get xmltype data
- 7 Use the EXTRACT function and the following XPath:/emps/emp/interests/interest/
- 8 XPath text() function
extract(doc, "/message/greeting/text()")
<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> -- Extract with text() operator returns scalar text value: SQL> select extract(doc, "/message/greeting/text()")
2 from myTable;
no rows selected SQL> SQL> drop table myTable; Table dropped.
</source>
Extract returns a COLLECTION of values (a repeating node)
<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 extract(myValue, "/ROWSET/ROW/TABLE_T/COL_LIST/COL_LIST_ITEM/NAME")
2 from myTable 3 where extractvalue(myValue, "/ROWSET/ROW/TABLE_T/SCHEMA_OBJ/NAME") = "T";
no rows selected SQL> SQL> SQL> drop table myTable; Table dropped. SQL>
</source>
Find rows that contain the ROWSET/ROW node
<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> SQL> select myID
2 from myTable 3 where existsnode(myValue, "/ROWSET/ROW") = 1 4 /
no rows selected SQL> SQL> SQL> drop table myTable; Table dropped. SQL>
</source>
Oracle support XPath predicate expressions
<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> update myTable
2 set doc = updateXml( doc,"message[greeting="Hi"]/body/text()", "test again") 3 /
0 rows updated. SQL> SQL> drop table myTable; Table dropped. SQL>
</source>
Use extract function and xpath function to retrieve xml element data
<source lang="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>
</source>
Use extract function to get xmltype data
<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 the EXTRACT function and the following XPath:/emps/emp/interests/interest/
<source lang="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 <interests> 5 <interest>i1</interest> 6 <interest>i2</interest> 7 <interest>i3</interest> 8 </interests> 9 </emp> 10 </emps>") 11 );
1 row created. SQL> SQL> SQL> select extract(emps,"/emps/emp/interests/interest/text()")
2 from myTable;
EXTRACT(EMPS,"/EMPS/EMP/INTERESTS/INTEREST/TEXT()")
i1i2i3 1 row selected. SQL> SQL> drop table myTable; Table dropped.
</source>
XPath text() function
<source lang="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>