We are currently working on the forum. For the short-term, all forum content will be in read-only format. We apologize for the interruption and look forward to collaborating with you shortly. All the best in your research!

XML to MS Access Not working in 3.11


I have been using the scripts here https://github.com/lindsay-stevens/openclinica_scripts/tree/master/OC_XML_to_SAS_R_CSV
to convert the XML extract into MS Access.

Since updating from 3.4 to 3.11 when i import the XML output from the scripts into Access it is creating a row for each item value, rather than one row containing all the values for single CRF.

Any idea? Has the extract CDISC ODM XML 1.3 Full with OpenClinica extensions format changed?




  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    I don't think the ODM XML extract has changed. I just tried the Access script against a fresh download from the demo JUNO database, which is running on OC 3.11. The XSL output imported to Access as expected, with one row per subject/event/event repeat/crf/item group/item group repeat; and the values from that combination in the columns.

    I also read through the relevant parts of the XSL and I can't see any condition that would cause items to appear as separate rows. There is a xsl:for-each that collects ItemData values from an ItemGroupData element and outputs them together.

    Although, I did find what seems to be a problem with study event repeat keys (SERK). In the for-each mentioned above, there's a test to check if there is a SERK, and if so, output it. Where it says 'xsl:if test="@StudyEventRepeatKey"' , it should probably say 'xsl:if test="../../@StudyEventRepeatKey"' - since the context is an ItemGroupData element the test as it is will always return false.

    That's my best guess. I can't be much more help than that since Community DataMart is my jam for data extraction: https://github.com/lindsay-stevens/openclinica_sqldatamart
  • skwalerskwaler Posts: 49
    edited November 2016
    Hi Lindsay,

    I just tried the JUNO extract and it worked fine for me.

    I wonder what could be different about my extracts. Apart from upgrading to OC 3.11 the database was moved to 9.5 from 8.4.
    Do you think it could be a an issue with postgresql 9.5?

    Do we know what version of postgres the JUNO demo is using?

  • skwalerskwaler Posts: 49

    Looking at my extract i can see what the problem is...

    each ItemData is being enclosed in a new ItemGroupData

    rather than all items in the one as it should

    Any ideas how this could be happening?

  • skwalerskwaler Posts: 49
    edited November 2016
    Looking into this further i have found that some OC instance XML extracts are working as expected and other instances not.

    All running OC 3.11 and the on the same PostgreSQL 9.5 database.

    Other formats like xls are working on all instances.

  • skwalerskwaler Posts: 49
    edited November 2016
    Further on this issue.

    Testing on an instance that is having the problem.

    1. Create a dataset and include all study items. The issue happens for all clinical data

    2. Create a small dataset, just one CRF from one event. It works fine.

    3. Take the dataset from (2) and add in extra CRFs from an event one by one. After the 5th or so CRF is added the issue appear. Though not for all clinical data.

    To be clear the issue is with the CDISC ODM XML 1.3 extract.

    Has anyone seen this before or have a clue?
  • kristiakkristiak Posts: 1,340 ✭✭✭
    Have you tried going back to PostgreSQL 8.x. I have tried OC 3.12 with PGSQL 9.5 and I have problem when importing a OC 3.6 backup file!
  • skwalerskwaler Posts: 49
    edited November 2016
    I restored by the database from postgres 9.5 back to 8.4 and the extracts work again (still running OC3.11)

    Does anyone know what the issue could be with 9.5?
    Is there any specific version supported?
    Settings i could be missing?

  • skwalerskwaler Posts: 49
    I found a page on the wiki mentioning issues with 9.x

    Much to my excitement I followed the steps however the extracts still are not working correctly.

    Getting closer...
  • kristiakkristiak Posts: 1,340 ✭✭✭
    There must be a reason why PGSQL version 9.x is not approved for OpenClinica use. Maybe Krikor can tell us?
  • skwalerskwaler Posts: 49
    I thought that since 3.10 PGSQL 9.x was supported

Sign In or Register to comment.