-- An example table of XMLTYPE CREATE TABLE PURCHASEORDER OF XMLTYPE;-- Insert a record to the XMLTYPE table. INSERT INTO purchaseorder VALUES(XMLTYPE(' <PurchaseOrder> <Reference>ADAMS-20011127121040988PST</Reference> <Actions> <Action> <User>SCOTT</User> <Date>2002-03-31</Date> </Action> </Actions> </PurchaseOrder>')); -- Create a database function that returns the inserted record as XMLTYPE. CREATE OR REPLACE FUNCTION getPurchaseOrder(reference VARCHAR2) RETURN XMLTYPE AS xml XMLTYPE; BEGIN SELECT VALUE(p) INTO xml FROM PURCHASEORDER p WHERE EXTRACTVALUE(VALUE(p),'/PurchaseOrder/Reference') = reference; RETURN xml; END; /commit;
希望能对你有帮助。 Oracle9i XML Database Developer's Guide - Oracle XML DB html: http://www.stanford.edu/dept/itss/docs/oracle/9i/appdev.920/a96620/toc.htm pdf: http://www.stanford.edu/dept/itss/docs/oracle/9i/appdev.920/a96620.pdf相关网页:Oracle XML DB http://www.oracle.com/technology/global/cn/tech/xml/xmldb/index.html Oracle 数据库 10g R1 XML DB 技术白皮书 (PDF) http://download.oracle.com/technology/tech/xml/xmldb/current/twp.pdf :) 楼主真有钱,一个贴发两个地方
CREATE TABLE PURCHASEORDER OF XMLTYPE;-- Insert a record to the XMLTYPE table.
INSERT INTO purchaseorder VALUES(XMLTYPE(' <PurchaseOrder> <Reference>ADAMS-20011127121040988PST</Reference> <Actions> <Action> <User>SCOTT</User> <Date>2002-03-31</Date> </Action> </Actions> </PurchaseOrder>')); -- Create a database function that returns the inserted record as XMLTYPE.
CREATE OR REPLACE FUNCTION getPurchaseOrder(reference VARCHAR2) RETURN XMLTYPE AS xml XMLTYPE; BEGIN SELECT VALUE(p) INTO xml FROM PURCHASEORDER p WHERE EXTRACTVALUE(VALUE(p),'/PurchaseOrder/Reference') = reference; RETURN xml; END;
/commit;
Oracle9i XML Database Developer's Guide - Oracle XML DB
html: http://www.stanford.edu/dept/itss/docs/oracle/9i/appdev.920/a96620/toc.htm
pdf: http://www.stanford.edu/dept/itss/docs/oracle/9i/appdev.920/a96620.pdf相关网页:Oracle XML DB
http://www.oracle.com/technology/global/cn/tech/xml/xmldb/index.html
Oracle 数据库 10g R1 XML DB 技术白皮书 (PDF)
http://download.oracle.com/technology/tech/xml/xmldb/current/twp.pdf
:) 楼主真有钱,一个贴发两个地方
我最近刚完成一个项目也是几于XMLTYPE的,你的这个问题很好解决,我给你一段SQL你可能就会明白了,如果还不明白可以加我的QQ,再具体讨论
SELECT EXTRACTVALUE(VALUE(X),'/TD/TDSEQ'),
EXTRACTVALUE(VALUE(X),'/TD/TDNO'),
EXTRACTVALUE(VALUE(X),'/TD/TDTY'),
EXTRACTVALUE(VALUE(X),'/TD/REF1'),
EXTRACTVALUE(VALUE(X),'/TD/REF2'),
EXTRACTVALUE(VALUE(X),'/TD/REF3'),
EXTRACTVALUE(VALUE(X),'/TD/AGENT4')
FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE_DATA, '/TD'))) X;Thanks
Hima