Solved

XMLElement function not working as expected

Posted on 2007-11-21
4
614 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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.

746 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

9 Experts available now in Live!

Get 1:1 Help Now