OIC - Handling Unknown Record Types in OIC (Header / Detail / Footer Detection) | How to validate a string in yyyymmdd is in date format?

📌 Problem Statement

In one of our Oracle Integration Cloud (OIC) file-based integrations, the source file structure was unclear.

We were receiving records, but no explicit identifier was present to distinguish:

Header

Detail

Footer

However, we observed one consistent pattern: 👉 Detail records always contain a date in YYYYMMDD format at a fixed position.

💡 Solution Approach

We used this date pattern as a validation key to identify detail records.

✔️ Logic:

Extract substring from the record (specific position where date exists)

Convert it into proper date format (YYYY-MM-DD)

Compare with original substring

Count matching records

If count = 0 → No detail record → Fail validation

🛠️ OIC Implementation

Step 1: Extract Date from Record

Assume date starts at position 19 in the string:

Xpath

substring(ns30:Data, 19, 8)

Step 2: Convert to Proper Date Format

Xpath

concat(

  substring(ns30:Data, 19, 4), "-",

  substring(ns30:Data, 23, 2), "-",

  substring(ns30:Data, 25, 2)

)

Step 3: Validate Date Format

Xpath

xp20:format-dateTime(

  concat(

    substring(ns30:Data, 19, 4), "-",

    substring(ns30:Data, 23, 2), "-",

    substring(ns30:Data, 25, 2)

  ),

  "[Y0001][M01][D01]"

)

Step 4: Compare with Original Value

Xpath

xp20:format-dateTime(...) = substring(ns30:Data, 19, 8)

👉 If TRUE → Valid date → Detail record

Step 5: Count Detail Records

Xpath

count(

  $ReadRawFile/nsmpr0:ReadResponse/ns30:RawFileRowSet/ns30:RawFileRow[

    xp20:format-dateTime(

      concat(

        substring(ns30:Data, 19, 4), "-",

        substring(ns30:Data, 23, 2), "-",

        substring(ns30:Data, 25, 2)

      ),

      "[Y0001][M01][D01]"

    ) = substring(ns30:Data, 19, 8)

  ]

)

🚨 Final Validation Condition

Xpath

count(...) = 0

Meaning:

✅ If count > 0 → Detail records exist → Proceed

❌ If count = 0 → No detail records → Fail Integration

🎯 Key Benefits

No dependency on explicit record type indicators

Works with inconsistent file formats

Lightweight validation using XPath

Prevents downstream processing errors

⚡ Pro Tip

If date position may vary or data is dirty:

Use normalize-space() before substring

Add additional checks (length = 8, numeric check)

🧾 Conclusion

When file structure is ambiguous, pattern-based validation (like date detection) is a powerful technique in OIC.

This approach ensures that only valid detail records are processed, improving reliability and