nssudha
asked on
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 </DataSour ce>
<DateCreated>10/16/2013 11:35:42 AM</DateCreated>
<DateLastUpdated>10/16/201 3 11:35:42 AM</DateLastUpdated>
<FootNoteCount>5</FootNote Count>
</HeaderInformation>
<EmployeeDetails>
<EMPLOYEE>
<EMPLOYEE_ID>101</EMPLOYEE _ID>
<FIRST_NAME>Neena</FIRST_N AME>
<LAST_NAME>Kochhar</LAST_N AME>
<EMAIL>NKOCHHAR</EMAIL>
<PHONE_NUMBER>515.123.4568 </PHONE_NU MBER>
<HIRE_DATE>21-SEP-05</HIRE _DATE>
<JOB_ID>AD_VP</JOB_ID>
<SALARY>17000</SALARY>
<MANAGER_ID>100</MANAGER_I D>
<DEPARTMENT_ID>90</DEPARTM ENT_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('sel ect emplyee_id as "Employee_ID,FIRST_NAME as "FIRS_NAME" ...');
EXECUTE IMMEDIATE 'TRUNCATE TABLE tmp_clob_xml');
dbms_xmlgen.setrowsettag(q ryctx, 'FootNoeDetails');
dbms_xmlgen.setrowtag(qryc tx,'FootNo te');
RESULT:= dbms_xmlgen.getxml(qryctx) ;
insert into tmp_clob_xml values (RESULT);
COMMIT;
dbms_xmlgen.closecontxt(qr ctx);
END;
/
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
<DateCreated>10/16/2013 11:35:42 AM</DateCreated>
<DateLastUpdated>10/16/201
<FootNoteCount>5</FootNote
</HeaderInformation>
<EmployeeDetails>
<EMPLOYEE>
<EMPLOYEE_ID>101</EMPLOYEE
<FIRST_NAME>Neena</FIRST_N
<LAST_NAME>Kochhar</LAST_N
<EMAIL>NKOCHHAR</EMAIL>
<PHONE_NUMBER>515.123.4568
<HIRE_DATE>21-SEP-05</HIRE
<JOB_ID>AD_VP</JOB_ID>
<SALARY>17000</SALARY>
<MANAGER_ID>100</MANAGER_I
<DEPARTMENT_ID>90</DEPARTM
</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('sel
EXECUTE IMMEDIATE 'TRUNCATE TABLE tmp_clob_xml');
dbms_xmlgen.setrowsettag(q
dbms_xmlgen.setrowtag(qryc
RESULT:= dbms_xmlgen.getxml(qryctx)
insert into tmp_clob_xml values (RESULT);
COMMIT;
dbms_xmlgen.closecontxt(qr
END;
/
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('se lect 1 a, 2 b from dual');
dbms_xmlgen.setrowsettag(q ryctx, 'aas');
dbms_xmlgen.setrowtag(qryc tx,'aa');
dbms_xmlgen.getxmltype(qry ctx,x1);
qryctx := dbms_xmlgen.newcontext('se lect 3 a, 4 b from dual');
dbms_xmlgen.setrowsettag(q ryctx, 'bbs');
dbms_xmlgen.setrowtag(qryc tx,'bb');
dbms_xmlgen.getxmltype(qry ctx,x2);
select APPENDCHILDXML(x1,'/*',x2) into x3 from dual;
dbms_output.put_line(x3.ge tStringVal ());
insert into tmp_clob_xml values (to_clob(x3));
end;
/
declare
qryctx dbms_xmlgen.ctxhandle;
x1 xmltype;
x2 xmltype;
x3 xmltype;
begin
qryctx := dbms_xmlgen.newcontext('se
dbms_xmlgen.setrowsettag(q
dbms_xmlgen.setrowtag(qryc
dbms_xmlgen.getxmltype(qry
qryctx := dbms_xmlgen.newcontext('se
dbms_xmlgen.setrowsettag(q
dbms_xmlgen.setrowtag(qryc
dbms_xmlgen.getxmltype(qry
select APPENDCHILDXML(x1,'/*',x2)
dbms_output.put_line(x3.ge
insert into tmp_clob_xml values (to_clob(x3));
end;
/
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?
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?
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...)
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
/
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'.
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
/
ASKER
Hi slightwv,
I am getting the count from the same table for <FootNoteCount>5</FootNote Count>. I believe <DateCreated>10/16/2013 11:35:42 AM</DateCreated>
<DateLastUpdated>10/16/201 3 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 </DataSour ce>
<DateCreated>10/16/2013 11:35:42 AM</DateCreated>
<DateLastUpdated>10/16/201 3 11:35:42 AM</DateLastUpdated>
<FootNoteCount>5</FootNote Count>
</HeaderInformation>
<EmployeeDetails>
<EMPLOYEE>
<EMPLOYEE_ID>101</EMPLOYEE _ID>
<FIRST_NAME>Neena</FIRST_N AME>
<LAST_NAME>Kochhar</LAST_N AME>
<EMAIL>NKOCHHAR</EMAIL>
<PHONE_NUMBER>515.123.4568 </PHONE_NU MBER>
<HIRE_DATE>21-SEP-05</HIRE _DATE>
<JOB_ID>AD_VP</JOB_ID>
<SALARY>17000</SALARY>
<MANAGER_ID>100</MANAGER_I D>
<DEPARTMENT_ID>90</DEPARTM ENT_ID>
</EMPLOYEE>
</EmployeeDetails>
I am getting the count from the same table for <FootNoteCount>5</FootNote
<DateLastUpdated>10/16/201
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
<DateCreated>10/16/2013 11:35:42 AM</DateCreated>
<DateLastUpdated>10/16/201
<FootNoteCount>5</FootNote
</HeaderInformation>
<EmployeeDetails>
<EMPLOYEE>
<EMPLOYEE_ID>101</EMPLOYEE
<FIRST_NAME>Neena</FIRST_N
<LAST_NAME>Kochhar</LAST_N
<EMAIL>NKOCHHAR</EMAIL>
<PHONE_NUMBER>515.123.4568
<HIRE_DATE>21-SEP-05</HIRE
<JOB_ID>AD_VP</JOB_ID>
<SALARY>17000</SALARY>
<MANAGER_ID>100</MANAGER_I
<DEPARTMENT_ID>90</DEPARTM
</EMPLOYEE>
</EmployeeDetails>
ASKER
Hi slightwv and flow01,
I will try both your examples tomorrow and post the outcome here.
Thank you for the help.
Thanks,
Raj.
I will try both your examples tomorrow and post the outcome here.
Thank you for the help.
Thanks,
Raj.
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):
A valid XML document must have a start node and end node for ALL child nodes.
Something like:
>>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>
A valid XML document must have a start node and end node for ALL child nodes.
Something like:
<SomeMasterRootNode>
<HeaderInformation>
...
</HeaderInformation>
<EmployeeDetails>
...
</EmployeeDetails>
</SomeMasterRootNode>
ASKER
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.
Thanks again!
Raj.
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.
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.
ASKER
Got it Slightwv. I will post the same.
Thank you!
Raj
Thank you!
Raj
ASKER
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
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
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.
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.
ASKER
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.
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.
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
/
ASKER
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.
Thanks again!
Raj.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
I'm not sure if the order of the orders were important.
ASKER
It worked! Thank you so much for all the help!
Thanks again!
Raj.
Thanks again!
Raj.
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.