OraFAQ Forum: Application Express, ORDS & MOD

Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Export XML (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Export XML [message #667391]

Tue, 26 December 2017 06:43

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?

Report message to a moderator

Re: Export XML [message #667393 is a reply to message #667391]

Tue, 26 December 2017 11:04

Littlefoot
Messages: 21823
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>

Report message to a moderator

Re: Export XML [message #667457 is a reply to message #667391]

Sat, 30 December 2017 15:22

Barbara Boehmer
Messages: 9100
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.

Report message to a moderator

Re: Export XML [message #667458 is a reply to message #667457]

Sat, 30 December 2017 17:13

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

Report message to a moderator

Re: Export XML [message #667459 is a reply to message #667458]

Sat, 30 December 2017 20:20

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"),

Report message to a moderator

Re: Export XML [message #667460 is a reply to message #667458]

Sat, 30 December 2017 21:25

Barbara Boehmer
Messages: 9100
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;

Report message to a moderator

Re: Export XML [message #667467 is a reply to message #667460]

Mon, 01 January 2018 03:38

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

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

Why is the xml file to SELECT?

What does that mean?

Report message to a moderator

Re: Export XML [message #667473 is a reply to message #667467]

Mon, 01 January 2018 06:03

Barbara Boehmer
Messages: 9100
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.

Report message to a moderator

Previous Topic:	persian calendar
Next Topic:	Running ORDS standalone

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 04 12:53:09 CST 2024