| data extraction from xmltype [message #664910] |
Fri, 11 August 2017 00:45  |
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 #664918 is a reply to message #664914] |
Fri, 11 August 2017 03:50  |
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 |
 |
Michel Cadot
Messages: 68683
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
