Home » Developer & Programmer » JDeveloper, Java & XML » empty result (Oracle db xml type)
empty result [message #334917] Fri, 18 July 2008 11:22 Go to next message
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 #334918 is a reply to message #334917] Fri, 18 July 2008 11:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: empty result [message #334920 is a reply to message #334917] Fri, 18 July 2008 11:39 Go to previous messageGo to next message
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 #334923 is a reply to message #334920] Fri, 18 July 2008 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I get something like "incorrect number of result columns" error

What about copying and pasting what you did instead of describing it? Then we can see what happens.

Also posting a test case (create table and insert statements) will greatly help to get an answer as well as posting your version (4 decimals).

Regards
Michel
Re: empty result [message #334928 is a reply to message #334917] Fri, 18 July 2008 12:51 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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

Previous Topic: Extracting table in XML in Oracle row to a flat table
Next Topic: SoapException in using webservices
Goto Forum:
  


Current Time: Thu Mar 28 18:26:12 CDT 2024