Home » Developer & Programmer » JDeveloper, Java & XML » extract data
extract data [message #289458] Fri, 21 December 2007 18:18 Go to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Hi,
There is an XMLTYPE column (XML_A) in TABLE T1.

Below is the content of one column
<?xml version="1.0" encoding="UTF-8"?>
<Userchoice>
	<choice>
	<Attribute>
		<ValTx>1111</ValTx>
		<ApplicationCd/>
		<NameTx>USER_ID</NameTx>
	</Attribute>
	</choice>
	<choice>
	<Attribute>
		<ValTx>01/01/01 1:54 PM</ValTx>
		<ApplicationCd/>
		<NameTx>REJECT_DATE</NameTx>
	</Attribute>
	</choice>
</Userchoice>


There are two attributes ValTx and NameTx.

I need to display the ValTx for NameTx = 'USER_ID' using sql....

output should be


XML_A_VAL_TX
--------------
1111


Let me know if anyone has the solution.

Regrads,
Nav

[Updated on: Fri, 21 December 2007 23:28]

Report message to a moderator

Re: extract data [message #289482 is a reply to message #289458] Sat, 22 December 2007 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, the result we want is in "Attribute" subtree, so we have to extract it:
SQL> select extract(xml_a,'/Userchoice/choice/Attribute') a from t1;
A
----------------------------------------------------------------------------------------------------
<Attribute><ValTx>1111</ValTx><ApplicationCd/><NameTx>USER_ID</NameTx></Attribute><Attribute><ValTx>
01/01/01 1:54 PM</ValTx><ApplicationCd/><NameTx>REJECT_DATE</NameTx></Attribute>

1 row selected.

Now, there are 2 "Attribute" parts, so we have to separate them:
SQL> select xmlsequence(extract(xml_a,'/Userchoice/choice/Attribute')) a from t1;
A
----------------------------------------------------------------------------------------------------
XMLSEQUENCETYPE(XMLTYPE(<Attribute><ValTx>1111</ValTx><ApplicationCd/><NameTx>USER_ID</NameTx></Attr
ibute>), XMLTYPE(<Attribute><ValTx>01/01/01 1:54 PM</ValTx><ApplicationCd/><NameTx>REJECT_DATE</Name
Tx></Attribute>))

1 row selected.

There, we have 2 xml trees starting from "Attribute", we need "ValTx" and "NameTx" parts and values so we convert this "xmlsequence" into SQL table and extract the values:
SQL> col v format a20
SQL> col n format a20
SQL> select extractvalue(value(a),'/Attribute/ValTx') v,
  2         extractvalue(value(a),'/Attribute/NameTx') n
  3  from t1, table(xmlsequence(extract(xml_a,'/Userchoice/choice/Attribute'))) a;
V                    N
-------------------- --------------------
1111                 USER_ID
01/01/01 1:54 PM     REJECT_DATE

2 rows selected.

It is now easy to only keep the one we want:
SQL> select extractvalue(value(a),'/Attribute/ValTx') v
  2  from t1, table(xmlsequence(extract(xml_a,'/Userchoice/choice/Attribute'))) a
  3  where extractvalue(value(a),'/Attribute/NameTx') = 'USER_ID';
V
--------------------
1111

1 row selected.

Regards
Michel
Re: extract data [message #289487 is a reply to message #289458] Sat, 22 December 2007 06:01 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Or with the use of XPath, simply:
SELECT EXTRACTVALUE
        (xml_a,
         '/Userchoice/choice/Attribute[NameTx = "USER_ID"]/ValTx'
        ) v
FROM   t1;
Re: extract data [message #289490 is a reply to message #289487] Sat, 22 December 2007 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Indeed.
SQL> select extractvalue(xml_a,'/Userchoice/choice/Attribute[NameTx="USER_ID"]/ValTx') v
  2  from t1;
V
--------------------
1111

1 row selected.

I'm far from a specialist in XML. Sad

Regards
Michel
Re: extract data [message #289520 is a reply to message #289458] Sun, 23 December 2007 06:41 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also use XML query to get the result:
SQL> select xmlquery (
  2  'for $i in ora:view("t1")/ROW/*/Userchoice/choice/Attribute
  3  where $i/NameTx = "USER_ID"
  4  return xs:string($i/ValTx)'
  5  returning content) v
  6  from dual
  7  /
V
-----------------------------------------------------------------
1111

1 row selected.

Or using the new xquery SQL*Plus feature:
SQL> xquery 
  2  for $i in ora:view("t1")/ROW/*/Userchoice/choice/Attribute
  3  where $i/NameTx = 'USER_ID'
  4  return $i/ValTx/text()
  5  /
RESULT_PLUS_XQUERY
-----------------------------------------------------------------
1111

1 item(s) selected.

Beware, this feature has several bugs in 10gR2 and xquery transformation to XML query may be wrong.

Regards
Michel

Previous Topic: java.sql.SQLException: Io exception: Undefined Error
Next Topic: Registering XML schema in XE 10g
Goto Forum:
  


Current Time: Fri Mar 29 07:37:56 CDT 2024