Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DBMS_XMLQUERY.getxml Error

Posted on 2004-09-10
5
Medium Priority
?
768 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 700 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

Independent Software Vendors: 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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

876 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