oracle xml数据类型常见操作

         oracle xml数据类型常见操作

分类： XML 数据库 2010-12-13 11:10 548人阅读 评论(0) 收藏 举报

xmloracletablereferenceactionreturning

xml数据类型名称为：XMLType；

如果此数据类型列在pl/sql查询时无法显示（比如用"*"查询的时候），可用ip_switchstates.getclobval()来查（ip_switchs为xml类型列）；

--需要了解细节的通知可以到下面网站去看看，里边有各个方法的语法和主意事项
--http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28369/xdb04cre.htm#i1030920

--创建一个表

CREATE TABLE table_with_xml_column (filename VARCHAR2(64), xml_document XMLType);

--向表中插入数据

INSERT INTO table_with_xml_column VALUES ('test' ,XMLType('<PurchaseOrder xmlns:xsi="http://www./2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation= "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd"> <Reference>SBELL-2002100912333601PDT</Reference> <Actions> <Action> <User>SVOLLMAN</User> </Action> </Actions> <Reject/> <Requestor>Sarah J. Bell</Requestor> <User>SBELL</User> <CostCenter>S30</CostCenter> <ShippingInstructions> <name>Sarah J. Bell</name> <address>400 Oracle Parkway Redwood Shores CA 94065 USA</address> <telephone>650 506 7400</telephone> </ShippingInstructions> <SpecialInstructions>Air Mail</SpecialInstructions> <LineItems> <LineItem ItemNumber="1"> <Description>A Night to Remember</Description> <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/> </LineItem> <LineItem ItemNumber="2"> <Description>The Unbearable Lightness Of Being</Description> <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/> </LineItem> <LineItem ItemNumber="3"> <Description>Sisters</Description> <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/> </LineItem> </LineItems> </PurchaseOrder> ')); INSERT INTO table_with_xml_column VALUES ('test' ,XMLType('<PurchaseOrder xmlns:xsi="http://www./2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation= "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd"> <Reference>SBELL-2002100912333601PDT</Reference> <Actions> <Action> <User>SVOLLMAN</User> </Action> </Actions> <Reject/> <Requestor>Sarah J. Bell</Requestor> <User>SBELL</User> <CostCenter>S30</CostCenter> <ShippingInstructions> <name>Sarah J. Bell</name> <address>400 Oracle Parkway Redwood Shores CA 94065 USA</address> <telephone>650 506 7400</telephone> </ShippingInstructions> <SpecialInstructions>Air Mail</SpecialInstructions> <LineItems> <LineItem ItemNumber="1"> <Description>A Night to Remember</Description> <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/> </LineItem> <LineItem ItemNumber="2"> <Description>The Unbearable Lightness Of Being</Description> <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/> </LineItem> <LineItem ItemNumber="3"> <Description>Sisters</Description> <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/> </LineItem> </LineItems> </PurchaseOrder> '));

--查询某个节点是否存在的方法

SELECT xml_document FROM table_with_xml_column WHERE XMLExists('//Part[@Id="715515011020"]'--xpath查询语法 PASSING xml_document);

SELECT xml_document FROM table_with_xml_column WHERE existsNode(xml_document, '//Part[@Id="715515011020"]') = 1;

 --获取部分节点

SELECT extract(xml_document, '//Part[@Id="715515011020"]') "Part" FROM table_with_xml_column WHERE existsNode(xml_document, '//Part[@Id="715515011020"]') = 1;

SELECT extract(xml_document, '//telephone/text()') "Part" FROM table_with_xml_column WHERE existsNode(xml_document, '//Part[@Id="715515011020"]') = 1;

--获取部分数据

extract().getStringVal() or extract().getnumberval(). SELECT extract(xml_document,'//address').getStringVal() FROM table_with_xml_column WHERE XMLExists('/PurchaseOrder' PASSING xml_document); SELECT extractValue(xml_document,'//address' ) FROM table_with_xml_column WHERE XMLExists('/PurchaseOrder' PASSING xml_document); SELECT XMLCast(XMLQuery('//address' PASSING xml_document RETURNING CONTENT) AS VARCHAR2(100)) "REFERENCE" FROM table_with_xml_column WHERE XMLExists('/PurchaseOrder' PASSING xml_document);

 --更新整个文档

UPDATE table_with_xml_column SET xml_document = XMLType('<PurchaseOrder xmlns:xsi="http://www./2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation= "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd"> <Reference>SBELL-2002100912333601PDT</Reference> <Actions> <Action> <User>SVOLLMAN</User> </Action> </Actions> <Reject/> <Requestor>Sarah J. Bell</Requestor> <User>SBELL</User> <CostCenter>S30</CostCenter> <ShippingInstructions> <name>Sarah J. Bell</name> <address>400 Oracle Parkway Redwood Shores CA 94065 USA</address> <telephone>650 506 7400</telephone> </ShippingInstructions> <SpecialInstructions>Air Mail</SpecialInstructions> <LineItems> <LineItem ItemNumber="1"> <Description>A Night to Remember</Description> <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/> </LineItem> <LineItem ItemNumber="2"> <Description>The Unbearable Lightness Of Being</Description> <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/> </LineItem> <LineItem ItemNumber="3"> <Description>Sisters</Description> <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/> </LineItem> </LineItems> </PurchaseOrder>') WHERE existsNode(xml_document, '/PurchaseOrder') = 1;

--updateXML 更新xml中单个数据

--先查看一下

SELECT extract(xml_document, '/PurchaseOrder/Actions/Action[1]/User/text()').getStringVal() ACTION FROM table_with_xml_column WHERE existsNode(xml_document, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;

 --更新数据

UPDATE table_with_xml_column SET xml_document = updateXML(xml_document, '/PurchaseOrder/Actions/Action[1]/User/text()', 'SKING') WHERE existsNode(xml_document, '/PurchaseOrder') = 1;

--更新多个数据节点 --先查看一下

SELECT extractValue(xml_document, '/PurchaseOrder/Requestor') NAME, extract(xml_document, '/PurchaseOrder/LineItems').getStringVal() LINEITEMS FROM table_with_xml_column WHERE existsNode(xml_document, '/PurchaseOrder') = 1;

--更新多条数据

UPDATE table_with_xml_column SET xml_document = updateXML(xml_document, '/PurchaseOrder/Requestor/text()', 'Stephen G. King', '/PurchaseOrder/LineItems/LineItem[1]/Part/@Id', '786936150421', '/PurchaseOrder/LineItems/LineItem[1]/Description/text()', 'The Rock', '/PurchaseOrder/LineItems/LineItem[3]', XMLType('<LineItem ItemNumber="99"> <Description>Dead Ringers</Description> <Part Id="715515009249" UnitPrice="39.95" Quantity="2"/> </LineItem>')) WHERE existsNode(xml_document, '/PurchaseOrder') = 1;

--节点赋值为空的效果

UPDATE table_with_xml_column SET xml_document = updateXML( xml_document, '/PurchaseOrder/LineItems/LineItem[Part/@Id="715515009058"]/Description', NULL, '/PurchaseOrder/LineItems/LineItem/Part[@Id="715515009058"]/@Quantity', NULL, '/PurchaseOrder/LineItems/LineItem[Description/text()="The Unbearable Lightness Of Being"]', NULL) WHERE existsNode(xml_document, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;

--数据节点赋值为空的效果

UPDATE table_with_xml_column SET xml_document = updateXML(xml_document, '/PurchaseOrder/LineItems/LineItem/Part[@Description="A Night to Remember"]/text()', NULL) WHERE existsNode(xml_document,'/PurchaseOrder') = 1;

--insertChildXML

SELECT extract(xml_document, '/PurchaseOrder/LineItems') FROM table_with_xml_column WHERE existsNode(xml_document, '/PurchaseOrder') = 1;

 UPDATE table_with_xml_column SET xml_document = insertChildXML(xml_document, '/PurchaseOrder/LineItems', 'LineItem', XMLType('<LineItem ItemNumber="99999"> <Description>The Harder They Come</Description> <Part Id="953562951413" UnitPrice="22.95" Quantity="1"/> </LineItem>')) WHERE existsNode(xml_document, '/PurchaseOrder') = 1;

--insertChildXMLbefore

UPDATE table_with_xml_column SET xml_document = insertChildXMLbefore (xml_document, '/PurchaseOrder/LineItems', 'LineItem', XMLType('<LineItem ItemNumber="1111"> <Description>The Harder They Come</Description> <Part Id="953562951413" UnitPrice="22.95" Quantity="1"/> </LineItem>')) WHERE existsNode(xml_document, '/PurchaseOrder') = 1;

 --insertChildXMLafter

UPDATE table_with_xml_column SET xml_document = insertChildXMLafter (xml_document, '/PurchaseOrder/LineItems', 'LineItem', XMLType('<LineItem ItemNumber="3333"> <Description>The Harder They Come</Description> <Part Id="953562951413" UnitPrice="22.95" Quantity="1"/> </LineItem>')) WHERE existsNode(xml_document, '/PurchaseOrder') = 1;

--insertXMLbefore

UPDATE table_with_xml_column SET xml_document = insertXMLbefore(xml_document, '/PurchaseOrder/LineItems/LineItem[1]', XMLType('<LineItem ItemNumber="314"> <Description>Brazil</Description> <Part Id="314159265359" UnitPrice="69.95" Quantity="2"/> </LineItem>')) WHERE existsNode(xml_document, '/PurchaseOrder') = 1;

--insertXMLafter

UPDATE table_with_xml_column SET xml_document = insertXMLafter(xml_document, '/PurchaseOrder/LineItems/LineItem[1]', XMLType('<LineItem ItemNumber="314"> <Description>Brazil</Description> <Part Id="314159265359" UnitPrice="69.95" Quantity="2"/> </LineItem>')) WHERE existsNode(xml_document, '/PurchaseOrder') = 1;

--appendChildXML

UPDATE table_with_xml_column SET xml_document = appendChildXML(xml_document, '/PurchaseOrder/Actions/Action[1]', XMLType('<Date>2002-11-04</Date>')) WHERE existsNode(xml_document, '/PurchaseOrder') = 1;

--deleteXML

 UPDATE table_with_xml_column SET xml_document = deleteXML(xml_document, '/PurchaseOrder/LineItems/LineItem[@ItemNumber="2222"]') WHERE existsNode(xml_document, '/PurchaseOrder') = 1;