how to produce a xmlfile from a table?

[hedub]

I need to generate xml from a table. How do I do this, and how to I spool or make an output file e.g xml from an oracle table? So when I run lets say test.xml (which is generated by oracle) in a browser it will display the table with row and column name and data.

 

[51m0n]

select xmlgen.getXML('select * from test') from dual;

to display it on a browser just put the xml file on a webspace, or use mod pl/sql and apache to serve it directly off oracle (this second option implies a lot of work though).

you could try (untested code) to put this:

declare
lvare_xml varchar2(4000);
begin
lvar_xml := xmlgen.getXML('select * from test');
htp.p(lvar_xml);
end;

in a prc, configure your dad and call it froma browser. --
regards,
Simon

 

[hedub]

ok, this is new to me. When running "select xmlgen.getXML('select * from test') from dual;" in sqlplus I got a error saying xmlgen.getxml has invalid id. I just need a simple aproach that can produce the xmlfile with the columnnames, rownames and the attributdata.

I have tried using spool and then select xmlelement but the query comes always at top and it will not show in a browser. The data in the table is in the xmldocument but it will not show. It also makes a parsing error e.g in xmlspy.

 

[51m0n]

can you post the error xmlgen is telling you?
the simplest thing you can do to get xml out of a table is the query i gave you.
xmlgen.getXML is a function returning a clob or a varchar2 contanig the resulting row of the qury given as parameter.
it's easy just find out what's wrong --
regards,
Simon

 

[hedub]

ok.. my table is called SERIE. it contains a set of attributes with values taken from an another table. I have a trigger doing that, so basicly the SERIE table is for statistics. The error msg is:

select xmlgen.getXML('select * from SERIE') from dual
*
FEIL på linje 1:
ORA-00904: "XMLGEN"."GETXML": ugyldig ID

oracle is norwegian, but feil means error and ugyldig means invalid.

 

[hedub]

You say returning a clob or a varchar2 .., can it return Smallint? Cause the table contains only attributes with this datatype. Thanks for all help so far!

 

[carp]

I think the problem may have something to do with your installation; the following worked OK for me:

14:15:12 SQL> create table serie(x smallint, y smallint, z smallint);

Table created.

14:15:28 SQL> insert into serie values(1,2,3);

1 row created.

14:15:37 SQL> insert into serie values(4,5,6);

1 row created.

14:15:45 SQL> insert into serie values(7,8,9);

1 row created.

14:15:53 SQL> commit;

Commit complete.

14:15:55 SQL> select xmlgen.getXML('select * from SERIE') from dual;
clsXMLGEN.GETXML('SELECT*FROMSERIE')
--------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
<ROW num=&quot;1&quot;>
<X>1</X>
<Y>2</Y>
<Z>3</Z>
</ROW>
<ROW num=&quot;2&quot;>
<X>4</X>
<Y>5</Y>
<Z>6</Z>
</ROW>
<ROW num=&quot;3&quot;>
<X>7</X>
<Y>8</Y>
<Z>9</Z>
</ROW>
</ROWSET>

 

[hedub]

I figured it out. It wouldnt work because XSU wasnt installed and configured for my database. The xsu contains the xmlgen and getxml packages. Thanks for all help!

 

[hedub]

Ops.. one more thing. The output only displays the first row, carps example shows all 3 but the output that I get is:

SQL> select xmlgen.getXML('select * from test') from dual;

XMLGEN.GETXML('SELECT*FROMTEST')
--------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
<ROW num=&quot;1&quot;>
<X>1</X>
<Y>2</Y>


SQL>
SQL>
How can I make it display/output all the rows?

 

[51m0n]

try this:

set linesize 1000;
set pagesize 1000;
--
regards,
Simon

 

[sem]

I suppose that the size of output in this case may be change by old good

SET LONG <size> Regards, Dima

 

[hedub]

Thank you! But.. the formatting of the xmloutput is rather messy.. The output is seen like this in a browser:

Bodø/Glimt 1 3 1 3 Tromsø 1 2 1 3 Aalesund 0 0 0 0 Brann 0 0 0 0 Bryne 0 0 0 0 Lillestrøm 1 1 2 0 Lyn 0 0 0 0 Vålerenga 0 0 0 0 Viking 0 0 0 0 Molde 0 0 0 0 Odd Grenland 0 0 0 0 Rosenborg 0 0 0 0 Sogndal 1 1 3 0 Stabæk 0 0 0 0

I would like it to be:

rowname rowname rowname rowname
Bodø/Glimt 1 3 1 3
Tromsø 1 2 1 3
Aalesund 0 0 0 0
... on so on.

How can I set this up in the select-statement?

 

[51m0n]

> How can I set this up in the select-statement?

you can create an xsl file and generate xhtml from the xml and the xsl. read some xsl documentation. xsl is the xml stylesheet language.
--
regards,
Simon