Home » Developer & Programmer » JDeveloper, Java & XML » data extraction from xmltype (Oracle 11g)
data extraction from xmltype [message #664910] Fri, 11 August 2017 00:45 Go to next message
Rengaprabhu
Messages: 18
Registered: August 2002
Junior Member
Hi Friends,

I am trying to extract data from xmltype , i managed to extract for normal text attributes.
There is one set of data with Table model, the path i am specifying is wrong. Pls correct me.

<table>
<name>mainSuppliersTable</name>
<row>
<text>
<name>suppliersname</name>
<value>kjbsdfhb</value>
</text>
<text>
<name>supplierslocation</name>
<value>bdbvjsdb</value>
</text>
</row>
<row>
<text>
<name>suppliersname</name>
<value>bvjhfvh</value>
</text>
<text>
<name>supplierslocation</name>
<value>bnjfdbjd</value>
</text>
</row>
</table>

My code which is not working.

select a.form_nbr, x.*
from sample a,
xmltable ('/form/table/name'
PASSING a.data_xml
columns
a varchar2(4000) PATH '/row/text/name',
b varchar2(4000) path './row/value'
) x
where a.form_nbr='2017491'
and a ='suppliersname';
Re: data extraction from xmltype [message #664913 is a reply to message #664910] Fri, 11 August 2017 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.


Re: data extraction from xmltype [message #664914 is a reply to message #664913] Fri, 11 August 2017 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also don't forget to feedback when you get answers or if you get solution by yourself or elsewhere.

[Updated on: Fri, 11 August 2017 01:25]

Report message to a moderator

Re: data extraction from xmltype [message #664918 is a reply to message #664914] Fri, 11 August 2017 03:50 Go to previous messageGo to next message
Rengaprabhu
Messages: 18
Registered: August 2002
Junior Member
Noted Michel.

Found solution for the below issue. But results fetch too many rows.
select a.form_nbr, x.*
from sample a,
xmltable ('/form/table/row/text'
PASSING a.data_xml
columns
a varchar2(4000) PATH './name',
b varchar2(4000) path './value'
) x
where a.form_nbr='2017491'
and a ='suppliersname';
Re: data extraction from xmltype [message #664935 is a reply to message #664918] Fri, 11 August 2017 10:20 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 11 August 2017 08:22

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
I no more answer to people who refuse to follow the rules and do not post what is asked or not answer our questions.


Previous Topic: unload the data from the Oracle database into xml, using java.
Next Topic: Oracle11g
Goto Forum:
  


Current Time: Thu Mar 28 09:14:32 CDT 2024