Topic
  • 2 replies
  • Latest Post - ‏2012-02-17T07:13:22Z by Vjeko
Vjeko
Vjeko
3 Posts

Pinned topic codepage conversion with XMLSERALIZE

‏2012-02-16T13:31:16Z |
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.
Thanks.
Updated on 2012-02-17T07:13:22Z at 2012-02-17T07:13:22Z by Vjeko
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: codepage conversion with XMLSERALIZE

    ‏2012-02-16T21:31:17Z  
    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:

    XML Serialization:
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.xml.doc/doc/c0023553.html

    Encoding considerations for retrieval of XML data from a database:
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.xml.doc/doc/c0022312.html

    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?

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • Vjeko
    Vjeko
    3 Posts

    Re: codepage conversion with XMLSERALIZE

    ‏2012-02-17T07:13:22Z  
    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:

    XML Serialization:
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.xml.doc/doc/c0023553.html

    Encoding considerations for retrieval of XML data from a database:
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.xml.doc/doc/c0022312.html

    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?

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Hi Matthias,
    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.

    Thank you.
    Vjeko