Topic
1 reply Latest Post - ‏2011-06-04T14:41:57Z by MatthiasNicola
Rashed76
Rashed76
1 Post
ACCEPTED ANSWER

Pinned topic XML

‏2011-05-24T16:31:39Z |
I am trying to use XMLELEMENT/XMLROW to generate XMLrecords from DB2 9.1(Windows).
My data has some character which cannot parse those.
If I use REC2XML with expresseion length 1.3 it does. Can anyone have any idia how can I do that in XMLELEMENT/XMLROW?
Here is the query I am using:

1. SELECT XMLROW (CODE, ADDRESS_1,ADDRESS_2,ADDRESS_3 OPTION ROW "row")
FROM(Select DISTINCT BUILDING_CODE as CODE,ADDRESS1 as ADDRESS_1,ADDRESS2 as ADDRESS_2,ADDRESS3 as ADDRESS_3 from RM.TABLE1)

2. SELECT XMLELEMENT(NAME "RashedTest",
XMLAGG(
XMLELEMENT(Name "row",
XMLELEMENT(NAME "CODE",CODE),
XMLELEMENT(NAME "ADDRESS_1",ADDRESS_1),
XMLELEMENT(NAME "ADDRESS_2",ADDRESS_2),
XMLELEMENT(NAME "ADDRESS_3",ADDRESS_3)
)
))
FROM(Select DISTINCT BUILDING_CODE as CODE,ADDRESS1 as ADDRESS_1,ADDRESS2 as ADDRESS_2,ADDRESS3 as ADDRESS_3 from RM.TABLE1)

They throws error as:
An exception occurred
Message: IBMCLI DriverDB2/NT64 SQL20377N An illegal XML character "#x1A" was found in an SQL/XML expression or function argument that begins with string "ABC/". SQLSTATE=0N002

Data includes the follwoing characters:
ABC/\<><>$!@#$%&*<html><br/>
May be not all the invalid characters are showing here but it could be in the data.

Any quick help will be much appreciated.
Updated on 2011-06-04T14:41:57Z at 2011-06-04T14:41:57Z by MatthiasNicola
  • MatthiasNicola
    MatthiasNicola
    321 Posts
    ACCEPTED ANSWER

    Re: XML

    ‏2011-06-04T14:41:57Z  in response to Rashed76
    Hi Rashed,

    the problem is that XML is not allowed to contain the character x1A. In fact, XML is only allowed to contain the characters listed here:

    http://www.w3.org/TR/2008/REC-xml-20081126/#charsets

    If you try to construct an XML element with one of the disallowed characters in the element value, then DB2 must throw an error. This is what happens in your case with the function XMLELEMENT, in compliance with the SQL/XML standard. The function XMLROW is just an abbreviation for multiple invocations of XMLELEMENT, and it behaves the same way.

    The function rec2xml is a very old function that was introduced before the SQL/XML standard defined the official XML construction functions (XMLELEMENT, XMLATTRIBUTES, etc.). It is -unfortunately- more forgiving and does not prevent you from constructing illegal XML. The problem is that if you create XML elements with illegal characters, no XML parser in any consuming application will be able to parse and process the XML that you are producing.

    The recommended solution is to clean the source data so that you remove or replace the illegal characters. You could start by determining which illegal characters occur in which columns. Then you can use the SQL function REPLACE within the XMLELEMENT function to replace the illegal characters.

    Thanks,

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/