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

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
    322 Posts

    Re: Inserting with XMLTABLE

    ‏2011-06-27T11:15:39Z  
    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

    Re: Inserting with XMLTABLE

    ‏2011-06-27T13:37:01Z  
    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
    322 Posts

    Re: Inserting with XMLTABLE

    ‏2011-06-28T04:19:09Z  
    • Kirsi
    • ‏2011-06-27T13:37:01Z
    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
    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

    Re: Inserting with XMLTABLE

    ‏2011-06-28T06:52:59Z  
    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/
    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
    322 Posts

    Re: Inserting with XMLTABLE

    ‏2011-06-28T20:54:00Z  
    • Kirsi
    • ‏2011-06-28T06:52:59Z
    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
    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

    Re: Inserting with XMLTABLE

    ‏2011-06-29T06:34:16Z  
    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/
    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