xml import Distinguish fields during XML import

[Quito]

Hi,

I'm using Jens Teich's XML to FMP (Drag n'Drop), which includes its own "Universal XSLT" to import XML. The resulting fields are named path, parent, node, value, and type.

The XML file can hold information from 1, 2, 3, 4 or more Medical Records. I've added a calculation to capture each Medical Record Number - MRN

(Case ( path = "MCCI_IN200100UV01/PORR_IN049016UV/controlActProcess/subject/investigationEvent/component/adverseEventAssessment/subject1/primaryRole/player1/asIdentifiedEntity/id/" and parent = "id" and node = "extension" and type = "Attribute" ; value ; "" )

to find the relevant value and send it to the Summary field. The problem I have is that there is still some granularity missing to distinguish the Medical Record Numbers, as the calculation will bring multiple MRNs into the same Summary field (because the calculation criteria are the same for all MRNs, see attachments). I cannot anticipate the structure of the Medical Record Number (the example includes a prefix to distinguish if the MRN is from a General Practitioner, a Specialist, a Hospital, or a Research Study, but I don't think I'll be that lucky in a Real World Environment). This is happening with other fields that also have repeating path, parent, node, and type patterns (such as the patient's age, Adverse Event date, weight, and height).

I'm guessing that the "Universal XSLT" is missing a field that distinguishes what goes where and allows to send the values to the correct Summary fields.

Any thoughts wil be well received. Thanks in advance!

[comment]

1 hour ago, Quito said:

I'm using Jens Teich's XML to FMP (Drag n'Drop), which includes its own "Universal XSLT" to import XML.

I am not familiar with this tool*.

IMHO, it is not possible to have a "universal" XSLT that would import any XML into Filemaker. There are far too many possible XML schemas for such undertaking.

My suggestion would be to write your own XSLT, tailored specifically to your input XML schema on the one hand and to your target FMP table fields on the other.

 

---
(*) OK, I found it at https://jensteich.de/universal-xslt/. It's not something I would use.

 

 

Edited March 20 by comment

[Quito]

Thank you, Comment,

OK, I'll go with your suggestion. First I'll tweak the "Universal XSLT", in hopes I can add the extra field without too much effort. Otherwise, an entirely new XSLT will turn into a huge task for me. The XSLT seems to be in a global field, not something I'm familiar with taking apart/replacing.

Best regards,

Daniel

[comment]

36 minutes ago, Quito said:

Otherwise, an entirely new XSLT will turn into a huge task for me.

Maybe you will recall something I told you here:

Quote

 in 80% of the cases of exporting from FMP you only need to know 20% (or less) of [the XSLT language].

The same is true for importing. And you can always ask for help here or at forums that focus on XSLT.

 

36 minutes ago, Quito said:

The XSLT seems to be in a global field, not something I'm familiar with taking apart/replacing.

The XSLT doesn't have to be in a global field. In fact, it cannot be in a global field while it's being used; it must be in a file located somewhere on your system. If you prefer, you can embed it in your solution (either in a global field or directly in the importing script) and export it to a file every time you need to use it. But that should happen only after it's been written and tested.  And it can just as well continue to live in a separate file.

 

Edited March 20 by comment

[Quito]

OK, so I looked at the XML and there are two unique identifiers for the MRN, namely, the id root (last numbers ending in 7, 8, 9, and 10) and the <code code= (numbered 1, 2, 3, and 4). The id root 2.16.840.1.113883.3.989.2.1.3.7 is on the record prior to GP12345567.

Maybe there's a way of processing the XML file to go into XML to FMP (Drag n'Drop) twice? Once, in the usual drag n'drop fashion, while the second script copies the entire XML and places it into a "MyXML_pasted_into_a_field" (or maybe even a global) field. I'm probably chasing my tail with the following workaround but, a simple Let could do the trick for most of the field clashes, without going into XSLT hell. Something like:

// First, a Substitute to remove all unnecessary spaces from the XML. Then

Let ( [

field = MyXML_pasted_into_a_field ;

begin = "<id root=\"2.16.840.1.113883.3.989.2.1.3.7\" extension=\"" ;

start = Position ( field ; begin ; 1 ; 1 ) + Length ( begin ) ;

end = Position ( field ; "\" /><code code=\"1\"" ; start ; 1 ) - 0 ] ;

Trim ( Substitute ( Middle ( field ; start ; end - start ) ; Char ( 10 ) ; "" ) ))

 

...and so on for the remaining MRNs. Below is the bit of XML that contains the four MRNs, after supposedly running the Substitute function to remove unnecessary spaces.

--------------------

<component typeCode="COMP"><adverseEventAssessment classCode="INVSTG" moodCode="EVN"><subject1 typeCode="SBJ"><primaryRole classCode="INVSBJ"><player1 classCode="PSN" determinerCode="INSTANCE"><name>ZXDR</name><administrativeGenderCode code="1" codeSystem="1.0.5218" /><birthTime value="20200202" /><deceasedTime value="20210303" /><asIdentifiedEntity classCode="IDENT"><id root="2.16.840.1.113883.3.989.2.1.3.7" extension="GP12345567" /><code code="1" codeSystem="2.16.840.1.113883.3.989.2.1.1.4" codeSystemVersion="2.0" /></asIdentifiedEntity><asIdentifiedEntity classCode="IDENT"><id root="2.16.840.1.113883.3.989.2.1.3.8" extension="SPE3456789" /><code code="2" codeSystem="2.16.840.1.113883.3.989.2.1.1.4" codeSystemVersion="2.0" /></asIdentifiedEntity><asIdentifiedEntity classCode="IDENT"><id root="2.16.840.1.113883.3.989.2.1.3.9" extension="HOS1234567" /><code code="3" codeSystem="2.16.840.1.113883.3.989.2.1.1.4" codeSystemVersion="2.0" /></asIdentifiedEntity><asIdentifiedEntity classCode="IDENT"><id root="2.16.840.1.113883.3.989.2.1.3.10" extension="RES9876543" /><code code="4" codeSystem="2.16.840.1.113883.3.989.2.1.1.4" codeSystemVersion="2.0" /></asIdentifiedEntity>

--------------------

Haven't tried it yet. Your insight on this subject is very important.

All the very best,

Daniel

Edited March 21 by Quito

[comment]

IMHO, you are attacking this from the wrong angle. Trying to parse XML using Filemaker's text functions is an exercise in frustration. You will never achieve the ease, and even more importantly, the reliability of a dedicated XML parser.

OTOH, Filemaker provides you with a built-in XML parser attached to an XSLT processor that allows you to ingest the data in the source XML directly into your solution, with no intermediary steps. So ... The front gate is open; there is no need to try and break in through the window. 

---
A side note:
For some special cases, where you do actually need to process XML data stored inside a Filemaker field, you can take advantage of the BaseElements plugin that provides some useful XML/XSLT functions.  For example, if you had the following well-formed* XML in the MyXML field:

<component typeCode="COMP">
  <adverseEventAssessment classCode="INVSTG" moodCode="EVN">
    <subject1 typeCode="SBJ">
      <primaryRole classCode="INVSBJ">
        <player1 classCode="PSN" determinerCode="INSTANCE">
          <name>ZXDR</name>
          <administrativeGenderCode code="1" codeSystem="1.0.5218"/>
          <birthTime value="20200202"/>
          <deceasedTime value="20210303"/>
          <asIdentifiedEntity classCode="IDENT">
            <id root="2.16.840.1.113883.3.989.2.1.3.7" extension="GP12345567"/>
            <code code="1" codeSystem="2.16.840.1.113883.3.989.2.1.1.4" codeSystemVersion="2.0"/>
          </asIdentifiedEntity>
          <asIdentifiedEntity classCode="IDENT">
            <id root="2.16.840.1.113883.3.989.2.1.3.8" extension="SPE3456789"/>
            <code code="2" codeSystem="2.16.840.1.113883.3.989.2.1.1.4" codeSystemVersion="2.0"/>
          </asIdentifiedEntity>
          <asIdentifiedEntity classCode="IDENT">
            <id root="2.16.840.1.113883.3.989.2.1.3.9" extension="HOS1234567"/>
            <code code="3" codeSystem="2.16.840.1.113883.3.989.2.1.1.4" codeSystemVersion="2.0"/>
          </asIdentifiedEntity>
          <asIdentifiedEntity classCode="IDENT">
            <id root="2.16.840.1.113883.3.989.2.1.3.10" extension="RES9876543"/>
            <code code="4" codeSystem="2.16.840.1.113883.3.989.2.1.1.4" codeSystemVersion="2.0"/>
          </asIdentifiedEntity>
        </player1>
      </primaryRole>
    </subject1>
  </adverseEventAssessment>
</component>

you could use the expression:

BE_XPathAll ( MyXML ; "//id/@extension" )

to return:

GP12345567
SPE3456789
HOS1234567
RES9876543

But again, I don't think this is the right path for you to follow.

---
(*) "Well-formed" means mainly that every start-tag has a corresponding end-tag and there is a single root element. A "bit of XML" like the one you posted will error out due to missing end-tags.

Edited March 21 by comment