oracle xml数据类型常见操作

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.w3.org/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.w3.org/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.w3.org/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 ;