OraFAQ Forum: JDeveloper, Java & XML » Include XML Header when using XMLELEMENT

Home » Developer & Programmer » JDeveloper, Java & XML » Include XML Header when using XMLELEMENT

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Include XML Header when using XMLELEMENT [message #160986]

Wed, 01 March 2006 04:45

ianbrewster
Messages: 8
Registered: March 2006
Location: MLFC, London

Junior Member

Hi,

I am using ORACLE 9.2.0.6 to create an XML file.
I am using a select statement using XMLELEMENT, XMLAGG etc. in a package procedure.
I am using a FETCH into an XMLTYPE variable within the procedure.

The problem I have is that the header:
<?xml version="1.0"?>
is not included in this variable and I cannot seem to find a way to add it.
I guess, preferably I'd prefer to have it as the first line in the select statement.
I have hunted high and low for this but can't seem to find a solution.
Hopefully it's something very simple that I'm missing.

Any help would be greatly appreciated.

The rest of the XML file is being output and looks absolutely fine when viewed in IE.

Thanks.

Report message to a moderator

Re: Include XML Header when using XMLELEMENT [message #161120 is a reply to message #160986]

Thu, 02 March 2006 02:18

mchadder
Messages: 224
Registered: May 2005
Location: UK

Senior Member

Hello.

I'm afraid it simply doesn't. Functions such as SYS_XMLGEN etc add it, but "true" SQL/XML doesn't.

SQL> SELECT SYS_XMLGEN('x') from dual;

SYS_XMLGEN('X')
------------------------------------------------------
<?xml version="1.0"?>
<ROW>x</ROW>

You have to "add" it, until 10g of course, when we get the XMLROOT function, http://www.oratechinfo.co.uk/sqlxml.html#xmlroot.

Rgds

Report message to a moderator

Re: Include XML Header when using XMLELEMENT [message #161129 is a reply to message #161120]

Thu, 02 March 2006 03:29

ianbrewster
Messages: 8
Registered: March 2006
Location: MLFC, London

Junior Member

Hi,

I should've used this forum sooner.
Would've saved me a lot of fruitless searching.
Thanks for the reply.

If I use your solution
Select sys_xmlgen('x') from dual
How do I 'add' it to the XMLTYPE variable along with the rest of my data. I tried populating it first with your select and then fetching the rest of my data and as expected the first header was overwritten.

Not sure how to concatenate the two bits of data together.
Also, the select also returns the row data <ROW>x</ROW>
which is not required.
I need to get rid of this.

Thanks for your help.
regards
Ian.

Report message to a moderator

Re: Include XML Header when using XMLELEMENT [message #161213 is a reply to message #161120]

Thu, 02 March 2006 11:36

ianbrewster
Messages: 8
Registered: March 2006
Location: MLFC, London

Junior Member

Hi,

I've now solved the problem of adding the header to the output.
I created a separate procedure which is passed the XML and some other parameters. One of these is whether or not a header is required. If it is I just write the standard header record first.

I then encountered another problem where the process was failing with write-error when I started using volumes of data.
This was because the record size had gone above the maximum 32767.
I got round this by adding NewLine characters at the end of each data record, searching for the first tag of each record and replacing with newline character plus the first tag.
This again is parameters passed, searchfor and ReplaceWith.
This has the benefit of making the file viewable in text editors.

Apparently all these problems will be solved in ORACLE 10g release. Shame we won't be getting it here for a few years!!

Thanks for your help.
Best regards.
Ian B.

Report message to a moderator

Re: Include XML Header when using XMLELEMENT [message #161229 is a reply to message #160986]

Thu, 02 March 2006 14:01

mchadder
Messages: 224
Registered: May 2005
Location: UK

Senior Member

Hello,

well, yes. 10g does make it easier, simply because it supports XMLROOT, but the 32767 issue is presumably because you're doing something like :

SQL> SELECT '<?xml version="1.0"?>' || 
  2         XMLAGG(XMLELEMENT("x", XMLELEMENT("object_name", object_name)))
  3    FROM all_objects;
SELECT '<?xml version="1.0"?>' ||
*
ERROR at line 1:
ORA-19011: Character string buffer too small

The problem here is that you're treating it as a VARCHAR2, you'd need to use the .getClobVal() method to ensure treating it as
a CLOB :

SQL> SELECT '<?xml version="1.0"?>' || 
  2         XMLAGG(XMLELEMENT("x", XMLELEMENT("object_name", object_name))).getClobVal()
  3    FROM all_objects;

'<?XMLVERSION="1.0"?>'||XMLAGG(XMLELEMENT("X",XMLELEMENT("OBJECT_NAME",OBJECT_NA
--------------------------------------------------------------------------------
<?xml version="1.0"?><x><object_name>ICOL$</object_name></x><x><object_name>I_US

Rgds

Report message to a moderator

Re: Include XML Header when using XMLELEMENT [message #161230 is a reply to message #160986]

Thu, 02 March 2006 14:03

mchadder
Messages: 224
Registered: May 2005
Location: UK

Senior Member

Forgot to mention, if the "write error" is your export code, then you might want to have a look at "pretty printing" ( See http://www.oratechinfo.co.uk/sqlxml.html#point5 for more information ).

Rgds

Report message to a moderator

Re: Include XML Header when using XMLELEMENT [message #161304 is a reply to message #161230]

Fri, 03 March 2006 03:14

ianbrewster
Messages: 8
Registered: March 2006
Location: MLFC, London

Junior Member

Hiya,

As mentioned earlier I'm outputting the data in a local procedure.
This is breaking the xml data down into 32767 size chunks and outputting the buffer but because there are no linefeed characters in the data it is exceeding the maximum record size on VMS and blowing.
I added a neat little bit to the procedure where you pass it a search criteria and a replace criteria.
I passed it the first tag in each data section and replaced it with a NewLine character concatenated with the same first tag.
Works well.

As for the header, I also pass it a parameter saying whether to include the header or not. if it is required I move the header line <?xml version="1.0"?> into the output buffer first.

This is a generic local procedure in my package that handles the whole file output. It gets passed xmlcontent,TargetDirectory, filename, characterset, AddHeaderFlag, SearchFor, replaceWith.
It is now working well and can be used for other XML file creation jobs I have to write.

Thanks for all your help.
Regards.
Ian B.

Report message to a moderator

Previous Topic:	ORA-19025: EXTRACTVALUE returns value of only
Next Topic:	invalid user.table.column when run Jdeveloper

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 04:42:32 CDT 2024