How can I convert selected XML column content from codepage UTF-8 to ISO 8859-2, which is my PC codepage?
The manual says that XMLSERIALIZE with EXCLUDING XMLDECLARATION will do the trick, but when opening the resulting document with <?xml version="1.0" encoding="ISO-8859-2"?> on top, in IE 7, all of the specific national characters (Croatian) are not presented correctly. If I put the <?xml version="1.0" encoding="UTF-8"?> instead, it looks fine.
MatthiasNicola 120000E28R322 Posts
Re: codepage conversion with XMLSERALIZE2012-02-16T21:31:17ZThis is the accepted answer. This is the accepted answer.Hi Vjeko,
I don't think that the EXCLUDING XMLDECLARATION option or INCLUDING XMLDECLARATION options of the XMLSERIALIZE function will cause a specific codepage conversion to happen. I think these options only specify whether you want to see an XML declaration at the top of the retrieved documents or not.
Typically it is recommended to retrieve XML without the XMLSERIALIZE function, i.e. to avoid explicit serialization and use implicit serialization instead. Implicit serialization means that the XML data is sent from the db2 server to the client and then automatically converted to client's codepage.
Here are the most relevant resources on this:
Encoding considerations for retrieval of XML data from a database:
Additional consideration may depends on the API that you use to retrieve the XML. Do you Java/JDBC or .NET or the Command Line Processor or some other interface.
Does that help?
Vjeko 010000287M3 Posts
Re: codepage conversion with XMLSERALIZE2012-02-17T07:13:22ZThis is the accepted answer. This is the accepted answer.
- MatthiasNicola 120000E28R
thank you for your answer, it pushed me in the right direction.
The story is as follows: I need to create an xml file (report) for the National Bank and the National Bank insists that the document must be in ISO-8859-2 codepage ( not very clever in my opinion, but there is no room for negotiation). I suppose they are processing it on pSeries machines where ISO-8899-2 is a default codepage (in Croatia).
I was able to get the needed result when submitting a CLP command
db2 -x "select xmlserialize ( SPPR_DAT_SADRZAJ As CLOB(100000) EXCLUDING XMLDECLARATION ) from DB2ADMIN.SPPR_IZVJ_DAT where IZVJ_DATUM = '2011-12-31' AND SPPR_IZVJ_VRSTA_SIF = 'RN'" > report.xml
on my AIX DB2 server (with ISO-8859-2 codepage),
redirecting it to a file and appending a declaration <?xml version="1.0" encoding="ISO-8859-2"?> on top.
According to DB2 documentation:
"If you retrieve the serialized data into an non-binary data type, the data is converted to the application encoding, but the encoding specification is not modified".
So I need to add an aproproate XML declaration. It seems like a DB2 shortcoming, I don't know why was it a problem to add an appropriate XML declaration.
Anyway, I still need to see how it will work with Java pureQuery server code on WAS running on AIX. I suppose it should act the same if I put the result in a CLOB host variable.