oracle与xml的详细介绍.doc

Oracle XML DB之浅入浅出作者：fuyuncat来源：WWW.HelloDBA.COM备注：XML DB是Oracle 9.2中出现的新特性。1 XML DB安装通过DBCA安装2 XML DB数据处理2.1 存储XML数据到XML表中2.1.1创建一个有XMLType类型字段的表：create table xmlcontent (keyvalue varchar2(10) primary key, xmlvalue xmltype);2.1.2创建一个XMLType类型表create table xmltable of xmltype;2.1.3从XML文件中读取数据存储到XML表中创建Directory指向存放XML文件的路径：SQL grant create any directory to xdb;Grant succeeded.SQL conn xdb/xdbConnected.SQL create directory XMLDIR as C:oracleXMLDB;Directory created.创建存储过程，从XML文件中读取数据存储到XML表中create or replace function getClobDocument( filename in varchar2, charset in varchar2 default NULL)return CLOB deterministicis file bfile := bfilename(XMLDIR,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;/- you can use getCLOBDocument() to generate a CLOB from a file containin- an XML document. For example, the following statement inserts a row into the - XMLType table Example2 created earlier:向表中插入数据：SQL INSERT INTO XMLTABLE 2 VALUES(XMLTYPE(getCLOBDocument(init.xml);1 row created.SQL INSERT INTO XMLContent 2 VALUES(1, XMLTYPE(getCLOBDocument(init.xml, UTF8);1 row created.SQL commit;Commit complete.2.2 更新XML表数据2.2.1普通Update方式SQL UPDATE XMLTABLE X SET VALUE(X)=XMLTYPE(getCLOBDocument(init.xml);1 row updated.SQL UPDATE XMLContent SET xmlvalue=XMLTYPE(getCLOBDocument(init.xml) 2 WHERE keyvalue=1;1 row updated.SQL commit;Commit complete.2.2.2使用updatexml()更新节点值SQL UPDATE xmlcontent 2 SET xmlvalue = updateXML(xmlvalue, 3 /sqlstress/config/userName/text(), 4 shanxi) 5 WHERE existsNode(xmlvalue, 6 /sqlstress/configuserName=shanxi806) = 1;1 row updated.SQL commit;Commit complete.SQL select extractvalue(xmlvalue,/sqlstress/config/userName) 2 from xmlcontent;EXTRACTVALUE(XMLVALUE,/SQLSTRESS/CONFIG/USERNAME)-shanxi2.2.3使用updatexml()更新一个节点树SQL UPDATE xmlcontent 2 SET xmlvalue = 3 updateXML(xmlvalue, 4 /sqlstress/functions/function1/parameters/prameter2/valuecope, 5 xmltype( 6 78 9 10 1399999999911 12 13 )14 )15 WHERE existsNode(xmlvalue,16 /sqlstress/functions/function1/parameters/parameterpid=217 ) = 1;1 row updated.2.3 从XML表中读取数据主要利用extract(), extractValue(), and existsNode()等几个函数。”Init.xml”的内容参见附录。2.3.1existsNode ()EXISTSNODE函数检查XML中的某一个节点是否存在。如果存在，返回1，否则返回0。SQL SELECT existsNode(value(X),/sqlstress/config/connURL) 2 FROM XMLTABLE X;EXISTSNODE(VALUE(X),/SQLSTRESS/CONFIG/CONNURL)- 1SQL SELECT existsNode(value(X),/sqlstress/config/connURLFalse) 2 FROM XMLTABLE X;EXISTSNODE(VALUE(X),/SQLSTRESS/CONFIG/CONNURLFALSE)- 0SQL SELECT existsNode(xmlvalue,/sqlstress/config/connURL) 2 FROM XMLContent X 3 WHERE keyvalue = 1;EXISTSNODE(XMLVALUE,/SQLSTRESS/CONFIG/CONNURL)- 1SQL SELECT existsNode(value(X), 2 /sqlstress/configuserPWD=|chr(10)| shanxi806|chr(10)| |) 3 FROM XMLTABLE X;EXISTSNODE(VALUE(X),/SQLSTRESS/CONFIGUSERPWD=|CHR(10)|SHANXI806|CHR(10)- 1SQL SELECT count(*) 2 FROM XMLContent x 3 WHERE existsNode(xmlvalue,/sqlstress/configuserName=shanxi806) = 1; COUNT(*)- 1SQL SELECT count(*) 2 FROM XMLContent x 3 WHERE existsNode(xmlvalue,/sqlstress/functions/functionid=1) = 1; COUNT(*)- 1SQL SELECT count(*) 2 FROM XMLContent x 3 WHERE existsNode(xmlvalue,/sqlstress/functions/function/parameters/parameter2pid=2) = 1; COUNT(*)- 12.3.2extractValue()EXTRACTVALUE()是从某个节点中读取值SQL select extractValue(xmlvalue, /sqlstress/config/userPWD) 2 from xmlcontent 3 where keyvalue=1;EXTRACTVALUE(XMLVALUE,/SQLSTRESS/CONFIG/USERPWD)- shanxi806SQL SELECT extractvalue(xmlvalue, /sqlstress/functions/function1/parameters/parameter2/pid) 2 FROM xmlcontent 3 WHERE keyvalue = 1;EXTRACTVALUE(XMLVALUE,/SQLSTRESS/FUNCTIONS/FUNCTION1/PARAMETERS/PARAMETER2/-2ExtractValue只能返回一个确切的位置节点的值，如果存在多个相同节点，Oracle就会报错：SQL SELECT extractvalue(xmlvalue, /sqlstress/functions/function1/parameters/parameter) 2 FROM xmlcontent 3 WHERE keyvalue = 1; FROM xmlcontent *ERROR at line 2:ORA-19025: EXTRACTVALUE returns value of only one nodeSQL SELECT extractvalue(xmlvalue, /sqlstress/config) 2 FROM xmlcontent 3 WHERE keyvalue = 1; FROM xmlcontent *ERROR at line 2:ORA-19025: EXTRACTVALUE returns value of only one node2.3.3Extract()EXTRACT函数返回一个XML文档的一个节点树，或者某一节点下所有符合条件的节点。返回一个节点树：SQL set line 100SQL set lone 20000SQL SELECT extract(xmlvalue, /sqlstress/config) 2 FROM xmlcontent 3 WHERE keyvalue = 1;EXTRACT(XMLVALUE,/SQLSTRESS/CONFIG)- jdbc:oracle:thin:31:1521:P51 shanxi806 shanxi806 100 1000 返回所有符合条件的节点：SQL SELECT extract(xmlvalue, /sqlstress/functions/function1/parameters/parameter/paraType) 2 FROM xmlcontent 3 WHERE keyvalue = 1;EXTRACT(XMLVALUE,/SQLSTRESS/FUNCTIONS/FUNCTION1/PARAMETERS/PARAMETER/PARATYPE)- String String int String String float int 2.3.4利用xmlsequence()和table()返回符合条件的节点的值：SQL SELECT extractValue(value(t),/paraType) 2 FROM XMLContent, 3 TABLE( xmlsequence ( 4 extract(xmlvalue, 5 /sqlstress/functions/function1/parameters/parameter/paraType) 6 ) t 7 WHERE keyvalue=1;EXTRACTVALUE(VALUE(T),/PARATYPE)- String String int String String float int2.4 XML的格式处理2.4.1利用transform()函数将XSLT应用到XML上SQL update xmltable x set value(x)=XMLTYPE(getclobdocument(example.xml);1 row updated.SQL commit;Commit complete.SQL SELECT value(t).transform(xmltype(getclobdocument(example.xsl) 2 from XMLTABLE t3 where existsNode(value(t), 4 /PurchaseOrderReference=ADAMS-20011127121040988PST 5 ) = 1;2.4.2利用XMLTransform()函数进行格式处理SQL select xmltransform(value(t), xmltype(getclobdocument(example.xsl)2 from XMLTABLE t;2.5 其他XMLType方法CREATEXML () ：一个用来创建XMLType实例的静态方法。ISFRAGMENT()：如果XMLType包含一个文档段则返回1。文档段的意思就是一个没有根节点的XML稳当。文档段一般可以通过Extract()函数