Solved

DBMS_XMLQUERY.getxml Error

Posted on 2004-09-10
5
752 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: 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

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…
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 explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

627 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