XSLT to change XML in MS ACCESS import

Question

I need to import the xml file into access and I should do the transformations (XML -> XML) and to do that Access calls an XSLT file.

Then my source file is:

<?xml version="1.0" encoding="ISO-8859-1" ?>
<F1Project>
<File>piloti.php</File>
<Fetchdate>2014-05-23 11:37:41</Fetchdate>
<IdTeam>614</IdTeam>
<Training>
<TrainingFirstSkill>accelerazione</TrainingFirstSkill>
<TrainingSecondSkill>tecnica</TrainingSecondSkill>
</Training>
<TalentScout>
<TalentScoutLevel>16</TalentScoutLevel>
<TalentScoutFunding>25000</TalentScoutFunding>
</TalentScout>
<Drivers>
<Driver Index="1">
<DriverId>357352</DriverId>
<DriverName>Doukas</DriverName>
<DriverSurname>Nastos</DriverSurname>
<DriverDPI>55344</DriverDPI>
</Driver>
<Driver Index="2">
<DriverId>539134</DriverId>
<DriverName>Jurica</DriverName>
<DriverSurname>Andonovic</DriverSurname>
<DriverDPI>1406</DriverDPI>
</Driver>
<Driver Index="3">
<DriverId>473147</DriverId>
<DriverName>Tommaso</DriverName>
<DriverSurname>Galea</DriverSurname>
<DriverDPI>5553</DriverDPI>
</Driver>
</Drivers>
</F1Project>

Importing it into MSAccess, it creates several tables for each element and so far everything is ok.

I would like to have in each table the element "data" (which the original xml is reported only in a table).

Then there is the problem of "index" which are not always 3, may be more or less, and even in this case would need the date in each element ...

Expected output:

<?xml version="1.0" encoding="ISO-8859-1" ?>
<F1Project>
<File>piloti.php</File>
<Fetchdate>2014-05-23 11:37:41</Fetchdate>
<IdTeam>614</IdTeam>
<Training>
***<Fetchdate>2014-05-23 11:37:41</Fetchdate>***
<TrainingFirstSkill>accelerazione</TrainingFirstSkill>
<TrainingSecondSkill>tecnica</TrainingSecondSkill>
</Training>
<TalentScout>
***<Fetchdate>2014-05-23 11:37:41</Fetchdate>***
<TalentScoutLevel>16</TalentScoutLevel>
<TalentScoutFunding>25000</TalentScoutFunding>
</TalentScout>
<Drivers>
<Driver Index="1">
***<Fetchdate>2014-05-23 11:37:41</Fetchdate>***
<DriverId>357352</DriverId>
<DriverName>Doukas</DriverName>
<DriverSurname>Nastos</DriverSurname>
<DriverDPI>55344</DriverDPI>
</Driver>
<Driver Index="2">
***<Fetchdate>2014-05-23 11:37:41</Fetchdate>***
<DriverId>539134</DriverId>
<DriverName>Jurica</DriverName>
<DriverSurname>Andonovic</DriverSurname>
<DriverDPI>1406</DriverDPI>
</Driver>
<Driver Index="3">
***<Fetchdate>2014-05-23 11:37:41</Fetchdate>***
<DriverId>473147</DriverId>
<DriverName>Tommaso</DriverName>
<DriverSurname>Galea</DriverSurname>
<DriverDPI>5553</DriverDPI>
</Driver>
***<Driver Index="n">
<Fetchdate>2014-05-23 11:37:41</Fetchdate>
<DriverId>####</DriverId>
<DriverName>xxxx</DriverName>
<DriverSurname>yyyy</DriverSurname>
<DriverDPI>####</DriverDPI>
</Driver>***
</Drivers>
</F1Project>

Who can help me to generate an XSLT that meets my needs? I hope I have explained my problem clearly thanks

Answer 1

You mostly need the identity transform, which simply copies the input XML to the output document. Apart from that, you can catch the relevant child nodes, <Training/> and <Driver/>, and attach the needed <Fetchdate/> to them.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="@*|node()">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="Training|Driver">
    <xsl:copy>
      <xsl:apply-templates select="@*"/>
      <xsl:copy-of select="ancestor::F1Project/Fetchdate"/>
      <xsl:apply-templates select="node()"/>
    </xsl:copy>
  </xsl:template>
</xsl:stylesheet>

There are two templates: one that matches every node, and one that matches the nodes that need the <Fetchdate/> added. They both copy the existing nodes to the output document, but in the second one, it copies:

1. any attributes of the node
2. the <Fetchdate/>, which it finds by visiting its <F1Project/> ancestor and finding its date
3. the children nodes

That stylesheet produces this output when run on your example data:

<F1Project>
  <File>piloti.php</File>
  <Fetchdate>2014-05-23 11:37:41</Fetchdate>
  <IdTeam>614</IdTeam>
  <Training>
    <Fetchdate>2014-05-23 11:37:41</Fetchdate>
    <TrainingFirstSkill>accelerazione</TrainingFirstSkill>
    <TrainingSecondSkill>tecnica</TrainingSecondSkill>
  </Training>
  <TalentScout>
    <TalentScoutLevel>16</TalentScoutLevel>
    <TalentScoutFunding>25000</TalentScoutFunding>
  </TalentScout>
  <Drivers>
    <Driver Index="1">
      <Fetchdate>2014-05-23 11:37:41</Fetchdate>
      <DriverId>357352</DriverId>
      <DriverName>Doukas</DriverName>
      <DriverSurname>Nastos</DriverSurname>
      <DriverDPI>55344</DriverDPI>
    </Driver>
    <Driver Index="2">
      <Fetchdate>2014-05-23 11:37:41</Fetchdate>
      <DriverId>539134</DriverId>
      <DriverName>Jurica</DriverName>
      <DriverSurname>Andonovic</DriverSurname>
      <DriverDPI>1406</DriverDPI>
    </Driver>
    <Driver Index="3">
      <Fetchdate>2014-05-23 11:37:41</Fetchdate>
      <DriverId>473147</DriverId>
      <DriverName>Tommaso</DriverName>
      <DriverSurname>Galea</DriverSurname>
      <DriverDPI>5553</DriverDPI>
    </Driver>
  </Drivers>
</F1Project>

This is a simple transformation. If you have any questions, please ask.