Originally posted by: SystemAdmin

I am receiving an xml file that is improperly structured for the process that requires the data. The process expects one instance of the data. However, I receive multiple records with repetitive data and some unique data. This all needs to be combined to send to another function to be processed. The following (Example 1) is a representation of the file. CaseNum is the key identifier. However, In order to properly process the data in its entirety I need the format to look like the second example. The CaseData, Case Person and ProgData is the same data in each correlating record and some records have the same APData.

Does anyone have a suggestion on how to handle this scenario? I do not have control over the creation of the incoming file so that is not an option. The on thought I had was to pre-parse the file and create an index file for each case number and have that to key off for direct data look up. What are your thoughts?

Example 1:
FILE 0:1
Record 0:M
..RecordHeader 0:1
..RecordBody 0:1
....CaseData 0:1
......CaseNum
......etc
......CasePerson 0:M
........Name
........etc
....ProgData 0:M
......Type
......etc
....APData 0:1
......A 0:1
........etc
......B 0:1
........etc
........B1 0:1
..........etc
......C 0:1
........etc

Example 2:
FILE 0:1
Record 0:M
..RecordHeader 0:1
..RecordBody 0:1
....CaseData 0:1
......CaseNum
......etc
......CasePerson 0:M
........Name
........etc
....ProgData 0:M
......Type
......etc
....APData 0:M
......A 0:1
........etc
......B 0:1
........etc
........B1 0:M
..........etc
......C 0:1
........etc
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: john.gibby

If the XML you are receiving does not match the XML Schema then the author of the XML needs to fix that. I don't understand trying to work with the XML data if it is not in conformance with the Schema. However, if for some reason, you must find a way to work with this data, why not modify the ranges for the APData & B1 groups to let the XML validate successfully.

John
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

It is in compliance with the agreed upon schema for the new process. However, I have been mandated to work with it as it is. I need it to be formatted as explained so the record can be processed as a whole. I tried to get them to re-author it, but they will not budge. So I need to get the pieces scattered between multiple records into a single record.
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Example 1 and Example 2 are the same - can you paste in the output you need?

It sounds like the input data is valid XML but structured differently from how your target application wants the data - is that right?

#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: janhess

I thought that but on closer inspection ....APData 0:M is different in example 2.
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

If I have two parents and three children the input will contain six records, instead of having one record with the AP (Adult Data) repeated and the relationship to the child (B1) repeated. Does this make sense?
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

I would use the UNIQUE function to identify all your unique parents and then use EXTRACT to attach the children to the right parent.
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Do you have an example?
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange