Home » Other » Client Tools » generate xml (12.1.0.2.0)
generate xml [message #676473] |
Tue, 11 June 2019 11:50 |
ora9a
Messages: 42 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
|
|
|
|
Re: generate xml [message #676494 is a reply to message #676474] |
Wed, 12 June 2019 08:43 |
ora9a
Messages: 42 Registered: June 2010
|
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 |
_jum
Messages: 577 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 #676501 is a reply to message #676496] |
Wed, 12 June 2019 12:06 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
ora9a wrote on Wed, 12 June 2019 16:43Hi,
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
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:12:55 CDT 2024
|