| empty result [message #334917] |
Fri, 18 July 2008 11:22  |
flamingo_2008
Messages: 3
Registered: July 2008
|
Junior Member |
|
|
Hello,
I am trying to query from XML Type table. If there's some value, then I get result. However, if a sub table doesn't have any value (or null value), then it gives me empty result, although the main table has value. For example, if children_names_table is null, it just gives me empty value.
I have tried other suggestions from http://forums.oracle.com/forums/thread.jspa?messageID=2425891
then I get something like "incorrect number of result columns" error. Thank you so much for any help. I really appreciate it.
Here are the codes:
SELECT EXTRACT(A.person_xml, '/') as person_xml_col,
A.person_xml.EXTRACT('/Person/Age') as person_age,
A.person_xml.EXTRACT('/Person/Name') as person_name,
extractValue(value(children_names_table), '/Value') children_name_list
from benefit_xml A,
table(XMLSequence(extract(person_xml,'/Person/Children/Names'))) children_names_table
WHERE extract(A.SSN,'/Person/SSN') = '1234567890'
|
|
|
|
|
|
| Re: empty result [message #334920 is a reply to message #334917] |
Fri, 18 July 2008 11:39  |
flamingo_2008
Messages: 3
Registered: July 2008
|
Junior Member |
|
|
Sorry about that. Here are the codes:
SELECT EXTRACT(A.person_xml, '/') as person_xml_col,
A.person_xml.EXTRACT('/Person/Age') as person_age,
A.person_xml.EXTRACT('/Person/Name') as person_name,
extractValue(value(children_names_table), '/Value') children_name_list
FROM benefit_xml A,
table(XMLSequence(extract(person_xml,'/Person/Children/Names'))) children_names_table
WHERE extract(A.SSN,'/Person/SSN') = '1234567890'
|
|
|
|
|
|
| Re: empty result [message #334928 is a reply to message #334917] |
Fri, 18 July 2008 12:51 |
flamingo_2008
Messages: 3
Registered: July 2008
|
Junior Member |
|
|
since I got empty result when children is null although I still want to see age and name. I tried this as suggestion from the other link.
WITH table1 as (SELECT XMLType ('<Person><SSN>1234567890</SSN><Age>50</Age><Name>John Smith</Name></Person>') benefit_xml
FROM dual
UNION ALL
select 1, null from dual
)
SELECT t.person_xml.EXTRACT('/Person/Age') as person_age,
t.person_xml.EXTRACT('/Person/Name') as person_name
from table1 t,
(select extractValue(value (children_names_table), '/Value') children_name_list
from table(XMLSequence(extract(person_xml,'/Person/Children/Names'))) children_names_table)
where extract(t.benefit_xml, '/Person/SSN') = '1234567890'
then, I have this error:
ORA-01789:query block has incorrect number of result columns
01789.0000 - "query block has incorrect number of result columns"
Thanks so much for any help. Really appreciate it.
|
|
|
|
| Re: empty result [message #334929 is a reply to message #334928] |
Fri, 18 July 2008 13:01 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Have a look at your query in "with" clause. First part of "union all" has ONE column of xmltype type, second part has TWO columns of number and string types, so the error.
Please use SQL Formatter to correctly format your query.
Regards
Michel
[Updated on: Fri, 18 July 2008 13:03] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
