Solved

DBMS_XMLQUERY.getxml Error

Posted on 2004-09-10
5
742 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
ID: 12027391
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
ID: 12027945
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
ID: 12028252
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
ID: 12029634
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
ID: 12041811
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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle DB monitor SW 21 60
create a nested synonym 4 28
Fill Date time Field 12 25
subtr returning incorrect value 8 32
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

831 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