Oracle PL/SQL/XML/XMLType

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

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