Create oracle xml header [message #439879] |
Tue, 19 January 2010 17:11 |
imymal
Messages: 2 Registered: January 2010
|
Junior Member |
|
|
Hi
I am trying to create a xml header in my sql code how would you use dbms_xmlQuery.setdataheader. The code is shown below:
set serveroutput on
DECLARE
ctx DBMS_XMLGEN.ctxHandle;
xml CLOB;
BEGIN
ctx := dbms_xmlgen.newcontext('select xmlelement ("test", xmlforest(ID_INFO AS "ID", FIRST_NAME AS "FIRST"))info from table_one');
dbms_xmlgen.setrowtag(ctx, 'Person');
dbms_xmlgen.setrowsettag(ctx, 'Enterprise');
xml := dbms_xmlgen.getxml(ctx);
dbms_output.put_line(xml);
END;
Trying to put xml information between <Enterprise> and <Person>.
The output is shown below:
<?xml version="1.0"?>
<Enterprise>
<Person>
<INFO>
<test><ID>100002</ID><FIRST>Imran</FIRST></test> </INFO>
</Person>
<Person>
<INFO>
<test><ID>100003</ID><FIRST>Omar</FIRST></test> </INFO>
</Person>
<Person>
<INFO>
<test><ID>100004</ID><FIRST>Asma</FIRST></test> </INFO>
</Person>
<Person>
<INFO>
<test><ID>100005</ID><FIRST>Fatimah</FIRST></test> </INFO>
</Person>
<Person>
<INFO>
<test><ID>100006</ID><FIRST>Aisha</FIRST></test> </INFO>
</Person>
</Enterprise>
Kind Regards - Imran Malik
|
|
|
|
Re: Create oracle xml header [message #439943 is a reply to message #439884] |
Wed, 20 January 2010 05:23 |
imymal
Messages: 2 Registered: January 2010
|
Junior Member |
|
|
Thank you for the link.
I have found another way to extract data and convert to xml.
SELECT XMLELEMENT("Enterprise", XMLAGG(XMLELEMENT("Person", XMLELEMENT("surname", scj_srtn), XMLELEMENT("code", scj_stuc)))).EXTRACT('/*')
FROM srs_scj;
I get an error shown below:
Error starting at line 1 in command:
SELECT XMLELEMENT("Enterprise", XMLAGG(XMLELEMENT("Person", XMLELEMENT("surname", scj_srtn), XMLELEMENT("code", scj_stuc)))).EXTRACT('/*')
FROM srs_scj
Error report:
SQL Error: ORA-31011: XML parsing failed
31011. 00000 - "XML parsing failed"
*Cause: XML parser returned an error while trying to parse the document.
*Action: Check if the document to be parsed is valid.
when i put a where statement shown below is gets the 1000 records in xml format
where rownum <= 1000;
I would preciate any help here.
Kind Regards - Imran Malik
[Updated on: Wed, 20 January 2010 05:24] Report message to a moderator
|
|
|
|