Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DBMS_XMLQUERY.getxml Error

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

715 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