Oracle PL/SQL Tutorial/XML/XMLTYPE

Материал из SQL эксперт
Перейти к: навигация, поиск

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>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/XML/XMLTYPE&oldid=3348»