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 previous message
ora9a
Messages: 34
Registered: June 2010
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

 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: FNDLOAD for Specific DFF Context
Next Topic: XML in View output
Goto Forum:
  


Current Time: Sun Aug 09 04:15:25 CDT 2020