help with extracting value of xml tags 1

[sqleer]

Howdy,

I am an Oracle DBA and new to XML parsing.
Given the following data sample, I would like to achieve two things.

1. extract only the value of the "Root" tag which is C:\Flintstone

2. extract the tag "TM_PORT" and its value

The column is a CLOB.

thank you

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE preferences SYSTEM '

http://java.sun.com/dtd/preferences.dtd'>

<preferences EXTERNAL_XML_VERSION="1.0">
<root type="user">
<map />
<node name="com">
<map />
<node name="mynode">
<map />
<node name="utils">
<map />
<node name="properties">
<map>
<entry key="Save" value="ON" />
<entry key="LiveTradeSession" value="SOCKET"/>
<entry key="TM_PORT" value="12021" />
<entry key="TM_PORT2" value="12022" />
<entry key="Root" value="C:/Flintstone" />
<entry key="TRADEMONITOR" value="ON" />
<entry key="HEALTH_MONITOR_CRITICAL_DISK_SPACE" value="500" />

<entry key="HEALTH_MONITOR_WARNING_DISK_SPACE" value="750" />
<entry key="HEALTH_MONITOR_PERIOD" value="600000" />
</map>
</node>
</node>
</node>
</node>
</root>
</preferences>

 

[tsuji]

I suppose you mean to use some kind of api rather than anything else. In that case, the cleanest is to use its xpath support. One version of the xpaths to get to them respectively is this.
[tt][1] "//@value[../@key='Root']"
[2] "//@value[../@key='TM_PORT']"[/tt]
Appy them using selectSingleNode() method to return the attribute nodes and then use the getNodeValue() method to get their values. As to this part, this article may show you enough info to get started.
ref

http://www.packtpub.com/article/xpath-support-in-oracle-jdeveloper-xdk-11g

 

[sqleer]

Thank you for your response.

I am trying to follow this example using xmlnamespace


SQL> with t as (
2 select xmltype('<?xml version="1.0" encoding="ISO-8859-1"?>
3 <?xml-stylesheet type="text/xsl" href="../oper_fct.xsl"?>
4 <test abc:thisone="O1" xmlns="abc" xmlns:abc="abc"
5 xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance"

6 xsi:schemaLocation="abc ../abc_abc.xsd">
7 <blaa>
8 ABCABC
9 </blaa>
10 </test>') x from dual)
11 select extractvalue(x,'//@abc:thisone','xmlns:abc="abc"') attr
12 from t
13 ;

ATTR
----------
O1

im my case, I am trying to extract the value of TM_PORT.
the call for the EXTRACTVALUE is such:

EXTRACTVALUE
(XMLType_instance, XPath_string
[, namespace_string ]
)



..but i guess i a missing something in the query synrtax below.

SQL> SELECT EXTRACTVALUE(data,'//@value','entry key="TM_PORT"')attr
2 from tm_data
3 /
from tm_data
*
ERROR at line 2:
ORA-31013: Invalid XPATH expression

 

[tsuji]

>'//@value','entry key="TM_PORT"'
That is not the xpath I wrote. I don't recognize it.

 

[sqleer]

..using your xpath, and following the extracvalue syntax using xpath from this link:

http://www.adp-gmbh.ch/ora/sql/extractvalue.html

..i am still having issues..forgive my novice status..

SQL> SELECT EXTRACTVALUE(data,'"//@value[../@key='Root']"')attr
2 from tm_data
3 /
SELECT EXTRACTVALUE(data,'"//@value[../@key='Root']"')attr
*
ERROR at line 1:
ORA-00907: missing right parenthesis


SQL> SELECT EXTRACTVALUE(data,'"//@value[../@key='TM_PORT']"')attr
2 from tm_data
3 /
SELECT EXTRACTVALUE(data,'"//@value[../@key='TM_PORT']"')attr
*
ERROR at line 1:
ORA-00907: missing right parenthesis

 

[sqleer]

Thank you so much for your help.

This works

SQL> SELECT EXTRACTVALUE(data,'//@value[../@key="Root"]')attr
2 from tm_data
3 /

C:/Portware

 

[sqleer]

another question,

given:

<?xml version="1.0" encoding="UTF-8"?>
<java version="1.4.2_11" class="java.beans.XMLDecoder">
<object class="com.indigo.utils.properties.EnvironmentProperties"
<void property="basketserverMemory">
<string>384m</string>
</void>
<void property="clientMemory">
<string>256m</string>
</void>

<void property="dbHome">
<string>C:\Portware</string>
</void>
<void property="jbossMemory">
<string>256m</string>
</void>
<void property="trademonitorMemory">
<string>128m</string>

</void>
</object>
</java>

i am trying to get the value of the dbHome tag.

my current query returns the values for all the tags:
how do i isolate the value of dbHome?
thanks.

select xmltype(e.data).extract('//string/text()').getStringVal() as db_home
from config e

dh_home
--------------------------------------------------------------------------------
384m256mC:\Portware256m128m

 

[tsuji]

[3][tt] '/java/object/void[@property="dbHome"]/string'[/tt]

 

[sqleer]

Once again tsuji,

Thank you.

select extractvalue(xmltype(data),'/java/object/void[@property="dbHome"]/string') db_home
from config

DB_HOME
--------------------------------------------------------------------------------
C:\Portware

SQL>