【筆記】oracle xml （一）

--090419xml.sql 113
--test xml files:test1.xml

http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://www.oracle.com/xdb/po.xsd">
ADAMS-20011127121040988PST


      SCOTT
      2002-03-31


Julie P. Adams
ADAMS
R20

    Julie P. Adams

Redwood Shores, CA 94065

    650 506 7300

Ground


      The Ruling Class



      Diabolique



      8 1/2

--test.xml

oracle
oracle

--test xmltype
create sequence comm_seq
start with 1
increment by 1
maxvalue 9999999
minvalue 1
cache 10;

CREATE TABLE t_test_xml_Example1
(
KEYVALUE varchar2(10) primary key,
XMLCOLUMN xmltype
);

CREATE TABLE t_test_xml_XMLTABLE OF XMLType;
CREATE TABLE t_test_xml_XMLTABLE1 OF XMLType;

create or replace directory D_DIR
as 'E:\oracle\ora92\xml\';

create or replace directory D_DIR_EXP
as 'E:\oracle\ora92\exp\tuning';

create or replace function f_getClobDocument(filename in varchar2,
                                             charset in varchar2 default NULL)
return CLOB deterministic is
file        bfile := bfilename('D_DIR', filename);
charContent CLOB := ' ';
targetFile bfile;
lang_ctx    number := DBMS_LOB.default_lang_ctx;
charset_id number := 0;
src_offset number := 1;
dst_offset number := 1;
warning     number;
begin
if charset is not null then
    charset_id := NLS_CHARSET_ID(charset);
end if;
targetFile := file;
DBMS_LOB.fileopen(targetFile, DBMS_LOB.file_readonly);
DBMS_LOB.LOADCLOBFROMFILE(charContent,
                            targetFile,
                            DBMS_LOB.getLength(targetFile),
                            src_offset,
                            dst_offset,
                            charset_id,
                            lang_ctx,
                            warning);
DBMS_LOB.fileclose(targetFile);
return charContent;
end;

INSERT INTO t_test_xml_XMLTABLE
VALUES
(XMLTYPE(f_getClobDocument('test.xml')));

INSERT INTO t_test_xml_XMLTABLE1
VALUES
(XMLTYPE(f_getClobDocument('test1.xml')));

insert into t_test_xml_Example1
values
(comm_seq.nextval, XMLTYPE(f_getClobDocument('test.xml')));

commit;

--
--if the existsNode then return 1 ,else return 0
SELECT existsNode(value(X), '/root/book/name') FROM t_test_xml_XMLTABLE X;

SELECT count(*)
FROM t_test_xml_XMLTABLE x
WHERE existsNode(value(x), '/root/book') = 1;

SELECT extractValue(value(x), '/root/book/name')
FROM t_test_xml_XMLTABLE X;

SELECT existsNode(value(X),
'/PurchaseOrder/LineItems/LineItem[2]/Part[@Id = "037429135020"]')
FROM t_test_xml_XMLTABLE1 X;

SELECT existsNode(value(X),
'/ PurchaseOrder / LineItems / LineItem
[Description = "8 1/2"]')
FROM t_test_xml_XMLTABLE1 X;

SELECT count(*)
FROM t_test_xml_XMLTABLE1 x
WHERE existsNode(value(x), '/PurchaseOrder[User="ADAMS"]') = 1;

DELETE FROM t_test_xml_XMLTABLE1 x
WHERE existsNode(value(x), '/PurchaseOrder[User="ADAMS"]') = 1;

SELECT extractValue(value(x), '/PurchaseOrder/Reference') FROM t_test_xml_XMLTABLE1 X;

--extractValue only return one value
SELECT extractValue(value(X),
'/PurchaseOrder/LineItems/LineItem/Description')
FROM t_test_xml_XMLTABLE1 X;

--Using extractValue() in the WHERE Clause
SELECT extractValue(value(x), ' / PurchaseOrder / Reference')
FROM t_test_xml_XMLTABLE1 X, SCOTT.EMP
WHERE extractValue(value(x), ' / PurchaseOrder / User') = EMP.ENAME
AND EMP.EMPNO = 7876;

--extract() is used when the XPath expression will result in
--a collection of nodes being returned.
SELECT extract(value(X),
' / PurchaseOrder / LineItems / LineItem / Description')
FROM t_test_xml_XMLTABLE1 X;

SELECT extract(value(x), '/PurchaseOrder/Reference') FROM t_test_xml_XMLTABLE1 X;

--Using XMLSequence()
--XMLSequence() takes an XMLType containing a
--document fragment and returns a collection of XMLType objects.
SELECT extractValue(value(t), '/Description')
FROM t_test_xml_XMLTABLE1 X,
       TABLE(xmlsequence(extract(value(X),
                                 '/PurchaseOrder/LineItems/LineItem/Description'))) t;
--result:
EXTRACTVALUE(VALUE(T),'/DESCRI..'
1 The Ruling Class
2 Diabolique
3 8 1/2

--update xml use updateXML
SQL> UPDATE t_test_xml_XMLTABLE1 t
2     SET value(t) = updateXML(value(t),
3                              '/PurchaseOrder/Reference/text()',
4                              'MILLER-200203311200000000PST')
5   WHERE existsNode(value(t),
6                    '/PurchaseOrder[Reference="ADAMS-20011127121040988PST"]') = 1;

1 row updated

UPDATE t_test_xml_XMLTABLE1 t
   SET value(t) = updateXML(value(t),
                             '/PurchaseOrder/LineItems/LineItem[2]',
                             xmltype('
                                      Andrei Rublev
                                                                            Quantity="2"/>
                                      '))
WHERE existsNode(value(t),
                  '/PurchaseOrder[Reference="MILLER-200203311200000000PST"]') = 1;

SELECT value(t) FROM t_test_xml_XMLTABLE1 t;

--some others xmltype functions
createXML();
isFragment();
getClobVal();
getRootElement();
getNameSpace();

insert into t_test_xml_XMLTABLE1
values
(Xmltype.createXML('oracle'));

select value(t).getRootElement() FROM t_test_xml_XMLTABLE1 t;
select value(t).isFragment() FROM t_test_xml_XMLTABLE1 t;
select value(t).getClobVal() FROM t_test_xml_XMLTABLE1 t;
select value(t).getNameSpace() FROM t_test_xml_XMLTABLE1 t;

--Using XML Schema with Oracle XML DB

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-590956/，如需轉載，請註明出處，否則將追究法律責任。

【筆記】oracle xml （一）

相關文章