Using XSLT to convert XML to fixed length text file

Question

I've an XML which I need to convert to the fixed width style text file and have to use the XSLT method to do it.

I'm a first time XSLT user and I've got my head about the basics but there is certain aspects of this source XML that is making it difficult...at least for me.

<Prescription>
<Drugs>
    <Drug id="1">
        <DrugName>Red Tablets</DrugName>
    </Drug>
    <Drug id="2">
        <DrugName>Blue Tablets</DrugName>
    </Drug>
</Drugs>
<Patients>
    <Patient id="20">
        <Surname>Doe</Surname>
        <Forenames>John</Forenames>
        <Items>
            <Item>
                <ProductID>1</ProductID>
                <AdminEvent date="2016-05-11" hour="7" qty="1"/>
                <AdminEvent date="2016-05-12" hour="7" qty="1"/>
            </Item>
        </Items>
    </Patient>
    <Patient id="50">
        <Surname>Doe</Surname>
        <Forenames>Jane</Forenames>
        <Items>
            <Item>
                <ProductID>2</ProductID>
                <AdminEvent date="2016-05-11" hour="7" qty="1"/>
                <AdminEvent date="2016-05-12" hour="7" qty="1"/>
            </Item>
        </Items>
    </Patient>
<Patients>

Here is a prescription with 2 drugs, and two patients. Patient John has Red Tablets for 2 days at 7am and Jane takes Blue tablets. I need to get this into a plain text file like:

[ForeName]+[Surname]+[DrugName]+[DrugID]+[Hour]+[Qty]+[Date]

So in this example:

John Doe    Blue Tablets    1   7   1   2016-05-11
John Doe    Blue Tablets    1   7   1   2016-05-12
Jane Doe    Red Tablets     2   7   1   2016-05-11
Jane Doe    Red Tablets     2   7   1   2016-05-12

I'm strggling with the concept of: a) Referring to a different branch (EG looking back from Item element back up to Drug) b) Only getting the DrugName for the ID in in the Item section

Hope all this makes sense!

Answer 1

XSLT 1.0

This transform will dynamically adjust the field length for patient and drug names... if they are fixed you may simplify this more.

<xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:msxsl="urn:schemas-microsoft-com:xslt"
                exclude-result-prefixes="msxsl"
>
  <xsl:output method="text" indent="yes"/>

  <xsl:variable name="name-max-length">
    <xsl:for-each select="//Patient">
      <xsl:sort select="string-length(concat(Forenames,' ',Surname))" data-type="number" />
      <xsl:if test="position() = last()">
        <xsl:value-of select="string-length(concat(Forenames,' ',Surname))" />
      </xsl:if>
    </xsl:for-each>
  </xsl:variable>

  <xsl:variable name="drug-max-length">
    <xsl:for-each select="//DrugName">
      <xsl:sort select="string-length(.)" data-type="number" />
      <xsl:if test="position() = last()">
        <xsl:value-of select="string-length(.)" />
      </xsl:if>
    </xsl:for-each>
  </xsl:variable>

  <xsl:template match="/">
    <xsl:apply-templates select="//AdminEvent"/>
  </xsl:template>

  <xsl:template match="AdminEvent">
    <xsl:variable name="name" select="concat(ancestor::Patient/Forenames,' ',ancestor::Patient/Surname)" />

    <xsl:variable name="productId" select="ancestor::Item/ProductID/text()"/>
    <xsl:variable name="drug" select="ancestor::Prescription/Drugs/Drug[@id=$productId]/DrugName"/>

    <xsl:value-of select="substring(concat($name,'                    '),1,($name-max-length + 4))"/>
    <xsl:value-of select="substring(concat($drug,'                    '),1,($drug-max-length + 4))"/>

    <xsl:value-of select="substring(concat($productId,'                    '),1,5)"/>

    <xsl:value-of select="substring(concat(@hour,'                    '),1,5)"/>
    <xsl:value-of select="substring(concat(@qty,'                    '),1,5)"/>
    <xsl:value-of select="substring(concat(@date,'                    '),1,10)"/>
    <xsl:text>&#10;</xsl:text>
  </xsl:template>
</xsl:stylesheet>

Results

John Doe    Red Tablets     1    7    1    2016-05-11
John Doe    Red Tablets     1    7    1    2016-05-12
Jane Doe    Blue Tablets    2    7    1    2016-05-11
Jane Doe    Blue Tablets    2    7    1    2016-05-12

Answer 2

Here is a XSLT that works for your sample:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>

<xsl:template match="/">
  <xsl:apply-templates select="//AdminEvent"/>
</xsl:template>

<xsl:template match="AdminEvent">
  <xsl:value-of select="ancestor::Patient/Forenames"/>
  <xsl:text> </xsl:text>
  <xsl:value-of select="ancestor::Patient/Surname"/>
  <xsl:text> '</xsl:text>
  <xsl:variable name="pid" select="ancestor::Item/ProductID/text()"/>
  <xsl:value-of select="ancestor::Prescription/Drugs/Drug[@id=$pid]/DrugName"/>
  <xsl:text>'
</xsl:text>
</xsl:template>

</xsl:stylesheet>

Note that I match for each element that should produce an output line (AdminSample) and then gather the data based on the current node.

Through ancestor I go upwards from the current AdminSample node till I find the right parent node (Patient), then go down again from there with a bracketed search condition on the Drug node to select the correct one matching the ProductID stored in the pid variable. Read about XPath axes here and about XSLT variables here.

Adding the other needed values from the AdminEvent itself should be trivial for you now :-)

Answer 3

Using XSLT 2.0 you can do it rather compact with

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema" exclude-result-prefixes="xs" version="2.0">

    <xsl:output method="text"/>

    <xsl:key name="drug-by-id" match="Drugs/Drug" use="@id"/>

    <xsl:template match="/">
        <xsl:apply-templates select="//AdminEvent"/>
    </xsl:template>

    <xsl:template match="AdminEvent">
        <xsl:value-of
            select="ancestor::Patient/concat(Forenames, ' ', Surname), key('drug-by-id', ../ProductID)/DrugName, ../ProductID, @qty, @date"/>
        <xsl:text>&#10;</xsl:text>
    </xsl:template>

</xsl:stylesheet>

Answer 4

I'm strggling with the concept of: a) Referring to a different branch (EG looking back from Item element back up to Drug) b) Only getting the DrugName for the ID in in the Item section

This is easy to solve by using a key to lookup the drug referenced by ProductID. For example, the following stylesheet:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="UTF-8"/>

<xsl:key name="drug" match="Drug" use="@id" />

<xsl:template match="Prescription">
    <xsl:for-each select="Patients/Patient">
        <xsl:variable name="surname" select="Surname" />
        <xsl:variable name="forename" select="Forenames" />
        <xsl:for-each select="Items/Item">
            <xsl:variable name="drugname" select="key('drug', ProductID)/DrugName" />
            <xsl:for-each select="AdminEvent">
                <xsl:value-of select="$surname" />
                <xsl:text> | </xsl:text>
                <xsl:value-of select="$forename" />
                <xsl:text> | </xsl:text>
                <xsl:value-of select="$drugname" />
                <xsl:text> | </xsl:text>
                <xsl:value-of select="@qty" />
                <xsl:text> | </xsl:text>
                <xsl:value-of select="@date" />
                <xsl:text>&#10;</xsl:text>
            </xsl:for-each>
        </xsl:for-each>
    </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

will return:

Doe | John | Red Tablets | 1 | 2016-05-11
Doe | John | Red Tablets | 1 | 2016-05-12
Doe | Jane | Blue Tablets | 1 | 2016-05-11
Doe | Jane | Blue Tablets | 1 | 2016-05-12

I'd suggest you ask a separate question about producing a fixed-width file.