xml xpath ibm-integration-bus extended-sql

Get XPath for XML Field Value that caused transformation error in IIB

I've a message flow in IIB that transforms an XML to flat file (DFDL) structure. I've a strange requirement on the exception handling.

While transformation, if any XML field causes an exception (say conversion exception), I've to generate an XML report which will have the XPath of the field causing issue. The ExceptionList generated has no XPath (in most of the cases).

How can I implement this without hampering too much of performance?

I have tried two methods:

1) Generate XPath for the complete XML payload and compare value

Suppose I've the XML as below and EmployerAddress(having value 'XYZ') caused issue

<ns:Company xmlns:ns="https://company">
    <Employer>
        <EmployerName>XYZ</EmployerName>         /ns:Company[1]/Employer[1]/EmployerName[1]='XYZ'
        <EmployerAddress>XYZ</EmployerAddress>   /ns:Company[1]/Employer[1]/EmployerAddress[1]='XYZ'
        <Employee>
            <EmployeeName>ABC</EmployeeName>     /ns:Company[1]/Employer[1]/Employee[1]/EmployeeName[1]='ABC'
            <Department>ABC</Department>         /ns:Company[1]/Employer[1]/Employee[1]/Department[1]='ABC'
        </Employee>
    </Employer>
</ns:Company>

ExceptionList contains Insert-Texts which has the value (in most of the cases) that caused error. Concatenate XYZ as ='XYZ' and search in the entire list of XPaths generated.

However this logic has issue, since ='XYZ' was also the value EmployerName, the search will return me more than 1 XPaths out of which, only 1 is correct.

2) Storing checkpoints before every ESQL statement

Store XPath as CheckPoints in Environment Tree before executing any statement. After successful execution of the statement, override the Checkpoint with next element involved in the ESQL statement.

This works however this is not a very good way to write a code plus memory & time consumption is maximum.

Any suggestions/pseudo/code will help a lot. Thanks in advance!

UPDATE

Adding Complete ExceptionList

ExceptionList
    RecoverableException
            File:CHARACTER:F:\build\slot3\S1000_P\src\DataFlowEngine\MessageServices\ImbDataFlowNode.cpp
            Line:INTEGER:1250
            Function:CHARACTER:ImbDataFlowNode::createExceptionList
            Type:CHARACTER:ComIbmWSInputNode
            Name:CHARACTER:getXPaths#FCMComposite_1_1
            Label:CHARACTER:getXPaths.HTTP Input
            Catalog:CHARACTER:BIPmsgs
            Severity:INTEGER:3
            Number:INTEGER:2230
            Text:CHARACTER:Node throwing exception
            Insert
                    Type:INTEGER:14
                    Text:CHARACTER:getXPaths.HTTP Input
            RecoverableException
                    File:CHARACTER:F:\build\slot3\S1000_P\src\DataFlowEngine\PluginInterface\ImbJniNode.cpp
                    Line:INTEGER:1269
                    Function:CHARACTER:ImbJniNode::evaluate
                    Type:CHARACTER:ComIbmXslMqsiNode
                    Name:CHARACTER:getXPaths#FCMComposite_1_5
                    Label:CHARACTER:getXPaths.XSL Transform
                    Catalog:CHARACTER:BIPmsgs
                    Severity:INTEGER:3
                    Number:INTEGER:2230
                    Text:CHARACTER:Caught exception and rethrowing
                    Insert
                            Type:INTEGER:14
                            Text:CHARACTER:getXPaths.XSL Transform
                    RecoverableException
                            File:CHARACTER:F:\build\slot3\S1000_P\src\DataFlowEngine\SQLNodeLibrary\ImbComputeNode.cpp
                            Line:INTEGER:515
                            Function:CHARACTER:ImbComputeNode::evaluate
                            Type:CHARACTER:ComIbmComputeNode
                            Name:CHARACTER:getXPaths#FCMComposite_1_4
                            Label:CHARACTER:getXPaths.createError
                            Catalog:CHARACTER:BIPmsgs
                            Severity:INTEGER:3
                            Number:INTEGER:2230
                            Text:CHARACTER:Caught exception and rethrowing
                            Insert
                                    Type:INTEGER:14
                                    Text:CHARACTER:getXPaths.createError
                            RecoverableException
                                    File:CHARACTER:F:\build\slot3\S1000_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp
                                    Line:INTEGER:792
                                    Function:CHARACTER:SqlStatementGroup::execute
                                    Type:CHARACTER:
                                    Name:CHARACTER:
                                    Label:CHARACTER:
                                    Catalog:CHARACTER:BIPmsgs
                                    Severity:INTEGER:3
                                    Number:INTEGER:2488
                                    Text:CHARACTER:Error detected, rethrowing
                                    Insert
                                            Type:INTEGER:5
                                            Text:CHARACTER:.createError.Main
                                    Insert
                                            Type:INTEGER:5
                                            Text:CHARACTER:13.3
                                    Insert
                                            Type:INTEGER:5
                                            Text:CHARACTER:SET OutputRoot.XMLNSC.Output.Casting = CAST(InputRoot.XMLNSC.*:*.Employer.EmployerAddress AS INTEGER);
                                    RecoverableException
                                            File:CHARACTER:F:\build\slot3\S1000_P\src\DataFlowEngine\ImbRdl\ImbRdlTypeCast.cpp
                                            Line:INTEGER:342
                                            Function:CHARACTER:SqlTypeCast::evaluate
                                            Type:CHARACTER:
                                            Name:CHARACTER:
                                            Label:CHARACTER:
                                            Catalog:CHARACTER:BIPmsgs
                                            Severity:INTEGER:3
                                            Number:INTEGER:2521
                                            Text:CHARACTER:Error casting from %3 to %4
                                            Insert
                                                    Type:INTEGER:5
                                                    Text:CHARACTER:.createError.Main
                                            Insert
                                                    Type:INTEGER:5
                                                    Text:CHARACTER:13.42
                                            Insert
                                                    Type:INTEGER:5
                                                    Text:CHARACTER:'XYZ'
                                            Insert
                                                    Type:INTEGER:5
                                                    Text:CHARACTER:INTEGER
                                            ConversionException
                                                    File:CHARACTER:F:\build\slot3\S1000_P\src\CommonServices\ImbUtility.cpp
                                                    Line:INTEGER:441
                                                    Function:CHARACTER:imbWcsToInt64
                                                    Type:CHARACTER:
                                                    Name:CHARACTER:
                                                    Label:CHARACTER:
                                                    Catalog:CHARACTER:BIPmsgs
                                                    Severity:INTEGER:3
                                                    Number:INTEGER:2595
                                                    Text:CHARACTER:Invalid characters
                                                    Insert
                                                            Type:INTEGER:2
                                                            Text:CHARACTER:-1
                                                    Insert
                                                            Type:INTEGER:5
                                                            Text:CHARACTER:S22018
                                                    Insert
                                                            Type:INTEGER:5
                                                            Text:CHARACTER:XYZ

This is what my Error Handling method generated in Environment Tree

ErrorDetails
    Error
        Exact
           ErrorCode:INTEGER:2595
           ExceptionType:CHARACTER:ConversionException
           ErrorSeverity:CHARACTER:Error
           ErrorDescription:CHARACTER:Error casting character string 'XYZ' to an integer. State = '-1' 'S22018' '0' ''. 
           ErrorExplanation:CHARACTER:An attempt was made to cast the character string 'XYZ' to an integer, but the string was of the wrong format. 
           ErrorResponse:CHARACTER:Other messages will give an indication of context of this error.
        Parent
           ErrorCode:INTEGER:2521
           ExceptionType:CHARACTER:RecoverableException
           ErrorSeverity:CHARACTER:Error
           ErrorDescription:CHARACTER:(.createError.Main, 13.42) : Error casting the value ''XYZ'' to 'INTEGER'. 
           ErrorExplanation:CHARACTER:An error occurred when casting a value to a different data type. This may be because no conversions exist between the two data types or because the particular value was unsuitable. 
           ErrorResponse:CHARACTER:Subsequent messages will indicate the context of the error.

Using logic (1), I get a response as

<Output>
    <CompleteXPath>/ns:Company[1]/Employer[1]/EmployerName[1]|/ns:Company[1]/Employer[1]/EmployerAddress[1]</CompleteXPath>
    <FieldName>EmployerName|EmployerAddress</FieldName>
    <FieldValue>XYZ|XYZ</FieldValue>
</Output>

Solution

While transformation, if any XML field causes an exception (say conversion exception), I've to generate an XML report which will have the XPath of the field causing issue.

That is a technical requirement, and (as you are finding out) it can be difficult/ expensive to implement. Who wants this XML report, and what do they want to do with it?

My suggestion, based on a lot of experience in this area is:

Do as much validation as possible using XMLNSC and XML Schema. Your example 'XYZ is not an integer' could easily be caught by declaring the elemnent as xs:int (or some other type of integer) in the XSD. The XMLNSC parser always supplies the path to the element when it reports a valdiation error. Stop writing ESQL for things that XMLNSC can do for you!
Do not try to validate everything. Insist that the business users (or whoever is asking for this feature) give you a list of what they care about. Write code to validate those things only.
If necessary, use the DFDL parser to catch errors. Again, the DFDL parser will almost always include the path to the problem field when it reports an error.
Explain to the business users (or whoever is asking for this feature) that doing any more than this will require custom code which will be expensive and time-consuming to implement, difficult to get right and difficult to maintain.

You may need to write some custom code to extract XPaths from the various types of XMLNSC and DFDL error message. That could be fiddly, but at least it is easy to put into a common library.