Home » SQL & PL/SQL » SQL & PL/SQL » SYS.XMLTYPE AND OUTPUT
SYS.XMLTYPE AND OUTPUT [message #208017] Thu, 07 December 2006 18:31 Go to next message
KANTPLAYALIK
Messages: 2
Registered: December 2006
Junior Member
OK, I THINK THIS IS THE RIGHT FORUM FOR THIS LOL Smile
I'M SO NEW TO ORACLE PROGRAMMING I HAVEN'T EVEN HEARD OF IT YET (HOPE THAT GIVES YOU GUYS SOME IDEA OF WHAT YOU ARE DEALING WITH HERE Smile).

IM USING SQL DEVELOPER AND ORACLE 10G EXPRESS

IS THERE A WAY TO VIEW THE XML OUTPUT FROM A QUERY LIKE :
----------------------------------------------------------
SELECT
XMLELEMENT(name "FAMILY_MEMBERS",
XMLELEMENT(name "FIRST_NAME",fl.FIRST_NAME),
XMLELEMENT(name "LAST_NAME",fl.LAST_NAME))
FROM FIRST_LAST fl;
----------------------------------------------------------

NOW I'D EXPECT TO SEE SOMETHING LIKE...

<FAMILY_MEMBERS>
<FIRST_NAME>JOHN</FIRST_NAME>
<LAST_NAME>DOE</LAST_NAME>
</FAMILY_MEMBERS>

ASSUMING OF COURSE THAT I ONLY HAD ONE RECORD IN THE TABLE.

BUT IN THE RESULTS WINDOW ALL I SEE IS

SYS.XMLTYPE 1 ROW SELECTED

I KNOW THIS IS TOTALLY NOOBISH SO I APOLOGIZE N ADVANCE.

THANKS FOR COMMENTS/SUGGESTION.
Re: SYS.XMLTYPE AND OUTPUT [message #208068 is a reply to message #208017] Fri, 08 December 2006 00:41 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You receive indeed a XMLTYPE from your query. That is, in fact, a CLOB subtype. In SQL*Plus you'll see the output as desired (you need to SET the LONG environmental variable higher to see it all) but in SQL Developer the result is obfuscated.

H:\>sqlplus hr/hr@nt920

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Dec 8 07:35:01 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production

SQL>
SQL> SELECT
  2  XMLAGG(XMLELEMENT(name "FAMILY_MEMBERS",
  3  XMLELEMENT(name "FIRST_NAME",fl.FIRST_NAME),
  4  XMLELEMENT(name "LAST_NAME",fl.LAST_NAME)))
  5  FROM employees fl
  6  WHERE rownum = 1
  7  /

XMLAGG(XMLELEMENT(NAME"FAMILY_MEMBERS",XMLELEMENT(NAME"FIRST_NAME",FL.FIRST_NAME

--------------------------------------------------------------------------------

<FAMILY_MEMBERS>
  <FIRST_NAME>Ellen</FIRST_NAME>
  <LAST_NAME>Abel</LAST_NAME>


SQL> sho long
long 80
SQL> set long 1000
SQL> /

XMLAGG(XMLELEMENT(NAME"FAMILY_MEMBERS",XMLELEMENT(NAME"FIRST_NAME",FL.FIRST_NAME

--------------------------------------------------------------------------------

<FAMILY_MEMBERS>
  <FIRST_NAME>Ellen</FIRST_NAME>
  <LAST_NAME>Abel</LAST_NAME>
</FAMILY_MEMBERS>


SQL>

You could use CAST() to convert the XMLTYPE to VARCHAR2:
SELECT CAST(xmlelement(name "FAMILY_MEMBERS"
                      ,   xmlelement(name "FIRST_NAME",   fl.first_name)
                      ,   xmlelement(name "LAST_NAME",   fl.last_name)
                      ) 
            AS VARCHAR2(2000))
FROM employees fl
WHERE rownum = 1
/
Note that the maximum length is limited to 2000 characters here.

I'd go for SQL*plus though. Perhaps someone else with SQL Developer experience?

MHE
Re: SYS.XMLTYPE AND OUTPUT [message #208085 is a reply to message #208068] Fri, 08 December 2006 02:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can also use the XMLTYPE methods to get data out of an Xmltype variable:
SELECT xmltype.getstringval(my_xml) 
FROM  (SELECT
XMLELEMENT(name "FAMILY_MEMBERS",
XMLELEMENT(name "FIRST_NAME",fl.FIRST_NAME),
XMLELEMENT(name "LAST_NAME",fl.LAST_NAME)) my_xml
FROM FIRST_LAST fl); 
Re: SYS.XMLTYPE AND OUTPUT [message #208253 is a reply to message #208017] Fri, 08 December 2006 17:27 Go to previous message
KANTPLAYALIK
Messages: 2
Registered: December 2006
Junior Member
Thanks So Much Guys, This is exactly what I was looking for.

Laughing
Previous Topic: SAVING/LOADING DATA TO/FROM MXL
Next Topic: Problem in craeting trigger
Goto Forum:
  


Current Time: Sun Dec 04 08:38:34 CST 2016

Total time taken to generate the page: 0.08658 seconds