Home » Developer & Programmer » JDeveloper, Java & XML » How to SELECT * FROM XML Document
How to SELECT * FROM XML Document [message #419399] Fri, 21 August 2009 17:22 Go to next message
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.
Re: How to SELECT * FROM XML Document [message #419400 is a reply to message #419399] Fri, 21 August 2009 17:43 Go to previous messageGo to next message
ThomasG
Messages: 3189
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.
Re: How to SELECT * FROM XML Document [message #419402 is a reply to message #419400] Fri, 21 August 2009 18:51 Go to previous messageGo to next message
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.
Re: How to SELECT * FROM XML Document [message #419403 is a reply to message #419399] Fri, 21 August 2009 19:43 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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?
Re: How to SELECT * FROM XML Document [message #419416 is a reply to message #419403] Fri, 21 August 2009 23:46 Go to previous messageGo to next message
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.
Re: How to SELECT * FROM XML Document [message #419420 is a reply to message #419416] Sat, 22 August 2009 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
Re: How to SELECT * FROM XML Document [message #419458 is a reply to message #419420] Sat, 22 August 2009 12:50 Go to previous messageGo to next message
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.
Re: How to SELECT * FROM XML Document [message #419459 is a reply to message #419458] Sat, 22 August 2009 14:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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 Go to previous messageGo to next message
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.
Re: How to SELECT * FROM XML Document [message #419485 is a reply to message #419483] Sun, 23 August 2009 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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 Go to previous messageGo to next message
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:
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


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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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

Re: How to SELECT * FROM XML Document [message #419533 is a reply to message #419491] Mon, 24 August 2009 01:12 Go to previous messageGo to next message
Frank
Messages: 7880
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 *

Re: How to SELECT * FROM XML Document [message #419833 is a reply to message #419488] Tue, 25 August 2009 13:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: How to SELECT * FROM XML Document [message #419906 is a reply to message #419895] Wed, 26 August 2009 02:35 Go to previous message
Michel Cadot
Messages: 64139
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
Previous Topic: Varray in Java
Next Topic: is embedded sql faster than refcursor?
Goto Forum:
  


Current Time: Fri Dec 09 01:54:55 CST 2016

Total time taken to generate the page: 0.11997 seconds