Help - Search - Members - Calendar
Full Version: XML output in SQL Plus
Oracle DBA Forums > Oracle > Oracle Forum
andy_9
Is there any way of getting a structurex XML ouptut in SQL plus? I'm just getting a unstructured output of it.

thanks!
HAL9000
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
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
"but want to have it displayed like in the Internet explorer you know? "

In SQL*Plus?

I don't understand your question.
andy_9
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
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
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
"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
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
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
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
>> 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?
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.