Oracle DBA Forums > XML output in SQL Plus

andy_9

Jul 7 2008, 10:13 AM

Is there any way of getting a structurex XML ouptut in SQL plus? I'm just getting a unstructured output of it.

thanks!

HAL9000

Jul 7 2008, 12:41 PM

Andy,

"Is there any way of getting a structurex XML ouptut in SQL plus? "

Here is a good tutotial on how to publish output in XML:

http://www.dba-oracle.com/t_format_output_html_xml.htm

andy_9

Jul 7 2008, 01:07 PM

Hm....no, that's how to generate dynamic XML. I want to generate my own XML but want to have it displayed like in the Internet explorer you know? Everything structured rather than everything in a row.

HAL9000

Jul 7 2008, 01:46 PM

"but want to have it displayed like in the Internet explorer you know? "

In SQL*Plus?

I don't understand your question.

andy_9

Jul 7 2008, 02:48 PM

when you do the select dbms_xmlgen.getxml( thing you'll get a represenatation like it is in the IE

<emps>
<emp>Smith</emp>
<emp>Ward</emp>
</emps>

when you do "select xmlelement(" you'll get the following:
<emps><emp>Smith</emp><emp>Ward</emp></emps>

That's a big difference. I would choose the first option but I want to name my elements and attributs like I want. That's not possible with dbms_xmlgen.getxml as far as I can see....

Thanks

Steve

Jul 7 2008, 03:19 PM

QUOTE (andy_9 @ Jul 7 2008, 03:49 PM)

when you do the select dbms_xmlgen.getxml( thing you'll get a represenatation like it is in the IE

<emps>
<emp>Smith</emp>
<emp>Ward</emp>
</emps>

when you do "select xmlelement(" you'll get the following:
<emps><emp>Smith</emp><emp>Ward</emp></emps>

That's a big difference. I would choose the first option but I want to name my elements and attributs like I want. That's not possible with dbms_xmlgen.getxml as far as I can see....

Thanks

You can get much more advanced with DBMS_XMLGEN. Check out the docs here:

http://download-uk.oracle.com/docs/cd/B193...58/d_xmlgen.htm

Also note that using CURSORs and column name aliases you can name things better.

The link HAL9000 should help, as does this one: http://www.dba-oracle.com/t_dbms_xmlgen.htm

andy_9

Jul 8 2008, 08:57 AM

Ok, thanks. Is there a way to get the output of a xml variable (xmltype) in a anonymous block? I've tried dbms_output.put_line(variable):

It doesn't work because of the error "Wrong number or type of the..."

Think xml is not a valid type for dbm_output.put_line. Is there a work around for that problem?

HAL9000

Jul 8 2008, 10:28 AM

"Is there a way to get the output of a xml variable (xmltype) in a anonymous block?"

You could pipe it to a different data file with utl_file, would that work for you?

Steve

Jul 9 2008, 08:12 AM

Check out the docs, there's a lot you can do to return the output as a temporary CLOB. You can also use GETXMLTYPE to return to an XMLTYPE column.

http://www.stanford.edu/dept/itss/docs/ora...gen.htm#1013207

Laurent Schneider

Jul 10 2008, 07:18 AM

xmlserialize

CODE

SCOTT@lsc01> select xmltype('<x><y><z>123</z></y></x>') from dual;
XMLTYPE('<X><Y><Z>123</Z></Y></X>')
-----------------------------------------------------------------------------------------------
<x><y><z>123</z></y></x>

SCOTT@lsc01> select xmlserialize(document xmltype('<x><y><z>123</z></y></x>')) from dual;
XMLSERIALIZE(DOCUMENTXMLTYPE('<X><Y><Z>123</Z></Y></X>'))
-----------------------------------------------------------------------------------------------
<x>
  <y>
    <z>123</z>
  </y>
</x>

andy_9

Jul 28 2008, 03:01 PM

is that working with a function? tried to implement the function but can't get it with
select xmlserialize(document xmltype(func_myfunction)) from dual;

QUOTE (Laurent Schneider @ Jul 10 2008, 12:19 PM)

xmlserialize

CODE

SCOTT@lsc01> select xmltype('<x><y><z>123</z></y></x>') from dual;
XMLTYPE('<X><Y><Z>123</Z></Y></X>')
-----------------------------------------------------------------------------------------------
<x><y><z>123</z></y></x>

SCOTT@lsc01> select xmlserialize(document xmltype('<x><y><z>123</z></y></x>')) from dual;
XMLSERIALIZE(DOCUMENTXMLTYPE('<X><Y><Z>123</Z></Y></X>'))
-----------------------------------------------------------------------------------------------
<x>
  <y>
    <z>123</z>
  </y>
</x>

burleson

Jul 28 2008, 08:56 PM

>> tried to implement the function but can't get it with

Laurent (an Oracle ACE) wrote you a working sample, and now you want him to customize it for you?