Oracle PL/SQL Tutorial/XML/XMLTYPE
Содержание
- 1 Creation of tables using XMLType data type and set the xml schema
- 2 Individual fields from the XMLTYPE"d column may be found using the EXTRACTVALUE function like this:
- 3 Insert xml document to a XMLType column with xmltype function
- 4 PL/SQL ability to access text within an XML document in the database
- 5 Query xmltype column
- 6 Retrieve value from SYS.XMLTYPE column
- 7 Store an XMLType type data in clob
- 8 Use sys.xmltype.createxml to add XML data to table
- 9 Use value function with single xmltype table
- 10 Use xmltype to convert xml string to xmltype data
- 11 XMLTYPE column
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 /
Individual fields from the XMLTYPE"d column may be found using the EXTRACTVALUE function like this:
SQL>
SQL>
SQL> --EXTRACTVALUE is an Oracle function that uses an XPath expression,
SQL>
SQL> CREATE TABLE testxml (id NUMBER(3), dt SYS.XMLTYPE);
Table created.
SQL>
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> select EXTRACTVALUE(t.dt,"//customer/name") from testxml t;
EXTRACTVALUE(T.DT,"//CUSTOMER/NAME")
-----------------------------------------------------------------------------------------
Joe Smith
SQL>
SQL>
SQL> drop table testxml;
Table dropped.
SQL>
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.
Retrieve value from SYS.XMLTYPE column
SQL> CREATE TABLE testxml (id NUMBER(3), dt SYS.XMLTYPE);
Table created.
SQL>
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>
SQL> SELECT *
2 FROM testxml t
3 WHERE t.dt.getclobval() LIKE "%Joe%"
4 /
ID DT
------------------------------------------------------------------------------------------------------
111 <?xml version="1.0"?><customer><name>Joe Smith</name><title>Mathematician</title></customer>
SQL>
SQL> drop table testxml;
Table dropped.
SQL>
SQL>
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>
Use sys.xmltype.createxml to add XML data to table
SQL> CREATE TABLE testxml (id NUMBER(3), dt SYS.XMLTYPE);
Table created.
SQL>
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> SELECT t.dt.getclobval()
2 FROM testxml t
3 WHERE ROWNUM < 2
4 /
T.DT.GETCLOBVAL()
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<customer>
<name>Joe Smith</name>
<title>Mathematician</title>
</customer>
SQL>
SQL> drop table testxml;
Table dropped.
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>
XMLTYPE column
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:
SQL> CREATE TABLE testxml (id NUMBER(3), dt SYS.XMLTYPE);
Table created.
SQL>
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.
SQL>