Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Export XML (11g)
Export XML [message #667391] |
Tue, 26 December 2017 06:43 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/1d4863045ce1105b2a29570edbccf646?s=64&d=mm&r=g) |
marcinsgdz
Messages: 14 Registered: December 2017
|
Junior Member |
|
|
Hellow
COL KONTA FORMAT a20000;
spool /home/oracle/Desktop/PLIKI_XML/export6.xml;
select dbms_xmlquery.getxml('
SELECT XMLELEMENT ("USER",
XMLELEMENT ("IMIE", IMIE),
XMLELEMENT ("NAZWISKO", NAZWISKO),
XMLELEMENT ("DZIAL", DZIAL),
XMLELEMENT ("LOGIN", LOGIN),
XMLELEMENT ("TYP", TYP),
XMLELEMENT ("EMAIL", EMAIL),
XMLELEMENT ("DATA_NADANIA", DATA_NADANIA),
XMLELEMENT ("DATA_COFNIECIA", DATA_COFNIECIA)
) AS KONTA
FROM KONTA_AD JOIN UPOWAZNIENIA ON
(KONTA_AD.NR_UPOWAZNIENIA=UPOWAZNIENIA.NR_UPOWAZNIENIA)
')
from dual;
spool off
And it only receives this in the xml file.
<?xml version = '1.0'?>
<rowset> <row num="1"> <konta> <user>
In addition, I have in the file the content of the inquiry - how to get rid of the machine?
|
|
|
Re: Export XML [message #667393 is a reply to message #667391] |
Tue, 26 December 2017 11:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Is this question really related to Oracle Application Express? Doesn't seem so; unless you explain why it should be here, I'll move it into our (PL/)SQL or, even better, XML forum.
As of a "clean" spool file, the following set of SET commands (as suggested by Kevin, here) might be useful:
SET ECHO OFF
SET VERIFY OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET LINESIZE 9999
SET PAGESIZE 0
SET FEEDBACK OFF
SET TIMING OFF
SET TIME OFF
Additionally, if you want to get the whole XML value,
Finally, an example:
SQL> SET ECHO OFF
SQL> SET VERIFY OFF
SQL> SET TRIMSPOOL ON
SQL> SET TRIMOUT ON
SQL> SET LINESIZE 9999
SQL> SET PAGESIZE 0
SQL> SET FEEDBACK OFF
SQL> SET TIMING OFF
SQL> SET TIME OFF
SQL> SET LONG 10000
SQL>
SQL> select dbms_xmlgen.getxml
2 ('select r.region_name, c.country_name
3 from regions r join countries c
4 on r.region_id = c.region_id
5 where r.region_id = 2'
6 ) xml from dual;
<?xml version="1.0"?>
<ROWSET>
<ROW>
<REGION_NAME>Americas</REGION_NAME>
<COUNTRY_NAME>Argentina</COUNTRY_NAME>
</ROW>
<ROW>
<REGION_NAME>Americas</REGION_NAME>
<COUNTRY_NAME>Brazil</COUNTRY_NAME>
</ROW>
<ROW>
<REGION_NAME>Americas</REGION_NAME>
<COUNTRY_NAME>Canada</COUNTRY_NAME>
</ROW>
<ROW>
<REGION_NAME>Americas</REGION_NAME>
<COUNTRY_NAME>Mexico</COUNTRY_NAME>
</ROW>
<ROW>
<REGION_NAME>Americas</REGION_NAME>
<COUNTRY_NAME>United States of America</COUNTRY_NAME>
</ROW>
</ROWSET>
SQL>
|
|
|
|
Re: Export XML [message #667458 is a reply to message #667457] |
Sat, 30 December 2017 17:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/1d4863045ce1105b2a29570edbccf646?s=64&d=mm&r=g) |
marcinsgdz
Messages: 14 Registered: December 2017
|
Junior Member |
|
|
where 'DZIAL=IT'
SQL Error: ORA-00907: "missing right parenthesis"
set long 10000
spool /home/oracle/Desktop/PLIKI_XML/export6.xml;
select dbms_xmlquery.getxml('
SELECT XMLELEMENT ("USER",
XMLELEMENT ("IMIE", IMIE),
XMLELEMENT ("NAZWISKO", NAZWISKO),
XMLELEMENT ("DZIAL", DZIAL),
XMLELEMENT ("LOGIN", LOGIN),
XMLELEMENT ("TYP", TYP),
XMLELEMENT ("EMAIL", EMAIL),
XMLELEMENT ("DATA_NADANIA", DATA_NADANIA),
XMLELEMENT ("DATA_COFNIECIA", DATA_COFNIECIA)
) AS KONTA
FROM KONTA_AD JOIN UPOWAZNIENIA ON
(KONTA_AD.NR_UPOWAZNIENIA=UPOWAZNIENIA.NR_UPOWAZNIENIA)
where DZIAL='IT'
')
from dual;
spool off
|
|
|
|
Re: Export XML [message #667460 is a reply to message #667458] |
Sat, 30 December 2017 21:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You have to double the single quotes that are within the select statement that is surrounded by single quotes.
('SELECT ... where DZIAL=''IT''')
select dbms_xmlquery.getxml
('SELECT XMLELEMENT
("USER",
XMLELEMENT ("IMIE", IMIE),
XMLELEMENT ("NAZWISKO", NAZWISKO),
XMLELEMENT ("DZIAL", DZIAL),
XMLELEMENT ("LOGIN", LOGIN),
XMLELEMENT ("TYP", TYP),
XMLELEMENT ("EMAIL", EMAIL),
XMLELEMENT ("DATA_NADANIA", DATA_NADANIA),
XMLELEMENT ("DATA_COFNIECIA", DATA_COFNIECIA)) AS KONTA
FROM KONTA_AD JOIN UPOWAZNIENIA
ON (KONTA_AD.NR_UPOWAZNIENIA=UPOWAZNIENIA.NR_UPOWAZNIENIA)
where DZIAL=''IT''')
from dual;
|
|
|
Re: Export XML [message #667467 is a reply to message #667460] |
Mon, 01 January 2018 03:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/1d4863045ce1105b2a29570edbccf646?s=64&d=mm&r=g) |
marcinsgdz
Messages: 14 Registered: December 2017
|
Junior Member |
|
|
Hellow!
MY files XML :
select dbms_xmlquery.getxml
('SELECT XMLELEMENT
("USER",
XMLELEMENT ("IMIE", IMIE),
XMLELEMENT ("NAZWISKO", NAZWISKO),
XMLELEMENT ("DZIAL", DZIAL),
XMLELEMENT ("LOGIN", LOGIN),
XMLELEMENT ("TYP", TYP),
XMLELEMENT ("EMAIL", EMAIL),
XMLELEMENT ("DATA_NADANIA", DATA_NADANIA),
XMLELEMENT ("DATA_COFNIECIA", DATA_COFNIECIA)) AS KONTA
FROM KONTA_AD JOIN UPOWAZNIENIA
ON (KONTA_AD.NR_UPOWAZNIENIA=UPOWAZNIENIA.NR_UPOWAZNIENIA)
where DZIAL=''IT''')
from dual;
<?xml version = '1.0'?>
<ROWSET>
<KONTA>
<USER><IMIE>JOHN</IMIE><NAZWISKO>LENON</NAZWISKO><DZIAL>IT</DZIAL><LOGIN>jleon</LOGIN><TYP>UZYTKOWNIK DOMENY</TYP><EMAIL>jl@gg.com</EMAIL><DATA_NADANIA>2015-11-01</DATA_NADANIA><DATA_COFNIECIA>2015-11-30</DATA_COFNIECIA></USER>
...
Why is the xml file to SELECT?
[Updated on: Mon, 01 January 2018 03:38] Report message to a moderator
|
|
|
|
Re: Export XML [message #667473 is a reply to message #667467] |
Mon, 01 January 2018 06:03 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
marcinsgdz wrote on Mon, 01 January 2018 01:38...Why is the xml file to SELECT?
Your question is not clear. You have to use a query with SELECT to retrieve data from the database. Dbms_xmlquery.getxml requires a query using SELECT as a parameter. If you are asking why the query using SELECT is appearing in your spooled file, then you need to SET ECHO OFF as previously stated by Littlefoot. You will need to SET ECHO OFF either before running your query from a saved sql file or put the SET ECHO OFF at the top of the saved sql file. If you just run your query by typing or copying and pasting it into SQL*Plus instead of running a saved SQL file, the query using SELECT will still appear in the spooled file.
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:33:23 CDT 2024
|