I am working with a batch of XML files that come every month. They all follow the same DOM tree structure and they do not come with schema files. Here is a sample:
<?xml version="1.0" encoding="ISO-8859-1" ?>
<REPORT><REPORT-DTL><REPORT-ID>PCRP60R1-C</REPORT-ID><REPORT-DATE>2020-10-01</REPORT-DATE><REPORT-NAME>OUTSIDE USE REPORT (PATIENTS WITH SIGNED CONSENT)</REPORT-NAME><REPORT-PERIOD-START>2020-09-01</REPORT-PERIOD-START><REPORT-PERIOD-END>2020-09-30</REPORT-PERIOD-END></REPORT-DTL>
<GROUP><GROUP-DTL><GROUP-ID>DoctorAGroup1234</GROUP-ID><GROUP-TYPE>HOSP</GROUP-TYPE><GROUP-NAME>COUNTY HOSP</GROUP-NAME></GROUP-DTL>
<PROVIDER><PROVIDER-DTL><PROVIDER-NUMBER>DoctorAID1234</PROVIDER-NUMBER><PROVIDER-LAST-NAME>DoctorALastname</PROVIDER-LAST-NAME><PROVIDER-FIRST-NAME>DoctorAFirstname</PROVIDER-FIRST-NAME><PROVIDER-MIDDLE-NAME>DoctorAMiddleName</PROVIDER-MIDDLE-NAME></PROVIDER-DTL>
<PATIENT><PATIENT-DTL><PATIENT-HEALTH-NUMBER>PatientANumber1234</PATIENT-HEALTH-NUMBER><PATIENT-LAST-NAME>PatientALastname</PATIENT-LAST-NAME><PATIENT-FIRST-NAME>PatientAFirstname</PATIENT-FIRST-NAME><PATIENT-BIRTHDATE>1941-02-11</PATIENT-BIRTHDATE><PATIENT-SEX>M</PATIENT-SEX></PATIENT-DTL>
<SERVICE-DTL1><SERVICE-LOC> </SERVICE-LOC><SERVICE-DATE>PatientAServiceDate2020-09-07</SERVICE-DATE><SERVICE-CODE>PatientAServiceCodeABC1</SERVICE-CODE><SERVICE-DESCRIPTION>PatientAServiceDescription-Facelift</SERVICE-DESCRIPTION><SERVICE-AMT>PatientAServiceAmount8.90</SERVICE-AMT></SERVICE-DTL1>
</PATIENT>
<PATIENT><PATIENT-DTL><PATIENT-HEALTH-NUMBER>PatientBNumber1235</PATIENT-HEALTH-NUMBER><PATIENT-LAST-NAME>PatientBLastname</PATIENT-LAST-NAME><PATIENT-FIRST-NAME>PatientBFirstname</PATIENT-FIRST-NAME><PATIENT-BIRTHDATE>1955-10-11</PATIENT-BIRTHDATE><PATIENT-SEX>F</PATIENT-SEX></PATIENT-DTL>
<SERVICE-DTL1><SERVICE-LOC> </SERVICE-LOC><SERVICE-DATE>PatientBServiceDate2020-12-08</SERVICE-DATE><SERVICE-CODE>PatientBServiceCodeABC2</SERVICE-CODE><SERVICE-DESCRIPTION>PatientBServiceDescription-Checkup</SERVICE-DESCRIPTION><SERVICE-AMT>PatientBServiceAmount10.50</SERVICE-AMT></SERVICE-DTL1>
</PATIENT>
<PATIENT><PATIENT-DTL><PATIENT-HEALTH-NUMBER>PatientCNumber1236</PATIENT-HEALTH-NUMBER><PATIENT-LAST-NAME>PatientCLastname</PATIENT-LAST-NAME><PATIENT-FIRST-NAME>PatientCFirstname</PATIENT-FIRST-NAME><PATIENT-BIRTHDATE>1965-02-07</PATIENT-BIRTHDATE><PATIENT-SEX>F</PATIENT-SEX></PATIENT-DTL>
<SERVICE-DTL1><SERVICE-LOC> </SERVICE-LOC><SERVICE-DATE>PatientCServiceDate2020-11-11</SERVICE-DATE><SERVICE-CODE>PatientCServiceCodeABC3</SERVICE-CODE><SERVICE-DESCRIPTION>PatientCServiceDescription-X-Ray</SERVICE-DESCRIPTION><SERVICE-AMT>PatientCServiceAmount18.00</SERVICE-AMT></SERVICE-DTL1>
</PATIENT>
<PATIENT><PATIENT-DTL><PATIENT-HEALTH-NUMBER>PatientDNumber1237</PATIENT-HEALTH-NUMBER><PATIENT-LAST-NAME>PatientDLastname</PATIENT-LAST-NAME><PATIENT-FIRST-NAME>PatientDFirstname</PATIENT-FIRST-NAME><PATIENT-BIRTHDATE>1975-07-09</PATIENT-BIRTHDATE><PATIENT-SEX>M</PATIENT-SEX></PATIENT-DTL>
<SERVICE-DTL1><SERVICE-LOC> </SERVICE-LOC><SERVICE-DATE>PatientDServiceDate2020-01-10</SERVICE-DATE><SERVICE-CODE>PatientDServiceCodeABC4</SERVICE-CODE><SERVICE-DESCRIPTION>PatientDServiceDescription-Nose Cleaning</SERVICE-DESCRIPTION><SERVICE-AMT>PatientDServiceAmount6.00</SERVICE-AMT></SERVICE-DTL1>
</PATIENT>
</PROVIDER>
</GROUP>
</REPORT>
Note the hierarchical structure of the code. All of the PATIENT data "belongs" to a parent PROVIDER node. Each PROCEDURE "belongs" to the PATIENT who supersedes it. The XML code describes these 4 interrelated tables.
My database software can't import these interrelated tables from an XML file -- my database app can't follow the hierarchy of the code.
Instead, I can import the data if it were "flattened", and the interrelated nodes were "unpacked" (and replicated). Here is how I would like my flattened table to look upon import. Yes, it has lots of redundancy -- the first 12 columns of this new table are identical for every record. (That's OK, I can eliminate the redundancies later; at this point, I just want to read in all the data.)
Here is the XML code that generated the flattened table in the image above:
<?xml version="1.0" encoding="ISO-8859-1" ?>
<REPORT>
<ImportedTable>
<REPORT-ID>PCRP60R1-C</REPORT-ID><REPORT-DATE>2020-10-01</REPORT-DATE><REPORT-NAME>OUTSIDE USE REPORT (PATIENTS WITH SIGNED CONSENT)</REPORT-NAME><REPORT-PERIOD-START>2020-09-01</REPORT-PERIOD-START><REPORT-PERIOD-END>2020-09-30</REPORT-PERIOD-END>
<GROUP-ID>DoctorAGroup1234</GROUP-ID><GROUP-TYPE>HOSP</GROUP-TYPE><GROUP-NAME>COUNTY HOSP</GROUP-NAME>
<PROVIDER-NUMBER>DoctorAID1234</PROVIDER-NUMBER><PROVIDER-LAST-NAME>DoctorALastname</PROVIDER-LAST-NAME><PROVIDER-FIRST-NAME>DoctorAFirstname</PROVIDER-FIRST-NAME><PROVIDER-MIDDLE-NAME>DoctorAMiddleName</PROVIDER-MIDDLE-NAME>
<PATIENT-HEALTH-NUMBER>PatientANumber1234</PATIENT-HEALTH-NUMBER><PATIENT-LAST-NAME>PatientALastname</PATIENT-LAST-NAME><PATIENT-FIRST-NAME>PatientAFirstname</PATIENT-FIRST-NAME><PATIENT-BIRTHDATE>1941-02-11</PATIENT-BIRTHDATE><PATIENT-SEX>M</PATIENT-SEX>
<SERVICE-LOC> </SERVICE-LOC><SERVICE-DATE>PatientAServiceDate2020-09-07</SERVICE-DATE><SERVICE-CODE>PatientAServiceCodeABC1</SERVICE-CODE><SERVICE-DESCRIPTION>PatientAServiceDescription-Facelift</SERVICE-DESCRIPTION><SERVICE-AMT>PatientAServiceAmount8.90</SERVICE-AMT>
</ImportedTable>
<ImportedTable>
<REPORT-ID>PCRP60R1-C</REPORT-ID><REPORT-DATE>2020-10-01</REPORT-DATE><REPORT-NAME>OUTSIDE USE REPORT (PATIENTS WITH SIGNED CONSENT)</REPORT-NAME><REPORT-PERIOD-START>2020-09-01</REPORT-PERIOD-START><REPORT-PERIOD-END>2020-09-30</REPORT-PERIOD-END>
<GROUP-ID>DoctorAGroup1234</GROUP-ID><GROUP-TYPE>HOSP</GROUP-TYPE><GROUP-NAME>COUNTY HOSP</GROUP-NAME>
<PROVIDER-NUMBER>DoctorAID1234</PROVIDER-NUMBER><PROVIDER-LAST-NAME>DoctorALastname</PROVIDER-LAST-NAME><PROVIDER-FIRST-NAME>DoctorAFirstname</PROVIDER-FIRST-NAME><PROVIDER-MIDDLE-NAME>DoctorAMiddleName</PROVIDER-MIDDLE-NAME>
<PATIENT-HEALTH-NUMBER>PatientBNumber1235</PATIENT-HEALTH-NUMBER><PATIENT-LAST-NAME>PatientBLastname</PATIENT-LAST-NAME><PATIENT-FIRST-NAME>PatientBFirstname</PATIENT-FIRST-NAME><PATIENT-BIRTHDATE>1955-10-11</PATIENT-BIRTHDATE><PATIENT-SEX>F</PATIENT-SEX>
<SERVICE-LOC> </SERVICE-LOC><SERVICE-DATE>PatientBServiceDate2020-12-08</SERVICE-DATE><SERVICE-CODE>PatientBServiceCodeABC2</SERVICE-CODE><SERVICE-DESCRIPTION>PatientBServiceDescription-Checkup</SERVICE-DESCRIPTION><SERVICE-AMT>PatientBServiceAmount10.50</SERVICE-AMT>
</ImportedTable>
<ImportedTable>
<REPORT-ID>PCRP60R1-C</REPORT-ID><REPORT-DATE>2020-10-01</REPORT-DATE><REPORT-NAME>OUTSIDE USE REPORT (PATIENTS WITH SIGNED CONSENT)</REPORT-NAME><REPORT-PERIOD-START>2020-09-01</REPORT-PERIOD-START><REPORT-PERIOD-END>2020-09-30</REPORT-PERIOD-END>
<GROUP-ID>DoctorAGroup1234</GROUP-ID><GROUP-TYPE>HOSP</GROUP-TYPE><GROUP-NAME>COUNTY HOSP</GROUP-NAME>
<PROVIDER-NUMBER>DoctorAID1234</PROVIDER-NUMBER><PROVIDER-LAST-NAME>DoctorALastname</PROVIDER-LAST-NAME><PROVIDER-FIRST-NAME>DoctorAFirstname</PROVIDER-FIRST-NAME><PROVIDER-MIDDLE-NAME>DoctorAMiddleName</PROVIDER-MIDDLE-NAME>
<PATIENT-HEALTH-NUMBER>PatientCNumber1236</PATIENT-HEALTH-NUMBER><PATIENT-LAST-NAME>PatientCLastname</PATIENT-LAST-NAME><PATIENT-FIRST-NAME>PatientCFirstname</PATIENT-FIRST-NAME><PATIENT-BIRTHDATE>1965-02-07</PATIENT-BIRTHDATE><PATIENT-SEX>F</PATIENT-SEX>
<SERVICE-LOC> </SERVICE-LOC><SERVICE-DATE>PatientCServiceDate2020-11-11</SERVICE-DATE><SERVICE-CODE>PatientCServiceCodeABC3</SERVICE-CODE><SERVICE-DESCRIPTION>PatientCServiceDescription-X-Ray</SERVICE-DESCRIPTION><SERVICE-AMT>PatientCServiceAmount18.00</SERVICE-AMT>
</ImportedTable>
<ImportedTable>
<REPORT-ID>PCRP60R1-C</REPORT-ID><REPORT-DATE>2020-10-01</REPORT-DATE><REPORT-NAME>OUTSIDE USE REPORT (PATIENTS WITH SIGNED CONSENT)</REPORT-NAME><REPORT-PERIOD-START>2020-09-01</REPORT-PERIOD-START><REPORT-PERIOD-END>2020-09-30</REPORT-PERIOD-END>
<GROUP-ID>DoctorAGroup1234</GROUP-ID><GROUP-TYPE>HOSP</GROUP-TYPE><GROUP-NAME>COUNTY HOSP</GROUP-NAME>
<PROVIDER-NUMBER>DoctorAID1234</PROVIDER-NUMBER><PROVIDER-LAST-NAME>DoctorALastname</PROVIDER-LAST-NAME><PROVIDER-FIRST-NAME>DoctorAFirstname</PROVIDER-FIRST-NAME><PROVIDER-MIDDLE-NAME>DoctorAMiddleName</PROVIDER-MIDDLE-NAME>
<PATIENT-HEALTH-NUMBER>PatientDNumber1237</PATIENT-HEALTH-NUMBER><PATIENT-LAST-NAME>PatientDLastname</PATIENT-LAST-NAME><PATIENT-FIRST-NAME>PatientDFirstname</PATIENT-FIRST-NAME><PATIENT-BIRTHDATE>1975-07-09</PATIENT-BIRTHDATE><PATIENT-SEX>M</PATIENT-SEX>
<SERVICE-LOC> </SERVICE-LOC><SERVICE-DATE>PatientDServiceDate2020-01-10</SERVICE-DATE><SERVICE-CODE>PatientDServiceCodeABC4</SERVICE-CODE><SERVICE-DESCRIPTION>PatientDServiceDescription-Nose Cleaning</SERVICE-DESCRIPTION><SERVICE-AMT>PatientDServiceAmount6.00</SERVICE-AMT>
</ImportedTable>
</REPORT>
So, my question is whether XSLT can transform from the top XML file into the bottom one. Note that, in the converted XML file, I am only interested in retaining nodes containing text. (Any non-text nodes from the original file can safely be ignored in this transformation.) Is there code that could make this transformation? (Note: I have been reading a number of threads dealing with XML conversions but this situation is unusual because of the relational structure of this data set. If this question has been answered elsewhere, please let me know!)
Thanks very much,
Ron
Flattening a hierarchical XML is a common task. You simply create an element for each record in the most atomic table and include the data from its parent tables. As it happens, the structure of your example XML input makes this very easy:
XSLT 1.0
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>
<xsl:template match="/REPORT">
<xsl:copy>
<xsl:variable name="report-data" select="REPORT-DTL/*" />
<xsl:for-each select="GROUP">
<xsl:variable name="group-data" select="GROUP-DTL/*" />
<xsl:for-each select="PROVIDER">
<xsl:variable name="provider-data" select="PROVIDER-DTL/*" />
<xsl:for-each select="PATIENT">
<ImportedTable>
<xsl:copy-of select="$report-data"/>
<xsl:copy-of select="$group-data"/>
<xsl:copy-of select="$provider-data"/>
<xsl:copy-of select="PATIENT-DTL/*"/>
<xsl:copy-of select="SERVICE-DTL1/*"/>
</ImportedTable>
</xsl:for-each>
</xsl:for-each>
</xsl:for-each>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>