ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【笔记】oracle xml (一)

【笔记】oracle xml (一)

原创 Linux操作系统 作者:yellowlee 时间:2009-04-19 19:55:02 0 删除 编辑

--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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 9i的bug?
请登录后发表评论 登录
全部评论

注册时间:2008-12-27

  • 博文量
    316
  • 访问量
    659668