extract table to xml file 1

[russland]

hi, i'd like to extracte a table and a few of it columns/attributes to a xml file.

any clue on how to use DBMS_XMLQUERY properly to do so?
thanks

 

[sem]

set pages 0
set long 100000
select xmlgen.getxml('select * from scott.emp'/*your query should be here*/) from dual

Regards, Dima

 

[russland]

hey, thanks for your willingnes to help.

i'm using oracle 8.1.7. correct me if i'm wrong but xmlgen is supported only by 9.x, isn't?

i first tried to output them on the comand line but it return 15++ for some reason. any idea?

cheers

here's the code
-------------------------
declare
queryCtx DBMS_XMLquery.ctxType;
result CLOB;
begin

-- set up the query context...!
queryCtx := DBMS_XMLQuery.newContext('select NAME from LOCATION');

-- get the result..!
result := DBMS_XMLQuery.getXML(queryCtx);
-- Now you can use the result to put it in tables/send as messages..
printClobOut(result);
DBMS_XMLQuery.closeContext(queryCtx); -- you must close the query handle..
end;
---------------------------


SQL> @c:\MakeLocXML.sql
15
16
17
18
19
20
21
22
23
24
25
26
27

 

[sem]

Correct you, you are wrong

I tried it on 8.1.7 right before posting

Regards, Dima

 

[russland]

hmmm... now i'm getting the error below. however, if this would work, how could i save the xmlgen to my harddrive?
thanks again.

---------------------
SQL> @c:\MakeLocXML3.sql
Input truncated to 63 characters
2
---------------------

my code is:
---------------------
set pages 0
set long 100000
select xmlgen.getxml('select NAME from LOCATION') from dual
---------------------

 

[sem]

You should end your code with semicolon and carriage return to let sql*plus know that you've finished entering command and it's time to execute it. In you script it should be

set pages 0
set long 100000
select xmlgen.getxml('select NAME from LOCATION') from dual;
--here is a blank line, don't forget




Regards, Dima

 

[russland]

sorry, for that. I didn't mean to waste your time with this typo.
wow, that did something. but i believe i need to install something to make this run. cannot find the oracleXMLStore. But again. where would it save the generated xml file? in the same directory?


-----------------------------------------
SQL> @c:\MakeLocXML3.sql
select xmlgen.getxml('select NAME from LOCATION;') from
*
ERROR at line 1:
ORA-29540: class OracleXMLStore does not exist
ORA-06512: at "ADMINUSER.XMLGEN", line 487
ORA-06512: at "ADMINUSER.XMLGEN", line 477
ORA-06512: at line 1
-----------------------------------------

 

[sem]

You should install java. In 8.1.6 you may just run initjvm.sql, but for 8.1.7 it's quite complex procedure (described somewhere at metalink), so it would be better to install it as a part of database installation. As for storing a file, the answer is NOWHERE. It just outputs the result to sql*plus screen. If you need to store it to a file, use SPOOL command. You may also need to add more commands to prevent plus from echoing commands and printing some other unuseful for your case information:

set feedback off
set serverout off
set termout off

Regards, Dima

 

[ddrillich]

Good Day,

The documentation about this package at

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96616/arxml30.htm#1009644

calls it DBMS_XMLGEN and as such it works for me: select dbms_xmlgen.getxml('select * from <my_table>') from dual;

Thanks Dima – this is a powerful package.

Regards,
Dan

 

[ddrillich]

russland,

The above page says: &quot;This package is similar to the DBMS_XMLQUERY package, except that it is written in C and compiled into the kernel. This package can only be run in the database.&quot;

Dan

 

[ddrillich]

Hi guys,

I guess I spoke too fast - the information that I found is valid for 9i.
Sorry about that.

Regards,
Dan

 

[russland]

hey guys,

that cannot be a hard one?!
thanks

_____________________________________
SQL> @c:\makeLocXML3.SQL
<?xml version = '1.0'?>
<ERROR>oracle.xml.sql.OracleXMLSQLException: ORA-00911: invalid character
</ERROR>
_____________________________________

my code:
_____________________________________
set pages 0
set long 100000
select xmlgen.getxml('select NAME from HC_LOCATION;') from dual;
_____________________________________

 

[sem]

Remove semicolon

Regards, Dima

 

[russland]

hey dima, is there any way i could thanks you?! i rephrased my statement so many times and now it works.

thanks for your time and support. now i only ned to export the clob to a file. i think i'll find that out rather quickly.

thanks again.

 

[sem]

You may spool it to file right from sql*plus.

BTW, where are you from? Russland is quite big...

Regards, Dima

 

[russland]

cheers. I'll check the documentation on spooler. thanks.

I'm not from russia at all. Still though I like it. my lovely country is more captialized but tremendousl tiny compared to russia (well, which country isn't so COMPARED to Russia)

best wishes

 

[NatPV]

Hi all,
I have a problem. I read your news on xml file generation.
I have tried to use xmlgen and dbms_xmlquery, but I haven't found a way to set more than one attribute for a node.
For example, I want to have:

<Contract IDContract = 'XXX' IDCustomer = 'YYY'>

But I don't know to to get this.
(Another problem I have is to write the <!DOCTYPE...> tag in the resulting xml file).
Any kind of suggestion on these two problems will be particularly appreciated.
Thank you in advance for any kind of help!

Natascia.