Topic
6 replies Latest Post - ‏2011-06-29T06:34:16Z by Kirsi
Kirsi
Kirsi
11 Posts
ACCEPTED ANSWER

Pinned topic Inserting with XMLTABLE

‏2011-06-27T10:05:54Z |
I am inserting data from XML to relational tables with XMLTABLE.

If the insert fails with CASTing error, for example trying to insert decimal data into an INTEGER field, is there a way to find out what table+column or maybe PATH was involved.

It would be easier to find out if there were errors in the source data.

Kirsi
Updated on 2011-06-29T06:34:16Z at 2011-06-29T06:34:16Z by Kirsi
  • MatthiasNicola
    MatthiasNicola
    321 Posts
    ACCEPTED ANSWER

    Re: Inserting with XMLTABLE

    ‏2011-06-27T11:15:39Z  in response to Kirsi
    Hi Kirsi,

    this is a good question because the SQL error (unfortunately) doesn't tell you in which column the error occurred. I have used the following trick in the past.

    Let's say my original XMLTABLE function looks like this, with only the first two column expressions shown:

    
    SELECT T.* FROM customer, XMLTABLE(
    '$INFO/customerinfo' COLUMNS custID   INTEGER     PATH 
    '@Cid', city     VARCHAR(16) PATH 
    'addr/city', .... ) AS T;
    


    Then just for debugging purposes I would change some (or all) of the column expression to produce my own "error values", like this:

    
    SELECT T.* FROM customer, XMLTABLE(
    '$INFO/customerinfo' COLUMNS custID   INTEGER     PATH 
    '(if (@Cid castable as xs:integer) then @Cid else -1)', city     VARCHAR(16) PATH 
    'addr/city/(if (string-length(.) <= 16) then . else "Error!")', ....) AS T;
    


    Then I can check the resulting column values to see where a -1 or "Error!" occurs in the output.

    Does this help?

    Thanks,

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • Kirsi
    Kirsi
    11 Posts
    ACCEPTED ANSWER

    Re: Inserting with XMLTABLE

    ‏2011-06-27T13:37:01Z  in response to Kirsi
    I have to consider this though it is tuff when in one table you have more than houndred columns. Others have less, so this is ok for them at least.

    Old xml extender did give you more information (table+column where the error occured). Extender is what we are now replacing with XMLTABLE.

    Thank you so far,
    I will get back to you with another question another day

    Kirsi
    • MatthiasNicola
      MatthiasNicola
      321 Posts
      ACCEPTED ANSWER

      Re: Inserting with XMLTABLE

      ‏2011-06-28T04:19:09Z  in response to Kirsi
      Another idea is to use an XML Schema to validate the incoming XML documents, to ensure that their data values match the expected data types.

      If you have an XML Schema and a document that happens to be invalid, you can use the DB2 procedure XSR_GET_PARSING_DIAGNOSTICS to get more detailed information about which element or attributes violates the schema.

      http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.xml.doc/doc/c0054516.html
      http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.xml.doc/doc/r0054517.html


      Matthias Nicola
      http://www.tinyurl.com/pureXML
      http://nativexmldatabase.com/
      • Kirsi
        Kirsi
        11 Posts
        ACCEPTED ANSWER

        Re: Inserting with XMLTABLE

        ‏2011-06-28T06:52:59Z  in response to MatthiasNicola
        Can you use that also in DB2 z/os v9.1?

        We do not get a proper schema that we could use from the xml data provider, but I think I could make that for us to use.
        Your first idea was ok also.

        I think I could on the first round rely on the incoming xml and run it and store the failed documents some where and then I could run this parsing diagnostics or some validating select-procedure (from your first idea) against the failed documents only.

        Kirsi
        • MatthiasNicola
          MatthiasNicola
          321 Posts
          ACCEPTED ANSWER

          Re: Inserting with XMLTABLE

          ‏2011-06-28T20:54:00Z  in response to Kirsi
          DB2 9.1 for z/OS supports the use of XML Schemas for validation, but the routine XSR_GET_PARSING_DIAGNOSTICS is new to DB2 9.7 for Linux, UNIX, and Windows and not (yet?) available in DB2 for z/OS.

          It's probably obvious, but the ideal solution would be to agree with the XML producer on an XML Schema, so that the provider can use the schema to validate the documents that they produce before you ever receive them. But yes, I know, the world is not always perfect :-).
          Matthias Nicola
          http://www.tinyurl.com/pureXML
          http://nativexmldatabase.com/
          • Kirsi
            Kirsi
            11 Posts
            ACCEPTED ANSWER

            Re: Inserting with XMLTABLE

            ‏2011-06-29T06:34:16Z  in response to MatthiasNicola
            Yes you are absolutelly right about that,
            the XML data provider gives us a schema without the maximum data lengths for example for Strings and double values. They have given them in separate definitions, and sometimes they forgot to tell us if something changes.

            But that is another story, I just have to thank you.
            I appreciate a lot your valuable advices!

            Best Regards, Kirsi