Oracle log_1.xml,【笔记】oracle xml （一）

--090419xml.sql 113

--test xml files:test1.xml

xsi:noNamespaceSchemaLocation="">

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