Solved

XMLElement function not working as expected

Posted on 2007-11-21
4
622 Views
Last Modified: 2011-09-20
I have a table called players with the following attributes and values:

 PLAYER_ID PLAYER_NAME
---------- ------------
         1 Smith
         2 Jones
         3 Wilson

When I issued this query,
select XMLElement("Player",
          XMLElement("PlayerID",player_id),
          XMLElement("PlayerName",player_name))
from players;

I expected to see results something like this

<Player>
  <PlayerID>1</PlayerID>
  <PlayerName>Smith</PlayerName>
</Player>
<Player>
  <PlayerID>2</PlayerID>
  <PlayerName>Jones</PlayerName>
</Player>
<Player>
  <PlayerID>3</PlayerID>
  <PlayerName>Wilson</PlayerName>
</Player>

but what actually returned was

XMLTYPE()
XMLTYPE()
XMLTYPE()

Is there something I need to do to get this to work?
0
Comment
Question by:awking00
  • 4
4 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 20330544
xmlelement returns xmltype's

each type contains the player node you're looking for

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 20330561
btw, off topic, please post your code about the iso weeks, I never did figure out what you were doing with the function based index that pertained to that question.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 125 total points
ID: 20330650
uset the getclobval() method of the returned xmltype's to see the contents

select XMLElement("Player",
          XMLElement("PlayerID",player_id),
          XMLElement("PlayerName",player_name)).getclobval()
from players;
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 20330661
And if you wanted all of the rows to be inside a single xml doc,
then use xmlagg to aggregate them.

and, that too returns an xmltype, so use getclobval on the xmlagg's returned value to see the combined xml


select XMLAGG(XMLElement("Player",
          XMLElement("PlayerID",player_id),
          XMLElement("PlayerName",player_name))
       ).getclobval()
from players;
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now