Home » Developer & Programmer » JDeveloper, Java & XML » How to query for xml atrribute? (Oracle 9i)
How to query for xml atrribute? [message #321093] Mon, 19 May 2008 01:23 Go to next message
ramora
Messages: 16
Registered: November 2006
Location: hyderabad
Junior Member
Hi experts,

Please help me.

my xml file look like below..

'<A A="A" T="256cff52" F="SECUF" U="admin" B="AID" C="panID" US="L_NAME" L="en" >
<T N="Table01">
<C A="D">
<C>Fid</C>
<O>AMACYR</O>
<N>null</N>
</C>
<C A="A">
<C>UID</C>
<O>null</O>
<N>256cff52</N>
</C>
</T>
</A>'

I need a query for retieve attributes of XML elements

Thanks in Advance.

Regards,
RAM.

[Updated on: Mon, 19 May 2008 03:19]

Report message to a moderator

Re: How to query for xml atrribute? [message #321160 is a reply to message #321093] Mon, 19 May 2008 05:07 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
If you want to extract values of specific attributes, just search it with @<attribute-name> in the XPath. An example:

SQL> DECLARE
  2    x          XMLTYPE :=
  3               xmlType('<A A="A" T="256cff52" F="SECUF" U="admin" B="AID" C="panID" US="L_NAME" L
="en" >
  4  <T N="Table01">
  5  <C A="D"> 
  6  <C>Fid</C>
  7  <O>AMACYR</O>
  8  <N>null</N>
  9  </C>
 10  <C A="A">
 11  <C>UID</C>
 12  <O>null</O>
 13  <N>256cff52</N> 
 14  </C>
 15  </T>
 16  </A>');
 17  BEGIN
 18    -- Extracting value of attribute "T" of node "A"
 19    DBMS_OUTPUT.PUT_LINE(x.Extract('/A/@T').getStringVal());
 20  END;
 21  /
256cff52

PL/SQL procedure successfully completed.

Alternatively, have a look at dbms_xmldom.getattributes.

Report message to a moderator

Re: How to query for xml atrribute? [message #321164 is a reply to message #321093] Mon, 19 May 2008 05:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Or simply:
SQL> select Extract(xmltype(
  2  '<A A="A" T="256cff52" F="SECUF" U="admin" B="AID" C="panID" US="L_NAME" L="en" >
  3  <T N="Table01">
  4  <C A="D"> 
  5  <C>Fid</C>
  6  <O>AMACYR</O>
  7  <N>null</N>
  8  </C>
  9  <C A="A">
 10  <C>UID</C>
 11  <O>null</O>
 12  <N>256cff52</N> 
 13  </C>
 14  </T>
 15  </A>' ),
 16  '/A/@T') val
 17  from dual;
VAL
----------------------------------------------------------------
256cff52

1 row selected.

Regards
Michel

Report message to a moderator

Re: How to query for xml atrribute? [message #321167 is a reply to message #321093] Mon, 19 May 2008 05:39 Go to previous messageGo to next message
ramora
Messages: 16
Registered: November 2006
Location: hyderabad
Junior Member
Thank you, my problem is solved with this solution.

Regards,
RAM.

Report message to a moderator

Re: How to query for xml atrribute? [message #321195 is a reply to message #321167] Mon, 19 May 2008 06:59 Go to previous messageGo to next message
ramora
Messages: 16
Registered: November 2006
Location: hyderabad
Junior Member
Hi,

I am facing a small problem with this query.

When I am trying to extract value of Attribute "A" of Node "C", the output is coming DA in single row.

1 select Extract(xmltype(
2 '<A A="A" T="256cff52" F="SECUF" U="admin" B="AID" C="panID" US="L_NAME" L="en" >
3 <T N="Table01">
4 <C A="D">
5 <C>Fid</C>
6 <O>AMACYR</O>
7 <N>null</N>
8 </C>
9 <C A="A">
10 <C>UID</C>
11 <O>null</O>
12 <N>256cff52</N>
13 </C>
14 </T>
15 </A>' ),
16 '/A/T/C/@A') val
17* from dual
SQL> /

VAL
--------------------------
DA

but in my requriment i need output in 2 rows like

VAL
--------------------------
D
A

Number of rows depends on by least node. In our example "C" is the least node.


Thanks in Advance.

Regards,
Ram.

Report message to a moderator

Re: How to query for xml atrribute? [message #321199 is a reply to message #321195] Mon, 19 May 2008 07:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
 
SQL> select extract(t.column_value, '*/@A') val
  2  from table(xmlsequence(extract(xmltype(
  3  '<A A="A" T="256cff52" F="SECUF" U="admin" B="AID" C="panID" US="L_NAME" L="en" >
  4  <T N="Table01">
  5  <C A="D">
  6  <C>Fid</C>
  7  <O>AMACYR</O>
  8  <N>null</N>
  9  </C>
 10  <C A="A">
 11  <C>UID</C>
 12  <O>null</O>
 13  <N>256cff52</N>
 14  </C>
 15  </T>
 16  </A>'),
 17  '/A/T/C'))) t
 18  /
VAL
----------------------------------------------------------------
D
A

2 rows selected.

Please read OraFAQ Forum Guide, "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: How to query for xml atrribute? [message #321371 is a reply to message #321093] Tue, 20 May 2008 01:35 Go to previous messageGo to next message
ramora
Messages: 16
Registered: November 2006
Location: hyderabad
Junior Member
Thanks michel,

with same above xml example, which is having 1 parent Node A with 2 Child Nodes T(above e.g. is only 1) and T is having 2 child nodes C.

I need output like below
1st Record - Atributes of A(A,256cff52..), Atribute of T(Table01),Attribute of C(D), elements of C
2nd Record - A,256cff52.., Table01,A, elements of C
3rd Record - A,256cff52.., Table02,E, elements of C
4th Record - A,256cff52.., Table02,F, elements of C
Can you help me please.


Regards,
Ram.

Report message to a moderator

Re: How to query for xml atrribute? [message #321719 is a reply to message #321371] Wed, 21 May 2008 02:29 Go to previous message
ramora
Messages: 16
Registered: November 2006
Location: hyderabad
Junior Member
Hi experts,

Please help me, Its urgent..
I am unable to get output, with below query

select extract(column_value,'//C/text()').getstringval() CVal,
extract(column_value,'//O/text()').getstringval() OVal
from table(xmlsequence(extract(xmltype(
'<A A="A" T="256cff52" F="SECUF" U="admin" B="AID" C="panID" US="L_NAME" L="en" >
<T N="Table01">
<C A="D">
<C>Fid</C>
<O>AMACYR</O>
<N>null</N>
</C>
<C A="A">
<C>UID</C>
<O>null</O>
<N>256cff52</N>
</C>
</T>
<T N="Table02">
<C A="E">
<C>Fid1</C>
<O>AMACYR1</O>
<N>null</N>
</C>
<C A="F">
<C>UID1</C>
<O>null</O>
<N>256cff52</N>
</C>
</T>
</A>'),
'//A/T')));

Att of A Att of A CVAL OVAL NVAL
-------- -------- --------------- --------------- ---------------
FidUID AMACYRnull null256cff52
Fid1UID1 AMACYR1null null256cff52


Here i am expecting 4 records with Atrributes of corresponding
child Nodes.But i am unable to pick attribute of Nodes and
i am getting only 2 records instead of 4records like below.

Att of A. Att of T CVAL OVAL NVAL
------------------------------------------
A Table01 Fid AMACYR null
A Table01 UID null 256cff52
A Table02 Fid1 AMACYR1 null
A Table02 UID1 null 256cff52

[Updated on: Wed, 21 May 2008 02:36]

Report message to a moderator

Previous Topic: About Creating Java Objects in Oracle
Next Topic: problem with reading XML attributes and elements
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 17 23:43:52 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.