Oracle XML Help.

Hi,

I need to generate an XML file from table data. I am new to XML and need some help. I was able to do it through a block using dbms_xmlgen. Now I need to add header information instead of the default <?xml version="1.0"?. I would like to add below header information and generate an XML file as below. I am working on Oracle Daabase 11g.

Any help is greatly appreciated!

Thank you!
Raj Naraparaju.

<HeaderInformation>
<DataSource>EMPLR-Footnote</DataSource>
<DateCreated>10/16/2013 11:35:42 AM</DateCreated>
<DateLastUpdated>10/16/2013 11:35:42 AM</DateLastUpdated>
<FootNoteCount>5</FootNoteCount>
</HeaderInformation>

<EmployeeDetails>
<EMPLOYEE>
<EMPLOYEE_ID>101</EMPLOYEE_ID>
<FIRST_NAME>Neena</FIRST_NAME>
<LAST_NAME>Kochhar</LAST_NAME>
<EMAIL>NKOCHHAR</EMAIL>
<PHONE_NUMBER>515.123.4568</PHONE_NUMBER>
<HIRE_DATE>21-SEP-05</HIRE_DATE>
<JOB_ID>AD_VP</JOB_ID>
<SALARY>17000</SALARY>
<MANAGER_ID>100</MANAGER_ID>
<DEPARTMENT_ID>90</DEPARTMENT_ID>
</EMPLOYEE>
</EmployeeDetails>


I am using employee table from "Oracle® XML DB Developer's Guide 11g" as I am not sure if I am allowed to post our actual data. I need help with the Header information. The script I have got so far is:

--PL/SQL BLOCK --
Declare

qryctx dbms_xmlgen.ctxhandle;
RESULT CLOB;

BEGIN

qryctx := dbms_xmlgen.newcontxt('select emplyee_id as "Employee_ID,FIRST_NAME as "FIRS_NAME" ...');

EXECUTE IMMEDIATE 'TRUNCATE TABLE tmp_clob_xml');

dbms_xmlgen.setrowsettag(qryctx, 'FootNoeDetails');
dbms_xmlgen.setrowtag(qryctx,'FootNote');
RESULT:= dbms_xmlgen.getxml(qryctx);

insert into tmp_clob_xml values (RESULT);

COMMIT;

dbms_xmlgen.closecontxt(qrctx);

END;
/
nssudhaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Easiest way is just prepend the string:

RESULT:= '<?xml version="1.0"?>' || dbms_xmlgen.getxml(qryctx);


There are other ways.  For example, you can do it with XMLSERIALIZE:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions252.htm#sthref1818

As for generating the XML, I really prefer the native XML SQL functions like XMLELEMENT to do it.

dbms_xmlgen will work as long as you can live with the limitations.  I remember playing with it a long time ago and found it wasn't as flexible as I needed.

I'll work on an example using the SQL function if I have the employee table loaded in my dev database.

I'll post what I come up with.
0
flow01Commented:
check if   APPENDCHILDXML does what you want (i'm not sure if you lose the  <?xml version="1.0"?> nor if you really want to lose it and just want to add the header information to the xml

declare
qryctx dbms_xmlgen.ctxhandle;
  x1 xmltype;
  x2 xmltype;
  x3 xmltype;
begin
 qryctx := dbms_xmlgen.newcontext('select 1 a, 2 b from dual');
 dbms_xmlgen.setrowsettag(qryctx, 'aas');
   dbms_xmlgen.setrowtag(qryctx,'aa');
    dbms_xmlgen.getxmltype(qryctx,x1);
 qryctx := dbms_xmlgen.newcontext('select 3 a, 4 b from dual');
dbms_xmlgen.setrowsettag(qryctx, 'bbs');
dbms_xmlgen.setrowtag(qryctx,'bb');
    dbms_xmlgen.getxmltype(qryctx,x2);
  select APPENDCHILDXML(x1,'/*',x2) into x3 from dual;
 dbms_output.put_line(x3.getStringVal());
 insert into tmp_clob_xml values (to_clob(x3));
end;
/
0
slightwv (䄆 Netminder) Commented:
OH, I completely misread the question....  *I feel so stupid*

Where are you getting the header information?  If it is from another table, please post some information.

Can you also post the desired XML document?  Where do you want the header fragment inserted?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

slightwv (䄆 Netminder) Commented:
Here's the approach I suggest.  If you have any questions, please ask.

In addition to the questions I asked above, I have one more:
I assume you want the ability to include more than one employee?

You would need a parent node like <Employees> to contain each employee.

Based on a simplified version of the employee table, this at least generates what I think you want (then again, I could be WRONG again...)

--drop table employee purge;
--create table employee(
	--employee_id number,
	--first_name varchar2(10),
	--last_name varchar2(10),
	--email varchar2(10)
--);
--
--insert into employee values(101,'Neena','Kochhar','NKOCHHAR');
--insert into employee values(102,'Fred','Flintstone','fflint');
--commit;


select xmlserialize(document
	xmlelement("EmployeeDetails",
		xmlelement("HeaderInformation",
			xmlforest(
				'EMPLR-Footnote' as "DataSource",
				sysdate as "DateCreated"
			)
		),
		xmlelement("EMPLOYEE",
			xmlforest(
				employee_id as "EMPLOYEE_ID",
				first_name as "FIRST_NAME",
				last_name as "LAST_NAME",
				email as "EMAIL"
			)
		)
	)
version '1.0' indent)
from employee where employee_id=101
/

Open in new window

0
slightwv (䄆 Netminder) Commented:
Given the test case above and the assumption of multiple employees, here is that example.

The xmlserialize really isn't necessary but when testing I prefer my XML to be 'pretty'.

select xmlserialize(document
	xmlelement("EmployeeDetails",
		xmlelement("HeaderInformation",
			xmlforest(
				'EMPLR-Footnote' as "DataSource",
				sysdate as "DateCreated"
			)
		),
		xmlelement("Employees",
			xmlagg(
				xmlelement("EMPLOYEE",
					xmlforest(
						employee_id as "EMPLOYEE_ID",
						first_name as "FIRST_NAME",
						last_name as "LAST_NAME",
						email as "EMAIL"
					)
				)
			)
		)
	)
version '1.0' indent)
from employee
/

Open in new window

0
nssudhaAuthor Commented:
Hi slightwv,

I am getting the count from the same table for <FootNoteCount>5</FootNoteCount>. I believe <DateCreated>10/16/2013 11:35:42 AM</DateCreated>
<DateLastUpdated>10/16/2013 11:35:42 AM</DateLastUpdated> can be taken from sysdate.

I did post the desired XML document. I am posting it again below. If you want, I can try and attach the actual sample XML document tomorrow by masking the actual data.

Thanks,
 
Raj.



<HeaderInformation>
<DataSource>EMPLR-Footnote</DataSource>
<DateCreated>10/16/2013 11:35:42 AM</DateCreated>
<DateLastUpdated>10/16/2013 11:35:42 AM</DateLastUpdated>
<FootNoteCount>5</FootNoteCount>
</HeaderInformation>

<EmployeeDetails>
<EMPLOYEE>
<EMPLOYEE_ID>101</EMPLOYEE_ID>
<FIRST_NAME>Neena</FIRST_NAME>
<LAST_NAME>Kochhar</LAST_NAME>
<EMAIL>NKOCHHAR</EMAIL>
<PHONE_NUMBER>515.123.4568</PHONE_NUMBER>
<HIRE_DATE>21-SEP-05</HIRE_DATE>
<JOB_ID>AD_VP</JOB_ID>
<SALARY>17000</SALARY>
<MANAGER_ID>100</MANAGER_ID>
<DEPARTMENT_ID>90</DEPARTMENT_ID>
</EMPLOYEE>
</EmployeeDetails>
0
nssudhaAuthor Commented:
Hi  slightwv and flow01,

I will try both your examples tomorrow and post the outcome here.

Thank you for the help.

Thanks,
Raj.
0
slightwv (䄆 Netminder) Commented:
I see another mistake I made in my last test case. Empolyees should be EmployeeDetails

>>I did post the desired XML document

That isn't a valid XML doc.  It is two distinct fragments.

Bascially what you posted is (removing all the child nodes):
<HeaderInformation>
...
</HeaderInformation>
<EmployeeDetails>
...
</EmployeeDetails> 

Open in new window


A valid XML document must have a start node and end node for ALL child nodes.

Something like:
<SomeMasterRootNode>
<HeaderInformation>
...
</HeaderInformation>
<EmployeeDetails>
...
</EmployeeDetails> 
</SomeMasterRootNode>

Open in new window

0
nssudhaAuthor Commented:
Thank you slightwv! I copied and typed the XML document manually. I might have missed some information. I will try to attach the real XML sample tomorrow, if possible.

Thanks again!
Raj.
0
slightwv (䄆 Netminder) Commented:
Creating a test case with dummied up data is fine.  Just make sure it represents what you actually need.

When you post tomorrow, please provide table structures and sample data with your expected results.

This way we can provide tested solutions that you should be able to plug and play.

The more accurate your requirements, the more accurate our examples.

You can't post 'real world' data most of the time but create your samples very close.

For example if your real table is:  
SALARIES(employ_ssn varchar2(9), salary number)
111223333,1000000000
3333445555,123

dummy it up:
my_pets(pet_name varchar2(9), age number)
fido,1000000000
spot,999

Then you should be able to to a simple search/replace and our example should work.
0
nssudhaAuthor Commented:
Got it Slightwv. I will post the same.
Thank you!
Raj
0
nssudhaAuthor Commented:
Hi sligtwv,

Sorry for the delay. I was in trainings and meetings all morning. Please find attached my XML and the table description is below. I modified the table name and details with orders.

Thanks,
Raj.

TABLE DESCRIPTION:

Table: Orders

RPT_ORD_ID      CHAR(32)
OrderID            NOT NULL CHAR(32)
FTNT_ORD_ ID    NUMBER
OrderDesc      VARCHAR2(500)
IS_ACTV      CHAR(1)
Orders.xml
0
slightwv (䄆 Netminder) Commented:
It's not invalid XML per say but you should really group repeating nodes.

Can you change the output or do you have to have it in that exact format?

If you can change it, I would suggest a <FootNotes> node to encapsulate the individual <FootNote> nodes.

Also confirm LastUpdated and Created should both be sysdate.  Something just doesn't seem right about that.
0
nssudhaAuthor Commented:
I am not sure if I can change the output as the sample was provided by our users.

I totally agree with you on the Laste Updated and Created date part. I can modify it once I have some clarity on it. If you could provide me an example with a block for the XML I provided, that would be great. If there are any changes that I cannot figure out, I will post the changes here again.

Thanks in advance!
Raj.
0
slightwv (䄆 Netminder) Commented:
Below is my test case.  If something doesn't work, please add to the test case and explain where it is failing.

drop table myorders purge;
create table myorders (
RPT_ORD_ID		CHAR(32),
OrderID			CHAR(32),
FTNT_ORD_ID		NUMBER,
OrderDesc		VARCHAR2(500),
IS_ACTV			CHAR(1) 
);

insert into myorders(orderid,orderdesc) values('1', 'Oracle Developers Guide.');
insert into myorders(orderid,orderdesc) values('2', 'Oracle DBA Guide.');
insert into myorders(orderid,orderdesc) values('3', 'Oracle PLSQL Guide.');
insert into myorders(orderid,orderdesc) values('4', 'Java Developers Guide.');
insert into myorders(orderid,orderdesc) values('5', 'Java for Dummies.');
commit;

with mydata as (
select orderid, orderdesc, count(*) over() mycount from myorders
)
select xmlserialize(document
	xmlelement("FootNoteDetails",
		xmlelement("HeaderInformation",
			xmlforest(
				'ORDR-Footnote' as "DataSource",
				sysdate as "DateCreated",
				sysdate as "DateLastUpdated",
				mycount as "OrdersCount"
			)
		),
		xmlagg(
			xmlelement("FootNote",
				xmlforest(
					trim(orderid) as "OrderID",
					orderdesc as "OrderDesc"
				)
			)
		)
	)
version '1.0' indent)
from mydata
group by mycount
/

Open in new window

0
nssudhaAuthor Commented:
It worked as a query when I ran it. Thank you! I have another small question. How do I insert this output into a temporary table TMP_CLOB_XML with one column "XML_RESULT" CLOB? When I tried through a PL/SQL block it is giving me an error. Please help me with this also.

Thanks again!
Raj.
0
slightwv (䄆 Netminder) Commented:
Quick tweak.

drop table tab1 purge;
create table tab1(col1 clob);

insert into tab1 (
select xmlserialize(document
	xmlelement("FootNoteDetails",
		xmlelement("HeaderInformation",
			xmlforest(
				'ORDR-Footnote' as "DataSource",
				sysdate as "DateCreated",
				sysdate as "DateLastUpdated",
				mycount as "OrdersCount"
			)
		),
		xmlagg(
			xmlelement("FootNote",
				xmlforest(
					trim(orderid) as "OrderID",
					orderdesc as "OrderDesc"
				)
			)
		order by orderid)
	)
version '1.0' indent)
from (
select orderid, orderdesc, count(*) over() mycount from myorders
)
group by mycount
)
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
I should point out that there is one difference inside the XML select between my latest and previous posts, I added an order by to the XMLAGG call.  

I'm not sure if the order of the orders were important.
0
nssudhaAuthor Commented:
It worked! Thank you so much for all the help!

Thanks again!
Raj.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.