Convert Oracle data into XML document

Hi Experts

I need to create a generic stored procedure which will take table name as input parameter and create xml file with below format. Xml file name should be table name and I've never done this before. Could you please help?

I tried few methods in Oracle but the output is not coming as expected.

<?xml version="1.0" encoding="UTF-8"?>
<MessageData>
      <Category>NewSubmission</Category>
      <TimeStamp>2016-07-22T12:11:00Z</TimeStamp>
      <ProdID>1234567890</ProdID>
</MessageData>
<Submission>
      <ReportingPeriod>2015-12-31</ReportingPeriod>      
      <Replacement>
            <MessageRef>12345678902016082313392REP</MessageRef>
            <ReplacedMessageRef>12345678902016072212110NEW</ReplacedMessageRef>
      </Replacement>
</Submission>
<Person>
      <FirstName>a</FirstName>
      <LastName>a</LastName>
      <Address>
      <City>a</City>
      <CountryCode>AF</CountryCode>
      <ResCountryCode>AF</ResCountryCode>
      </Address>
</Person>
Ram KarthikAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
It looks like that data would come from different tables.  Do you have a one-to-many relation in there?

You sample XML you posted is also incorrect.  There is no root node.

Generating the XML is pretty easy.  I would suggest looking at the XML functions in SQL.

Here is the MessageData part of your XML:
/*
drop table tab1 purge;
create table tab1(category varchar2(20), time_stamp timestamp, prodid number);

insert into tab1 values('NewSubmission',to_timestamp('2016-07-22 12:11:00','YYYY-MM-DD HH24:MI:SS'),1234667890);
commit;
*/

select xmlelement("MessageData",
	xmlforest(
		category as "Category",
		to_char(time_stamp,'YYYY-MM-DD"T"HH24:MI:SS"Z"') as "TimeStamp",
		prodid as "ProdId"
	)
)
from tab1
/

Open in new window


If you can provide more detail about your tables, we can probably provide an expanded example.
0
 
awking00Commented:
Can you show the table structures and the data that would be used to produce your example xml file?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.