oracle to deal the xml field

For more information ,you can move to the website:

 

http://www.adp-gmbh.ch/xml/xpath.html

 

 

如果xml字段中有多个相同的item，比如object节点下面有多个void，则用[ ]中括号来指定哪一个void 。

 

 

select * from

extractvalue(xmltype(new_value), '//object/void[1]/object/void[attribute::property=''type'']/string') "eventType",

 

extractvalue(xmltype(new_value), '//object/void[2]/object/void[attribute::property=''type'']/string') "eventType"

 

from my_xml.

 

createtable my_xml

(

OLD_VALUE CLOB,

NEW_VALUE CLOB

)

insertinto my_xml(old_value,new_value)values('<product_details id="1">

<product id="21">

<name>test1</name>

<code>123</code>

</product>

<product id="20">

<name>test2</name>

<code>456</code>

</product>

</product_details>

','<product_details id="1">

<product id="21">

<name>test1</name>

<code>123</code>

</product>

<product id="20">

<name>test2</name>

<code>456</code>

</product>

</product_details>

');

select *from my_xml;

/*

<product_details id="1">

<product id="21">

<name>test1</name>

<code>123</code>

</product>

<product id="20">

<name>test2</name>

<code>456</code>

</product>

</product_details>*/

 

selectextractvalue(xmltype(new_value),'//product[attribute::id=''21'']/name') "name"from my_xml mx;

 

selectextractvalue(xmltype(new_value),'//product[attribute::id=''21'']/code') "code"from my_xml mx;

 

 

Tip:

1,because the type of mx.new_value in table my_xml is 'clob'*(see the sql of  create table ),so the first step ,you should change the type fromclob toxmltype by using xmltype funtion ,so that oracle can understand how to deal it.

 

2,you must set the path of xml field for extractvaluefunction as its second parameter ,

for the path ,you must attendtion,you can't return more than one value for the path ,in other words,you shoud using just like above[@id=1]to make sure return one value only.

 

 For more information ,you can move to the website:

 

http://www.adp-gmbh.ch/xml/xpath.html