OraFAQ Forum: JDeveloper, Java & XML » How to SELECT * FROM XML Document

Home » Developer & Programmer » JDeveloper, Java & XML » How to SELECT * FROM XML Document

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to SELECT * FROM XML Document [message #419399]

Fri, 21 August 2009 17:22

artmt
Messages: 32
Registered: October 2006
Location: Boston

Member

I have a simple xml document, but I don't know what elements it contains.
How do I SELECT * ?
I know there is a simple way but I forgot what it is, and surprisingly was not able to google it up.

Thanks.

Report message to a moderator

Re: How to SELECT * FROM XML Document [message #419400 is a reply to message #419399]

Fri, 21 August 2009 17:43

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Ah, the "I know it but don't remember the search terms" everybody runs into now and then Very Happy

It's the COLUMN_NAME.getClobVal() function you are after, most likely.

Report message to a moderator

Re: How to SELECT * FROM XML Document [message #419402 is a reply to message #419400]

Fri, 21 August 2009 18:51

artmt
Messages: 32
Registered: October 2006
Location: Boston

Member

Thanks, but that is not it.
I need to return not XML doc, but XML parsed as a regular data set with rows and columns. Extract or anything else that requires element names in the arguments will not work for me. I need to be able to SELECT * without knowing what elements the XML doc contains.

Report message to a moderator

Re: How to SELECT * FROM XML Document [message #419403 is a reply to message #419399]

Fri, 21 August 2009 19:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

We don't know what is your input data.
We don't know what you expect for results.

>I need to return not XML doc, but XML parsed as a regular data set with rows and columns.
How is column width determined?

Within the XML how do you discern the difference between strings, numbers, dates, & other datatypes?

Report message to a moderator

Re: How to SELECT * FROM XML Document [message #419416 is a reply to message #419403]

Fri, 21 August 2009 23:46

artmt
Messages: 32
Registered: October 2006
Location: Boston

Member

Here is more info.

I need to:

SELECT * 
FROM some_functions(xmltype('
    <XML>
      <ROW>
        <FNAME>John</FNAME>
        <LNAME>Smith</LNAME>
      </ROW>
      <ROW>
        <FNAME>Jane</FNAME>
        <LNAME>Smith</LNAME>
      </ROW>
    </XML>
    '))

I need the output to look like this:

FNAME   LNAME
John	Smith
Jane	Smith

It is essencial that I can do "SELECT *", not knowing what the element names are.
I know this is doable. I have done this before ... somehow ...
My vague recollection is that it involved a table() expression without explicitly defined table function, but I am not 100% sure.

Report message to a moderator

Re: How to SELECT * FROM XML Document [message #419420 is a reply to message #419416]

Sat, 22 August 2009 00:50

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> with 
  2    data as (
  3    select xmltype('<XML>
  4        <ROW>
  5          <FNAME>John</FNAME>
  6          <LNAME>Smith</LNAME>
  7        </ROW>
  8        <ROW>
  9          <FNAME>Jane</FNAME>
 10          <LNAME>Smith</LNAME>
 11        </ROW>
 12      </XML>
 13      ') val
 14    from dual
 15    )
 16  select extractvalue(value(t),'/ROW/FNAME') fname,
 17         extractvalue(value(t),'/ROW/LNAME') lname
 18  from data, 
 19       table(xmlsequence(extract(data.val, '/XML/ROW'))) t
 20  /
FNAME      LNAME
---------- ----------
John       Smith
Jane       Smith

2 rows selected.

There are many examples in "JDeveloper, Java & XML" forum.

Regards
Michel

Report message to a moderator

Re: How to SELECT * FROM XML Document [message #419458 is a reply to message #419420]

Sat, 22 August 2009 12:50

artmt
Messages: 32
Registered: October 2006
Location: Boston

Member

Thanks Michel, but this is not what I am looking for.
I have to be able to SELECT * without referencing any particlular data elements.

Report message to a moderator

Re: How to SELECT * FROM XML Document [message #419459 is a reply to message #419458]

Sat, 22 August 2009 14:04

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can't. SELECT * tells Oracle to search in its dictionary the list and type of columns. There is no Oracle dictionary here, it is YOUR data, you have to tell Oracle how they are built.

In addition, you should NEVER use SELECT *, it is very bad idea and way of programming.

Regards
Michel

[Updated on: Sat, 22 August 2009 14:06]

Report message to a moderator

Re: How to SELECT * FROM XML Document [message #419483 is a reply to message #419459]

Sat, 22 August 2009 22:01

artmt
Messages: 32
Registered: October 2006
Location: Boston

Member

Michel Cadot wrote on Sat, 22 August 2009 14:04

You can't. SELECT * tells Oracle to search in its dictionary the list and type of columns. There is no Oracle dictionary here, it is YOUR data, you have to tell Oracle how they are built.

But you can do SELECT * from a table function, even though its metadata is not in the dictionary. Can you not? I don't have access to a db at the moment to check, but I am pretty sure you can.

Quote:

In addition, you should NEVER use SELECT *, it is very bad idea and way of programming.

Regards
Michel

And in my experience ALWAYS/NEVER type beliefs are also responsible for quite a few poor solutions.

Report message to a moderator

Re: How to SELECT * FROM XML Document [message #419485 is a reply to message #419483]

Sun, 23 August 2009 00:28

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

But you can do SELECT * from a table function, even though its metadata is not in the dictionary

Wrong the metadata of a table function IS in the dictionary as you declare the returned type which definition is in the dictionary.

Quote:

And in my experience ALWAYS/NEVER type beliefs are also responsible for quite a few poor solutions.

It was a simplified way to say the thing but tell me an example you (have to) use it in a production environment and I tell you why it is (most likely) wrong.
I do no believe anything, my experience (more than 20 years) of application development, management and maintenance showed me you sooner or later be sorry for having coded a SELECT *.

Regards
Michel

[Updated on: Sun, 23 August 2009 00:54]

Report message to a moderator

Re: How to SELECT * FROM XML Document [message #419488 is a reply to message #419485]

Sun, 23 August 2009 02:01

artmt
Messages: 32
Registered: October 2006
Location: Boston

Member

Quote:

Wrong the metadata of a table function IS in the dictionary as you declare the returned type which definition is in the dictionary.

You are right. Conceptually, I understand why SELECT * should require dictionary metadata, but for some reason I think I have seen exceptions. Can you think of any? Or am I wrong about this?

Quote:

Off the top of my head here is one example. i came up with it in the context of ETL routines, but it is probably applicable in other contexts.

As a quality control measure you want to make sure all changes to data structures are reflected in the code wherever it matters. If you add a column to the source table you want to make sure the cange is reflected in the target, and the code does populate the new column instead of setting it to NULL. And if the changes were not cascaded you want existing code to break before it goes into production.

Do you see any problems with this approach?

[Updated on: Sun, 23 August 2009 02:07]

Report message to a moderator

Re: How to SELECT * FROM XML Document [message #419491 is a reply to message #419488]

Sun, 23 August 2009 04:14

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

As a quality control measure you want to make sure all changes to data structures are reflected in the code wherever it matters.

As a quality control measure it should be better that the code shoud be reviewed at each data structure change to be sure it will do what it is intended to do.
As we don't (should not) rely on implicit conversion or default values, we should not rely on automatic change in code.
What if you don't wnat the new column to be reflected in the target because it implies to change application in this one? How can you decide that ALL changes in source MUST BE a change in the target for ALL applications?

Regards
Michel

Report message to a moderator

Re: How to SELECT * FROM XML Document [message #419533 is a reply to message #419491]

Mon, 24 August 2009 01:12

Frank
Messages: 7901
Registered: March 2000

Senior Member

In complicated structured programs, it is quite valid to pass around parameters of <table>%rowtype.
To fill those, one needs a select *

Report message to a moderator

Re: How to SELECT * FROM XML Document [message #419833 is a reply to message #419488]

Tue, 25 August 2009 13:41

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Tom Kyte recently published a new Reason #13134213 Not to Use SELECT *

Regards
Michel

[Updated on: Tue, 25 August 2009 13:41]

Report message to a moderator

Re: How to SELECT * FROM XML Document [message #419895 is a reply to message #419833]

Wed, 26 August 2009 01:27

Frank
Messages: 7901
Registered: March 2000

Senior Member

Yep, I read it and I do not agree.
Since alteration of production code should be done at scheduled times only, I fail to see how the need to recompile a small percentage of your code due to such an alteration would be a problem.
Moreover, nowhere he claims that "you should NEVER use SELECT *, it is very bad idea and way of programming". I believe his is more a warning to reconsider using SELECT * all over the place, when you know up front you will only need a few columns.
The same goes for the "query indexed columns vs query table" argument: if you know you will be querying 80-90% of the columns in a table, it is safe to assume there is no index providing all these columns.
My whole point is that your statement is too bold and as such will not land.

Report message to a moderator

Re: How to SELECT * FROM XML Document [message #419906 is a reply to message #419895]

Wed, 26 August 2009 02:35

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I believe his is more a warning to reconsider using SELECT * all over the place

1/ I didn't say my sentence comes from Tom Kyte (he used to say "never says never" but...)
2/ My sentence was also to warn of the usage of "SELECT *". The first thought should be "do not use it" then "is it a very special case I can use it?"
3/ Tom many times gave reasons to not use SELECT * (hence the title of his article), this article is (a new) one over many.
4/ It is rare you have to use ALL columns in a procedure, it is rare you do not have to handle in your code a new column that you add to your table or a change in datatype... "SELECT *" and %ROWTYPE seems to automatically handle this but is the code still correct? isn't some implicit conversion that modifies the result? isn't/doesn't...?
The years I spent in development and with developers (and project managers) showed me that "compiler will automatically handle changes" quickly means "we have not to review the code if there is any structural change" which is wrong (I think you agree).

Regards
Michel

Report message to a moderator

Previous Topic:	Varray in Java
Next Topic:	is embedded sql faster than refcursor?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 23:53:43 CDT 2024