Home » SQL & PL/SQL » Client Tools » generate xml (12.1.0.2.0)
generate xml [message #676473] Tue, 11 June 2019 11:50 Go to next message
ora9a
Messages: 26
Registered: June 2010
Junior Member
Hi, I have to generate an xml file from a query. I have done this code:


SELECT TO_CHAR(
        xmlroot(
            xmlelement("Students",
                xmlagg(
                    xmlelement("Student",
                        xmlconcat(
                            xmlelement ("StudentCode",StudentCode),
                            xmlelement ("GivenName",GivenName),
                            xmlelement ("Surname",Surname),
                            xmlelement ("OtherName",OtherName),
                            xmlelement ("Gender",Gender),
                            xmlelement ("DOB",DOB),
                            xmlelement ("Title",Title),
                            xmlelement ("DeceasedFlag",DeceasedFlag),
                            xmlelement ("Email",Email),
                            xmlelement ("PhoneHome",PhoneHome),
                            xmlelement ("PhoneMobile",PhoneMobile),
                            xmlelement ("FullTimePartTime",FullTimePartTime),
                            xmlelement ("ForeignDomesticCode",ForeignDomesticCode),
                            xmlelement ("StudentEnrolmentCode",StudentEnrolmentCode))))),
        VERSION '1.0')
        .GETCLOBVAL()) AS STUDENTS
FROM XMLTEST;


When I run this in Toad, I can see the results as :

<?xml version="1.0"?>
<Students>
<Student>
<StudentCode>@1234567</StudentCode>
<GivenName>Sharon</GivenName>
<Surname>Corbett</Surname>
<OtherName/>
<Gender>F</Gender>
<DOB>2019-06-11</DOB>
<Title>Mrs</Title>
<DeceasedFlag>N</DeceasedFlag>
<Email>sharon@mycompany.com</Email>
<PhoneHome>987654321</PhoneHome>
<PhoneMobile>123456789</PhoneMobile>
<FullTimePartTime>F</FullTimePartTime>
<ForeignDomesticCode>4</ForeignDomesticCode>
<StudentEnrolmentCode>RE</StudentEnrolmentCode>
</Student>
<Student>
<StudentCode>@9876543</StudentCode>
<GivenName>Toslima</GivenName>
<Surname>Begum</Surname>
<OtherName>Toslima</OtherName>
<Gender>F</Gender>
<DOB>2019-06-11</DOB>
<Title>Miss</Title>
<DeceasedFlag>N</DeceasedFlag>
<Email>toslima@mycompany.com</Email>
<PhoneHome>987654321</PhoneHome>
<PhoneMobile>123456789</PhoneMobile>
<FullTimePartTime>F</FullTimePartTime>
<ForeignDomesticCode>1</ForeignDomesticCode>
<StudentEnrolmentCode>RE</StudentEnrolmentCode>
</Student>
</Students>

However, when i save this from toad as xml file and then open the xml in notepad, the data is not the same:

<?xml version="1.0" ?>
<!DOCTYPE main [
<!ELEMENT main (DATA_RECORD*)>
<!ELEMENT DATA_RECORD (STUDENTS?)+>
<!ELEMENT STUDENTS (#PCDATA)>
]>
<main>
<DATA_RECORD>
<STUDENTS><?xml version="1.0"?>
<Students>
<Student>
<StudentCode>@1234567</StudentCode>
<GivenName>Sharon</GivenName>
<Surname>Corbett</Surname>
<OtherName/>
<Gender>F</Gender>
<DOB>2019-06-11</DOB>
<Title>Mrs</Title>
<DeceasedFlag>N</DeceasedFlag>
<Email>sharon@mycompany.com</Email>
<PhoneHome>987654321</PhoneHome>
<PhoneMobile>123456789</PhoneMobile>
<FullTimePartTime>F</FullTimePartTime>
<ForeignDomesticCode>4</ForeignDomesticCode>
<StudentEnrolmentCode>RE</StudentEnrolmentCode>
</Student>
<Student>
<StudentCode>@9876543</StudentCode>
<GivenName>Toslima</GivenName>
<Surname>Begum</Surname>
<OtherName>Toslima</OtherName>
<Gender>F</Gender>
<DOB>2019-06-11</DOB>
<Title>Miss</Title>
<DeceasedFlag>N</DeceasedFlag>
<Email>toslima@mycompany.com</Email>
<PhoneHome>987654321</PhoneHome>
<PhoneMobile>123456789</PhoneMobile>
<FullTimePartTime>F</FullTimePartTime>
<ForeignDomesticCode>1</ForeignDomesticCode>
<StudentEnrolmentCode>RE</StudentEnrolmentCode>
</Student>
</Students>
</STUDENTS>
</DATA_RECORD>
</main>

How can i generate the xml file in sqlplus? i have tried spooling but it generates a blank file.

Also, i need to have this in the start tag:
<?xml version="1.0" encoding="utf-8"?> <ImportTask xmlns="http://inplace.quantumit.com.au/import/version_1_0/" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

Many thanks

Re: generate xml [message #676474 is a reply to message #676473] Tue, 11 June 2019 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 66721
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As we have neither your table and data nor what you actually did in SQL*Plus, we can't help.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data.

Re: generate xml [message #676494 is a reply to message #676474] Wed, 12 June 2019 08:43 Go to previous messageGo to next message
ora9a
Messages: 26
Registered: June 2010
Junior Member
Hi Michel. I have made some progress. I can get the xml file, but just need to take out the query and headings in the spooled file.



 CREATE TABLE xmltest (
                             StudentCode VARCHAR2(50),
                             GivenName VARCHAR2(50),
                             Surname VARCHAR2(50),
                             OtherName VARCHAR2(50),
                             Gender VARCHAR2(1),
                             DOB DATE,
                             Title VARCHAR2(10),
                             DeceasedFlag VARCHAR2(1),
                             Email VARCHAR2(100),
                             PhoneHome NUMBER,
                             PhoneMobile NUMBER,
                             FullTimePartTime VARCHAR2(1),
                             ForeignDomesticCode VARCHAR2(1),
                             StudentEnrolmentCode VARCHAR2(2));
  
  
  INSERT INTO xmltest VALUES ('123456','Joe','Smith','John','M',sysdate,'Mr','N','joe@myemail.com',123456,987654,'F','D','RE');
  INSERT INTO xmltest VALUES ('998765','Sharon','Peters','Tracy','F',sysdate,'Mrs','N','sharon@myemail.com',123456,987654,'F','D','RE');
  
 spool c:\sql\xmltest

 SELECT TO_CHAR(
        xmlroot(
            xmlelement("Students",
                xmlagg(
                    xmlelement("Student",
                        xmlconcat(
                            xmlelement ("StudentCode",StudentCode),
                            xmlelement ("GivenName",GivenName),
                            xmlelement ("Surname",Surname),
                            xmlelement ("OtherName",OtherName),
                            xmlelement ("Gender",Gender),
                            xmlelement ("DOB",DOB),
                            xmlelement ("Title",Title),
                            xmlelement ("DeceasedFlag",DeceasedFlag),
                            xmlelement ("Email",Email),
                            xmlelement ("PhoneHome",PhoneHome),
                            xmlelement ("PhoneMobile",PhoneMobile),
                            xmlelement ("FullTimePartTime",FullTimePartTime),
                            xmlelement ("ForeignDomesticCode",ForeignDomesticCode),
                            xmlelement ("StudentEnrolmentCode",StudentEnrolmentCode))))),
        VERSION '1.0')
        .GETCLOBVAL()) AS STUDENTS
FROM XMLTEST;

spool off;


The spooled file shows as:
> SELECT TO_CHAR(
  2          xmlroot(
  3              xmlelement("Students",
  4                  xmlagg(
  5                      xmlelement("Student",
  6                          xmlconcat(
  7                              xmlelement ("StudentCode",StudentCode),
  8                              xmlelement ("GivenName",GivenName),
  9                              xmlelement ("Surname",Surname),
 10                              xmlelement ("OtherName",OtherName),
 11                              xmlelement ("Gender",Gender),
 12                              xmlelement ("DOB",DOB),
 13                              xmlelement ("Title",Title),
 14                              xmlelement ("DeceasedFlag",DeceasedFlag),
 15                              xmlelement ("Email",Email),
 16                              xmlelement ("PhoneHome",PhoneHome),
 17                              xmlelement ("PhoneMobile",PhoneMobile),
 18                              xmlelement ("FullTimePartTime",FullTimePartTime),
 19                              xmlelement ("ForeignDomesticCode",ForeignDomesticCode),
 20                              xmlelement ("StudentEnrolmentCode",StudentEnrolmentCode))))),
 21          VERSION '1.0')
 22          .GETCLOBVAL()) AS STUDENTS
 23  FROM XMLTEST;

STUDENTS                                                                        
--------------------------------------------------------------------------------
<?xml version="1.0"?>                                                           
<Students>                                                                      
  <Student>                                                                     
    <StudentCode>123456</StudentCode>                                           
    <GivenName>Joe</GivenName>                                                  
    <Surname>Smith</Surname>                                                    
    <OtherName>John</OtherName>                                                 
    <Gender>M</Gender>                                                          
    <DOB>2019-06-12</DOB>                                                       
    <Title>Mr</Title>                                                           
    <DeceasedFlag>N</DeceasedFlag>                                              

STUDENTS                                                                        
--------------------------------------------------------------------------------
    <Email>joe@myemail.com</Email>                                              
    <PhoneHome>123456</PhoneHome>                                               
    <PhoneMobile>987654</PhoneMobile>                                           
    <FullTimePartTime>F</FullTimePartTime>                                      
    <ForeignDomesticCode>D</ForeignDomesticCode>                                
    <StudentEnrolmentCode>RE</StudentEnrolmentCode>                             
  </Student>                                                                    
  <Student>                                                                     
    <StudentCode>998765</StudentCode>                                           
    <GivenName>Sharon</GivenName>                                               
    <Surname>Peters</Surname>                                                   

STUDENTS                                                                        
--------------------------------------------------------------------------------
    <OtherName>Tracy</OtherName>                                                
    <Gender>F</Gender>                                                          
    <DOB>2019-06-12</DOB>                                                       
    <Title>Mrs</Title>                                                          
    <DeceasedFlag>N</DeceasedFlag>                                              
    <Email>sharon@myemail.com</Email>                                           
    <PhoneHome>123456</PhoneHome>                                               
    <PhoneMobile>987654</PhoneMobile>                                           
    <FullTimePartTime>F</FullTimePartTime>                                      
    <ForeignDomesticCode>D</ForeignDomesticCode>                                
    <StudentEnrolmentCode>RE</StudentEnrolmentCode>                             

STUDENTS                                                                        
--------------------------------------------------------------------------------
  </Student>                                                                    
</Students>                                                                     
                                                                                

> spool off

Re: generate xml [message #676495 is a reply to message #676494] Wed, 12 June 2019 09:04 Go to previous messageGo to next message
_jum
Messages: 559
Registered: February 2008
Senior Member
For the header and the namespace compare:
WITH data (scode, sname) AS (
  SELECT '@1234567', 'Corbett' FROM dual UNION ALL
  SELECT '@9876543', 'Begum'   FROM dual
)
SELECT XMLSERIALIZE(
         DOCUMENT
           XMLELEMENT(
            "ImportTask " , xmlattributes (  'http://inplace.quantumit.com.au/import/version_1_0/' as "xmlns",     
                                             'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:i"
                                           )     ,
             XMLELEMENT("Students",                                           
                   XMLAGG(
                     XMLELEMENT(
                      "Student",
                         XMLELEMENT("StudentCode",scode),
                         XMLELEMENT("Surname",sname)))
               ))
       VERSION '1.0" encoding="UTF-8"'
       INDENT SIZE = 2)
  FROM data;


X                                                                               
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8""?>                                         
<ImportTask xmlns="http://inplace.quantumit.com.au/import/version_1_0/" xmlns:i=
"http://www.w3.org/2001/XMLSchema-instance">                                    
  <Students>                                                                    
    <Student>                                                                   
      <StudentCode>@1234567</StudentCode>                                       
      <Surname>Corbett</Surname>                                                
    </Student>                                                                  
    <Student>                                                                   
      <StudentCode>@9876543</StudentCode>                                       
      <Surname>Begum</Surname>                                                  
    </Student>                                                                  
  </Students>                                                                   
</ImportTask>                                                                   
Re: generate xml [message #676496 is a reply to message #676495] Wed, 12 June 2019 09:43 Go to previous messageGo to next message
ora9a
Messages: 26
Registered: June 2010
Junior Member
Hi,

Thanks that works! The only thing left is to remove the sql prompts from the spool file.

Thanks
Re: generate xml [message #676498 is a reply to message #676496] Wed, 12 June 2019 10:21 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
ora9a wrote on Wed, 12 June 2019 07:43
Hi,

Thanks that works! The only thing left is to remove the sql prompts from the spool file.

Thanks
try below

sqlplus -s
Re: generate xml [message #676501 is a reply to message #676496] Wed, 12 June 2019 12:06 Go to previous message
Michel Cadot
Messages: 66721
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ora9a wrote on Wed, 12 June 2019 16:43
Hi,

Thanks that works! The only thing left is to remove the sql prompts from the spool file.

Thanks
set pagesize 0 feedback off heading off
Previous Topic: FNDLOAD for Specific DFF Context
Next Topic: XML in View output
Goto Forum:
  


Current Time: Wed Dec 11 00:38:35 CST 2019