Create JSON file using ODI

Christyxo

I am trying to create a JSON file populated by ODI mappings and created with the CREATE FILE procedure, but I can't generate the file at the end of the process as I keep getting the following error:

ODI-1228: Task Procedure-PRC_JSON_CONTACT-Create File fails on the target connection CFILE_CONTACT_local.
Caused By: java.sql.SQLException: ODI-40768: Could not save the file C:\Oracle\DEV\response\contact.json because a class java.sql.SQLException occurred and said: java.sql.SQLException: java.sql.SQLException: oracle.odi.jdbc.driver.xml.io.exception.PipelineStageException: ORABPEL-15236
Translation Failure.
Failed to translate XML to JSON. Start of root element expected.
The data does not conform to the NXSD schema. Please correct the problem.

The topology is ComplexFile with a DTD file created using the Native Schema Builder with the translation type = json, schema name = XSDSCHEMA, standalone = true.

I reversed engineered the model successful, with 3 datastores being generated; ROOTELEMENT, CONTACT, and OPTIN, and I can use this model as a source to load the data into tables in the DB successfully, but not as a target.

I have 3 separate ODI mappings which load the results into these 3 tables, and the execution of these is successful. Each mapping uses Control Append, LKM SQL Multi-Connect, and IKM SQL to SQL Control Append. The operation logs shows the expected number of inserts in these mappings. I do not want to populate every field in the mapping but as with other issues on the complex file, NULLS can cause this process to fail so I've also tried by adding a valid output to every field in the mapping and still get the error. I don't really understand the logic of the ORDER columns but I've added sequential numbers that align with the order of the elements in the XSD.

The 3 mappings are placed inside 1 package, with a starting procedure running SYNCRONIZE FROM FILE, followed by the 3 mappings, followed by the procedure CREATE FILE "C:\Oracle\DEV\response\contact.json" FROM SCHEMA "XSDSCHEMA". It's the create file step is where the process fails with the above error.

The same error occurs even if I don't run the mappings in the package.

The same error occurs if I use the JSON model as both source and target in a mapping before creating the file.

I think the problem is specifically

Failed to translate XML to JSON. Start of root element expected.

but I don't know how to debug this of start to understand where this is going wrong. Does anyone have any thoughts?