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
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 messageGo to next message
Littlefoot
Messages: 21806
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,
SET LONG 10000

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 #667457 is a reply to message #667391] Sat, 30 December 2017 15:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
It looks like the problem is probably just the SET LONG value as Littlefoot mentioned. If you do not set it, then the default value is 80. Please see the demonstration below.

-- tables and data for testing:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE konta_ad
  2    (nr_upowaznienia  NUMBER)
  3  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO konta_ad VALUES (1)
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE upowaznienia
  2    (nr_upowaznienia  NUMBER)
  3  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO upowaznienia VALUES (1)
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> COMMIT
  2  /

Commit complete.

-- with SET LONG 80 (default value):
SCOTT@orcl_12.1.0.2.0> SET LONG 80
SCOTT@orcl_12.1.0.2.0> COL KONTA FORMAT a20000;
SCOTT@orcl_12.1.0.2.0> select dbms_xmlquery.getxml
  2  	      ('SELECT XMLELEMENT
  3  			 ("USER"
  4  			  ) AS KONTA
  5  		FROM   KONTA_AD JOIN UPOWAZNIENIA
  6  		ON     (KONTA_AD.NR_UPOWAZNIENIA=UPOWAZNIENIA.NR_UPOWAZNIENIA)')
  7  from   dual;

DBMS_XMLQUERY.GETXML('SELECTXMLELEMENT("USER")ASKONTAFROMKONTA_ADJOINUPOWAZNIENI
--------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <KONTA>
         <USER><


1 row selected.

-- with SET LONG 100000:
SCOTT@orcl_12.1.0.2.0> SET LONG 100000
SCOTT@orcl_12.1.0.2.0> select dbms_xmlquery.getxml
  2  	      ('SELECT XMLELEMENT
  3  			 ("USER"
  4  			  ) AS KONTA
  5  		FROM   KONTA_AD JOIN UPOWAZNIENIA
  6  		ON     (KONTA_AD.NR_UPOWAZNIENIA=UPOWAZNIENIA.NR_UPOWAZNIENIA)')
  7  from   dual;

DBMS_XMLQUERY.GETXML('SELECTXMLELEMENT("USER")ASKONTAFROMKONTA_ADJOINUPOWAZNIENI
--------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <KONTA>
         <USER></USER>
      </KONTA>
   </ROW>
</ROWSET>


1 row selected.

Re: Export XML [message #667458 is a reply to message #667457] Sat, 30 December 2017 17:13 Go to previous messageGo to next message
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 #667459 is a reply to message #667458] Sat, 30 December 2017 20:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SELECT XMLELEMENT ("USER",
it could work better after you replace line above with line below
SELECT XMLELEMENT ("USER"),
Re: Export XML [message #667460 is a reply to message #667458] Sat, 30 December 2017 21:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 messageGo to next message
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 #667472 is a reply to message #667467] Mon, 01 January 2018 05:47 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

Why is the xml file to SELECT?
What does that mean?
Re: Export XML [message #667473 is a reply to message #667467] Mon, 01 January 2018 06:03 Go to previous message
Barbara Boehmer
Messages: 9077
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.

Previous Topic: persian calendar
Next Topic: Running ORDS standalone
Goto Forum:
  


Current Time: Thu Mar 28 18:11:48 CDT 2024