OraFAQ Forum: JDeveloper, Java & XML

Home » Developer & Programmer » JDeveloper, Java & XML » extract data

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

extract data [message #289458]

Fri, 21 December 2007 18:18

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

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: extract data [message #289487 is a reply to message #289458]

Sat, 22 December 2007 06:01

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;

Report message to a moderator

Re: extract data [message #289490 is a reply to message #289487]

Sat, 22 December 2007 07:15

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: extract data [message #289520 is a reply to message #289458]

Sun, 23 December 2007 06:41

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 24 23:58:50 CST 2025