Solved

DBMS_XMLQUERY.getxml Error

Posted on 2004-09-10
5
729 Views
Last Modified: 2012-05-05
All,

I have the following data in the machine column for the table V$SESSION:

MACHINE
---------------
SOMEMACHINE
SOMEMACHINE
SOMEMACHINE
SOMEMACHINE
SOMEMACHINE
SOMEMACHINE
ANOTHERMACHINE\SOMEUSER
SOMEMACHINE
SOMEMACHINE

I am trying to get the following statement to work correctly:

SELECT DBMS_XMLQUERY.GETXML('SELECT MACHINE FROM V$SESSION')
FROM DUAL;

The output I get when I execute the above statement (minus the dashed lines) looks like this:

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

<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <MACHINE>SOMEMACHINE</MACHINE>
   </ROW>
   <ROW num="2">
      <MACHINE>SOMEMACHINE</MACHINE>
   </ROW>
   <ROW num="3">
      <MACHINE>SOMEMACHINE</MACHINE>
   </ROW>
   <ROW num="4">
      <MACHINE>SOMEMACHINE</MACHINE>
   </ROW>
   <ROW num="5">
      <MACHINE>SOMEMACHINE</MACHINE>
   </ROW>
   <ROW num="6">
      <MACHINE>SOMEMACHINE</MACHINE>
   </ROW>
   <ROW num="7">
      <MACHINE>ANOTHERMACHINE\SOMEUSER

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

Note that the ending </MACHINE> tag is not present in row number seven of the XML output and that the final two entries for SOMEMACHINE are not present.

Why does the DBMS_XMLQUERY.getxml procedure stop once it reaches ANOTHERMACHINE\SOMEUSER?  Does anyone know of a work around?

Any help would be greatly appreciated.  Thanks.

0
Comment
Question by:L1Alpha01
  • 3
  • 2
5 Comments
 
LVL 7

Expert Comment

by:grim_toaster
Comment Utility
Are you running this from SQL*Plus, if so, then I believe that your problem is that the data is just getting truncated for display purposes (the correct values are created).  To check this at the SQL*Plus prompt type "show all", and see what the long setting is (mine defaulted to 80, and therefore showed even less than your example).

Simply type:
set long 20000

That should provide enough display space to show all of the generated xml for you!
0
 

Author Comment

by:L1Alpha01
Comment Utility
I wish that were the problem, however, I am running the statement from TOAD and viewing the XML output in its entirety using the popup editor function.  Any other thoughts....
0
 
LVL 7

Expert Comment

by:grim_toaster
Comment Utility
Sorry, but I really don't think that you can change that in TOAD (I can't find any reference to it anywhere! - and yes I get the same problem)...

So, the only real workaround that I can give you I'm afraid would be to execute it in SQL*Plus and spool it out to a file, and then use your favourite editor to view it!
0
 

Author Comment

by:L1Alpha01
Comment Utility
I think the problem is related to this statement:

SELECT MACHINE, LENGTH(MACHINE)
 FROM V$SESSION;

The output from the above statement (minus the dashed lines) is as follows:

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

SOMEMACHINE,11
SOMEMACHINE\SOMEUSER,21

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

Notice that there are only twenty characters for SOMEMACHINE\SOMEUSER not 21.  For some reason there is a chr(0) appended to the second row of the above query (I think the chr(0) is appended for NT machines only).  I think the DBMS_XMLQUERY.getxml call I listed originally is failing because chr(0) is a nonprintable character.  

Does anyone know a way around this (without changing the data in the database)?  Could someone give me an example?

Thanks



0
 
LVL 7

Accepted Solution

by:
grim_toaster earned 350 total points
Comment Utility
Wow, well spotted!

Anyways, to solve your problem then, simply use:

SELECT REPLACE(DBMS_XMLQUERY.GETXML('SELECT MACHINE FROM V$SESSION'), CHR(0), NULL)
FROM DUAL;
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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

744 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

19 Experts available now in Live!

Get 1:1 Help Now