Home » Developer & Programmer » JDeveloper, Java & XML » Create oracle xml header (oracle 10g)
Create oracle xml header [message #439879] Tue, 19 January 2010 17:11 Go to next message
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 #439884 is a reply to message #439879] Tue, 19 January 2010 21:31 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Is this you are looking for ? click on it to see that content

sriram Smile
Re: Create oracle xml header [message #439943 is a reply to message #439884] Wed, 20 January 2010 05:23 Go to previous messageGo to next message
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

Re: Create oracle xml header [message #439972 is a reply to message #439943] Wed, 20 January 2010 07:00 Go to previous message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
As you stated in your header(title) ...

Xml with header.. and even your message like ..
Quote:
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:

like you are able to generate the xml but not the header.....

so My the link posted is for to generate xml header.

And whatever in your 2nd post the query executes fine for me without any error. and there are so many ways to do that...



http://www.psoug.org/reference/dbms_xmlgen.html
http://forums.oracle.com/forums/thread.jspa?threadID=855271&tstart=465
DBMS_XMLquery or DBMS_XMLGen......

and what type of columns you are extracting?
and http://it.toolbox.com/blogs/parthas/need-help-ora31011-xml-parsing-failed-17499

http://forums.oracle.com/forums/message.jspa?messageID=2591289#2591289

Let me try with some other ways and get back to you...

sriram Smile

[Updated on: Wed, 20 January 2010 07:27]

Report message to a moderator

Previous Topic: java.lang.NullPointerException while invoke Session Bean
Next Topic: Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds 500
Goto Forum:
  


Current Time: Thu Mar 28 06:14:48 CDT 2024