Creating XML from an Oracle DB

Hi Tim,

I have spent about three hours using Google and Metalink trying to find the

answer to what must be a FAQ when generating XML from Oracle but I cannot
find anything that answers the question.

I work for a company in the UK and we are generating an XML file from an
Oracle 9.2 database using DBMS_XMLQUERY and DBMS_LOB. The XML file has to
be
in UTF-8 format (encoding="utf-8") but this is not the format that it is
held in the database. From what I have read, it seems that it is AL32UTF8.

As a result the UK pound sign contained in a VARCHAR2 column on the
database
is causing problems. When we try to open the XML file using Internet
Explorer it will not display its contents.

The TO_ASCII value of the pound sign is 49827 when selected from the
database. We need to find a way of converting from one format to the other.

I could probably use a combination of Oracle functions such as TRANSLATE
and
CHR to convert individual occurrences of the pound sign. However, any text
column may have a pound sign in it and it is possible that there are other
characters that will cause problems of this sort. There must be a more
elegant and foolproof way of doing this.

Any advice would be much appreciated.

Keith
**********************************************************************
Visit the Virgin Atlantic website for all the latest news and
fantastic offers - http://www.virgin.com/atlantic
This e-mail (and any attachments) may contain privileged and/or
confidential information. If you are not the intended recipient please
do not disclose, copy, distribute, disseminate or take any action in
reliance on it. If you have received this message in error please reply
and tell us and then delete all copies on your system. Any opinion on
or advice or information contained in this email is not necessarily
that
of the owners or officers of this company.
Should you wish to communicate with us by e-mail, we cannot guarantee
the security of any data outside our own computer system.
**********************************************************************

Relevant Pages

  • Re: [ Off Topic ] extracting data from a database and converting it into an XML file
    ... It works great with Oracle and MySQL databases, ... Oracle has built in support for XML already? ... If you stored your data in Oracle XML Database, ... standard 'C' and 'C++', ...
    (alt.comp.lang.learn.c-cpp)
  • Re: XML in a Relational Database
    ... But i cannot use oracle because it is expensive, ... a cheap and light database. ... > Recent versions of major DBMS supports XML in a way you save each XML ... >> The focus of this application is the XML documents could be whatever. ...
    (microsoft.public.dotnet.xml)
  • Re: extracting data from a database and converting it into an XML file
    ... > I have several tables from a database. ... > I need to extract data from these tables, and then convert it into an XML ... Oracle brings extensive XML functionality inside the ... XML file into another by using XSLT. ...
    (comp.lang.cpp)
  • RE: Store XML in database - newbie question
    ... If you are using Oracle you can define a column as an XMLType this will allow ... you store and retrieve the XML as a column. ... There is also the possibilty of storing the XML as part of the ... > to save some XML data to a database. ...
    (microsoft.public.dotnet.languages.csharp)
  • RE: Store XML in database - newbie question
    ... If you are using Oracle you can define a column as an XMLType this will allow ... you store and retrieve the XML as a column. ... There is also the possibilty of storing the XML as part of the ... > to save some XML data to a database. ...
    (microsoft.public.dotnet.framework)