?
Solved

XMLElement function not working as expected

Posted on 2007-11-21
4
Medium Priority
?
627 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
4 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 20330544
xmlelement returns xmltype's

each type contains the player node you're looking for

0
 
LVL 74

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 74

Accepted Solution

by:
sdstuber earned 500 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 74

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

765 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