Search code examples
ms-accessxsltimportodkxlm

Importing ODK XML file to MS Access using XLST transformation

I'm trying to import a submission.xml file generated from an ODK survey into Access. I'm using the 'XML import' function in MS Access and need to transform the data so that the each data set of a node that will be imported to a table will contain the ID of the survey.

I have a XML with the survey data that looks like this:

<SoLa_Tu_Insp_2016-03-13 id="SOLA-160313"
    instanceID="uuid:63c27738-df02-4298-9090-7ab96d4e1ab2"
    submissionDate="2016-04-08T02:11:47.600Z"
    isComplete="true"
    markedAsCompleteDate="2016-04-08T02:13:12.322Z"
    xmlns="http://opendatakit.org/submissions">
<start>2016-04-08T23:47:50.615Z</start>
<end>2016-04-08T02:11:30.954Z</end>
<deviceid>353375050176865</deviceid>
<telnr />
<insp>
    <objekt>25</objekt>
    <objdel>212</objdel>
    <objdel-tx>Bronstunneln</objdel-tx>
    <datum>2016-04-08</datum>
    <sign>MFR RLS</sign>
    <anm>8</anm>
</insp>
<skdr>
    <skd>
        <metod>100</metod>
        <ts>
            <ts-nr>523</ts-nr>
            <ts-typskada>Lucka - L&#246;s -  Takelement</ts-typskada>
            <ts-kdel>15</ts-kdel>
            <ts-kelem>1530</ts-kelem>
            <ts-mtrl>13</ts-mtrl>
            <ts-typ>35</ts-typ>
            <ts-orsak>999</ts-orsak>
            <ts-aktivitet>87</ts-aktivitet>
            <ts-enhet>st</ts-enhet>
            <ts-aktivitet-tx>&#197;tdragning</ts-aktivitet-tx>
            <ts-mangd>1.0000000000</ts-mangd>
        </ts>
        <ovr>
            <ovr-kdel />
            <ovr-kdel-tx />
            <ovr-kelem />
            <ovr-kelem-tx />
            <ovr-mtrl />
            <ovr-mtrl-tx />
            <ovr-typ />
            <ovr-typ-tx />
            <ovr-orsak />
            <ovr-aktivitet />
            <ovr-aktivitet-tx />
            <ovr-enhet />
            <ovr-mangd />
        </ovr>
        <kdel>15</kdel>
        <kelem>1530</kelem>
        <mtrl>13</mtrl>
        <typ>35</typ>
        <orsak>999</orsak>
        <skd-tx>G&#229;ng/inspektionsbrygga etc / Lucka/d&#246;rr / Rostfritt st&#229;l / L&#246;s / &#214;vrigt</skd-tx>
        <tk>3</tk>
        <anm>Luckan h&#229;ller p&#229; att ramla av</anm>
        <aktivitet>87</aktivitet>
        <mangd>1</mangd>
        <enhet>st</enhet>
        <sekt1>675</sekt1>
        <sekt2 />
        <lage>VT</lage>
        <lage-tx />
        <bildskada>1460073108135.jpg</bildskada>
        <bildnr />
    </skd>
    <skd>
        <metod>100</metod>
        <ts>
            <ts-nr>321</ts-nr>
            <ts-typskada>f.a. sprutbetong - L&#228;ckage - Tak</ts-typskada>
            <ts-kdel>6</ts-kdel>
            <ts-kelem>665</ts-kelem>
            <ts-mtrl>6</ts-mtrl>
            <ts-typ>50</ts-typ>
            <ts-orsak>129</ts-orsak>
            <ts-aktivitet>45</ts-aktivitet>
            <ts-enhet>kg</ts-enhet>
            <ts-aktivitet-tx>Injektering</ts-aktivitet-tx>
            <ts-mangd>5.0000000000</ts-mangd>
        </ts>
        <ovr>
            <ovr-kdel />
            <ovr-kdel-tx />
            <ovr-kelem />
            <ovr-kelem-tx />
            <ovr-mtrl />
            <ovr-mtrl-tx />
            <ovr-typ />
            <ovr-typ-tx />
            <ovr-orsak />
            <ovr-aktivitet />
            <ovr-aktivitet-tx />
            <ovr-enhet />
            <ovr-mangd />
        </ovr>
        <kdel>6</kdel>
        <kelem>665</kelem>
        <mtrl>6</mtrl>
        <typ>50</typ>
        <orsak>129</orsak>
        <skd-tx>Huvudb&#228;rverk / Tak ytf&#246;rst&#228;rkning / Fiberarmerad sprutbetong / L&#228;ckage / Vatten</skd-tx>
        <tk>2</tk>
        <anm>3/s</anm>
        <aktivitet>45</aktivitet>
        <mangd>5</mangd>
        <enhet>kg</enhet>
        <sekt1>694</sekt1>
        <sekt2>694</sekt2>
        <lage>CT</lage>
        <lage-tx>Kalk, j&#228;rn,mangan utf&#228;lning droppar rikligt</lage-tx>
        <bildskada />
        <bildnr>4517</bildnr>
    </skd>
    <skd>
        <metod>100</metod>
        <ts>
            <ts-nr>321</ts-nr>
            <ts-typskada>f.a. sprutbetong - L&#228;ckage - Tak</ts-typskada>
            <ts-kdel>6</ts-kdel>
            <ts-kelem>665</ts-kelem>
            <ts-mtrl>6</ts-mtrl>
            <ts-typ>50</ts-typ>
            <ts-orsak>129</ts-orsak>
            <ts-aktivitet>45</ts-aktivitet>
            <ts-enhet>kg</ts-enhet>
            <ts-aktivitet-tx>Injektering</ts-aktivitet-tx>
            <ts-mangd />
        </ts>
        <ovr>
            <ovr-kdel />
            <ovr-kdel-tx />
            <ovr-kelem />
            <ovr-kelem-tx />
            <ovr-mtrl />
            <ovr-mtrl-tx />
            <ovr-typ />
            <ovr-typ-tx />
            <ovr-orsak />
            <ovr-aktivitet />
            <ovr-aktivitet-tx />
            <ovr-enhet />
            <ovr-mangd />
        </ovr>
        <kdel>6</kdel>
        <kelem>665</kelem>
        <mtrl>6</mtrl>
        <typ>50</typ>
        <orsak>129</orsak>
        <skd-tx>Huvudb&#228;rverk / Tak ytf&#246;rst&#228;rkning / Fiberarmerad sprutbetong / L&#228;ckage / Vatten</skd-tx>
        <tk>1</tk>
        <anm>Kalk, j&#228;rn,mangan utf&#228;lning</anm>
        <aktivitet>45</aktivitet>
        <mangd />
        <enhet>kg</enhet>
        <sekt1>698</sekt1>
        <sekt2 />
        <lage>VT</lage>
        <lage-tx>8/sek</lage-tx>
        <bildskada />
        <bildnr>4519-4520</bildnr>
    </skd>
    <skd>
        <metod>100</metod>
        <ts>
            <ts-nr>513</ts-nr>
            <ts-typskada>Lucka - Defekt - Takelement</ts-typskada>
            <ts-kdel>15</ts-kdel>
            <ts-kelem>1530</ts-kelem>
            <ts-mtrl>13</ts-mtrl>
            <ts-typ>48</ts-typ>
            <ts-orsak>999</ts-orsak>
            <ts-aktivitet>88</ts-aktivitet>
            <ts-enhet>st</ts-enhet>
            <ts-aktivitet-tx>&#214;versyn</ts-aktivitet-tx>
            <ts-mangd />
        </ts>
        <ovr>
            <ovr-kdel />
            <ovr-kdel-tx />
            <ovr-kelem />
            <ovr-kelem-tx />
            <ovr-mtrl />
            <ovr-mtrl-tx />
            <ovr-typ />
            <ovr-typ-tx />
            <ovr-orsak />
            <ovr-aktivitet />
            <ovr-aktivitet-tx />
            <ovr-enhet />
            <ovr-mangd />
        </ovr>
        <kdel>15</kdel>
        <kelem>1530</kelem>
        <mtrl>13</mtrl>
        <typ>48</typ>
        <orsak>999</orsak>
        <skd-tx>G&#229;ng/inspektionsbrygga etc / Lucka/d&#246;rr / Rostfritt st&#229;l / Defekt / &#214;vrigt</skd-tx>
        <tk>2</tk>
        <anm>G&#229;r ej skruva saknas 2st skruvar</anm>
        <aktivitet>88</aktivitet>
        <mangd />
        <enhet>st</enhet>
        <sekt1>653</sekt1>
        <sekt2 />
        <lage>HT</lage>
        <lage-tx>Nya skruvh&#229;l luckan som matchar</lage-tx>
        <bildskada />
        <bildnr>4523-4521</bildnr>
    </skd>
    <skd>
        <metod>100</metod>
        <ts>
            <ts-nr>513</ts-nr>
            <ts-typskada>Lucka - Defekt - Takelement</ts-typskada>
            <ts-kdel>15</ts-kdel>
            <ts-kelem>1530</ts-kelem>
            <ts-mtrl>13</ts-mtrl>
            <ts-typ>48</ts-typ>
            <ts-orsak>999</ts-orsak>
            <ts-aktivitet>88</ts-aktivitet>
            <ts-enhet>st</ts-enhet>
            <ts-aktivitet-tx>&#214;versyn</ts-aktivitet-tx>
            <ts-mangd>1.0000000000</ts-mangd>
        </ts>
        <ovr>
            <ovr-kdel />
            <ovr-kdel-tx />
            <ovr-kelem />
            <ovr-kelem-tx />
            <ovr-mtrl />
            <ovr-mtrl-tx />
            <ovr-typ />
            <ovr-typ-tx />
            <ovr-orsak />
            <ovr-aktivitet />
            <ovr-aktivitet-tx />
            <ovr-enhet />
            <ovr-mangd />
        </ovr>
        <kdel>15</kdel>
        <kelem>1530</kelem>
        <mtrl>13</mtrl>
        <typ>48</typ>
        <orsak>999</orsak>
        <skd-tx>G&#229;ng/inspektionsbrygga etc / Lucka/d&#246;rr / Rostfritt st&#229;l / Defekt / &#214;vrigt</skd-tx>
        <tk>2</tk>
        <anm>Luckan beh&#246;vs passas om</anm>
        <aktivitet>88</aktivitet>
        <mangd>1</mangd>
        <enhet>st</enhet>
        <sekt1>681</sekt1>
        <sekt2 />
        <lage>VT</lage>
        <lage-tx>TV3 tidigare 2 skruv har s&#228;tts men m&#229;det &#229;tg&#228;rdas</lage-tx>
        <bildskada />
        <bildnr>4527</bildnr>
    </skd>
    <skd>
        <metod>100</metod>
        <ts>
            <ts-nr>513</ts-nr>
            <ts-typskada>Lucka - Defekt - Takelement</ts-typskada>
            <ts-kdel>15</ts-kdel>
            <ts-kelem>1530</ts-kelem>
            <ts-mtrl>13</ts-mtrl>
            <ts-typ>48</ts-typ>
            <ts-orsak>999</ts-orsak>
            <ts-aktivitet>88</ts-aktivitet>
            <ts-enhet>st</ts-enhet>
            <ts-aktivitet-tx>&#214;versyn</ts-aktivitet-tx>
            <ts-mangd>1.0000000000</ts-mangd>
        </ts>
        <ovr>
            <ovr-kdel />
            <ovr-kdel-tx />
            <ovr-kelem />
            <ovr-kelem-tx />
            <ovr-mtrl />
            <ovr-mtrl-tx />
            <ovr-typ />
            <ovr-typ-tx />
            <ovr-orsak />
            <ovr-aktivitet />
            <ovr-aktivitet-tx />
            <ovr-enhet />
            <ovr-mangd />
        </ovr>
        <kdel>15</kdel>
        <kelem>1530</kelem>
        <mtrl>13</mtrl>
        <typ>48</typ>
        <orsak>999</orsak>
        <skd-tx>G&#229;ng/inspektionsbrygga etc / Lucka/d&#246;rr / Rostfritt st&#229;l / Defekt / &#214;vrigt</skd-tx>
        <tk>3</tk>
        <anm />
        <aktivitet>88</aktivitet>
        <mangd>1</mangd>
        <enhet>st</enhet>
        <sekt1>710</sekt1>
        <sekt2 />
        <lage>HT</lage>
        <lage-tx>Nedre skruvh&#229;l passar inte</lage-tx>
        <bildskada />
        <bildnr>4543-4542</bildnr>
    </skd>
</skdr>
<ritningar>212B2B01 212B2B02 212B2B03 212B2B04</ritningar>
<skd-antal>6</skd-antal>
<sekt-min>653</sekt-min>
<sekt-max>710</sekt-max>
<tk-min>1</tk-min>
<tk-max>3</tk-max>
<n0:meta xmlns:n0="http://openrosa.org/xforms">
    <n0:instanceID>uuid:63c27738-df02-4298-9090-7ab96d4e1ab2</n0:instanceID>
    <n0:instanceName>S&#246;L&#228;-25-212-2016-04-08-MFR RLS-4ae669a1-7d89-424a-bfab-26d506da0604</n0:instanceName>
</n0:meta>

At each 'skd' node I want to add the 'instanceID' attribute of the root element so that each 'skd' node will look something like:

<skdr>
    <skd>
        <id>uuid:63c27738-df02-4298-9090-7ab96d4e1ab2</id>
        <metod>100</metod>

So I'm trying to use this XSL-file for the transformation, which is not working as intended:

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

<xsl:output indent="yes" encoding="utf-8"/>
<xsl:strip-space elements="*"/>

<xsl:template match="/">
    <dataroot>
        <xsl:apply-templates select="@*|node()"/>
    </dataroot>
</xsl:template>

<xsl:template match="@*|node()">
    <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
</xsl:template>

<xsl:template match="//skd">
    <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
            <ID><xsl-copy-of select="/SoLa_Tu_Insp_2016-03-13/@instanceID"/></ID>
        <xsl:apply-templates select="node()"/>
    </xsl:copy>

</xsl:template>

The result I get when using the above transformation is like this:

    <?xml version="1.0" encoding="utf-8"?>
<dataroot>
  <SoLa_Tu_Insp_2016-03-13 xmlns="http://opendatakit.org/submissions" id="SOLA-160313" instanceID="uuid:63c27738-df02-4298-9090-7ab96d4e1ab2" submissionDate="2016-04-08T02:11:47.600Z" isComplete="true" markedAsCompleteDate="2016-04-08T02:13:12.322Z">
    <start>2016-04-08T23:47:50.615Z</start>
    <end>2016-04-08T02:11:30.954Z</end>
    <deviceid>353375050176865</deviceid>
    <telnr/>
    <insp>
      <objekt>25</objekt>
      <objdel>212</objdel>
      <objdel-tx>Bronstunneln</objdel-tx>
      <datum>2016-04-08</datum>
      <sign>MFR RLS</sign>
      <anm>8</anm>
    </insp>
    <skdr>
      <skd>
        <metod>100</metod>
        <ts>
          <ts-nr>523</ts-nr>
          <ts-typskada>Lucka - Lös -  Takelement</ts-typskada>
          <ts-kdel>15</ts-kdel>
          <ts-kelem>1530</ts-kelem>
          <ts-mtrl>13</ts-mtrl>
          <ts-typ>35</ts-typ>
          <ts-orsak>999</ts-orsak>
          <ts-aktivitet>87</ts-aktivitet>
          <ts-enhet>st</ts-enhet>
          <ts-aktivitet-tx>Åtdragning</ts-aktivitet-tx>
          <ts-mangd>1.0000000000</ts-mangd>
        </ts>

Thers is no 'instanceID' at each 'skd' node. I've tested the XPATHs at http://www.freeformatter.com/xpath-tester.html and it seems to be OK.

Anyone have any idea what I'm doing wrong here?

Solution

  • Your template does not match the skd element in the source XML document, because that element is in a namespace.

    Try it this way instead:

    <xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:odk="http://opendatakit.org/submissions"
exclude-result-prefixes="odk">
<xsl:output indent="yes" encoding="utf-8"/>
<xsl:strip-space elements="*"/>

<xsl:template match="/">
    <dataroot>
        <xsl:apply-templates select="@*|node()"/>
    </dataroot>
</xsl:template>

<xsl:template match="@*|node()">
    <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
</xsl:template>

<xsl:template match="odk:skd">
    <xsl:copy>
        <xsl:apply-templates select="@*"/>
        <ID xmlns="http://opendatakit.org/submissions">
            <xsl:value-of select="/odk:SoLa_Tu_Insp_2016-03-13/@instanceID"/>
        </ID>
        <xsl:apply-templates select="node()"/>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>

    Additional notes:

    1. Starting a match pattern with // is redundant;
    2. xsl-copy-of is not a valid XSLT instruction;
    3. Your version <xsl:apply-templates select="@*|node()"/> would have duplicated the child nodes.