asked on

DBMS_XMLQUERY.getxml Error

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.

grim_toaster

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!

L1Alpha01

ASKER

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....

grim_toaster

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!

L1Alpha01

ASKER

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

ASKER CERTIFIED SOLUTION

grim_toaster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial