Import XML files in MS Access using an XSL transformation

Question

What I'm trying to do: There is an automation system which produces like 10k XML files. I want to read all that files into a MS Access Database. In order to import the files (with the VBA ImportXML function) I need to transform the files with the VBA FormatXML function using an XSL stylesheet.

I googled some stuff, but it seems like I can't get it to work. This is what an XML file looks like (based on confidentiality reasons I cant show a real file, but the structure looks like that):

<?xml version="1.0" encoding="UTF-16"?>
<Album xmlns="x-schema:ConfigFileSchema.xml">
    <Informations>
        <General>
            <Name>Name of Album</Name>
            <Band>Name of Band</Band>
        </General>
        <Parameters>
            <Parameter>
                <ParamName>Length of Album</ParamName>
                <ParamValue>57:16</ParamValue>
            </Parameter>
            <Parameter>
                <ParamName>Year</ParamName>
                <ParamValue>2020</ParamValue>
            </Parameter>
        </Parameters>
        <Tracks>
            <Track>
                <Trackdef>
                    <TrackName>Name of Track</TrackName>
                    <TrackLength>3:30</TrackLength>
                </Trackdef>
                <Parameters>
                    <Parameter>
                        <ParamName>Features</ParamName>
                        <ParamValue>...</ParamValue>
                    </Parameter>
                    <Parameter>
                        <ParamName>Some other Parameters</ParamName>
                        <ParamValue>...</ParamValue>
                    </Parameter>
                    <Parameter>
                        <ParamName>Some other Parameters</ParamName>
                        <ParamValue>...</ParamValue>
                    </Parameter>
                </Parameters>
            </Track>
            <Track>
                <Trackdef>
                    <TrackName>Name of Track</TrackName>
                    <TrackLength>3:30</TrackLength>
                </Trackdef>
                <Parameters>
                    <Parameter>
                        <ParamName>Features</ParamName>
                        <ParamValue>...</ParamValue>
                    </Parameter>
                    <Parameter>
                        <ParamName>Some other Parameters</ParamName>
                        <ParamValue>...</ParamValue>
                    </Parameter>
                    <Parameter>
                        <ParamName>Some other Parameters</ParamName>
                        <ParamValue>...</ParamValue>
                    </Parameter>
                </Parameters>
            </Track>
        </Tracks>
    </Informations>
</Album>

I tried to do some very easy transformations to get the output XML from TransformXML to look like that:

<?xml version="1.0"?>
<Information>
    <Track>
        <Parameter>
            <ParamName>Features</ParamName>
            <ParamValue>...</ParamValue>
        </Parameter>
        <Parameter>
            <ParamName>Some other Parameters</ParamName>
            <ParamValue>...</ParamValue>
        </Parameter>
    </Track>
    <Track>
        <Parameter>
            <ParamName>Features</ParamName>
            <ParamValue>...</ParamValue>
        </Parameter>
        <Parameter>
            <ParamName>Some other Parameters</ParamName>
            <ParamValue>...</ParamValue>
        </Parameter>
    </Track>
</Information>

Using this XSLT:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="/Album/Informations/Tracks/Track/Parameters">
        <Information>
            <xsl:for-each select="child::Parameter">
                <Parameter>
                    <xsl:copy-of select="child::ParamName"/>
                    <xsl:copy-of select="child::ParamValue"/>
                </Parameter>
            </xsl:for-each>
        </Information>
        <xsl:apply-templates/>
    </xsl:template>
</xsl:stylesheet>

Sadly it doesn't do anything, a blank file is produced. I hope that somebody can point out what I'm doing wrong here.

Closing as a duplicate. No criticism intended; you couldn't be expected to know that hundreds of other people have asked the same question in different ways. But the answer is always the same: search for "XSLT default namespace" — Michael Kay, Feb 01 '20 at 21:27

score 2 · Accepted Answer · answered Feb 01 '20 at 21:19

You were missing the fact that all of your elements are in a default namespace defined on your element

<Album xmlns="x-schema:ConfigFileSchema.xml">

You have to define that namespace in your XSLT as well to properly access its elements. This can be achieved with the attribute

xmlns:x="x-schema:ConfigFileSchema.xml"

on your xsl:stylesheet element. To get rid of this namespace in your output, also add the attribute

exclude-result-prefixes="x"

to the xsl:stylesheet element.
So the whole stylesheet could look like the following. To get unique parameters it depends on the TrackName being unique, because it is used in creating the key value. Of course you can change that to another unique value (but do it in both places - the definition and the usage).

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:x="x-schema:ConfigFileSchema.xml" exclude-result-prefixes="x">
    <xsl:output method="xml" indent="yes" />
    <xsl:key name="params" match="x:Parameters/x:Parameter" use="concat(x:ParamName,../../x:Trackdef/x:TrackName)" />

    <xsl:template match="text()" />

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

    <xsl:template match="/x:Album/x:Informations/x:Tracks/x:Track">
        <Track>
            <xsl:for-each select="x:Parameters/x:Parameter[generate-id() = generate-id(key('params',concat(x:ParamName,../../x:Trackdef/x:TrackName))[1])]">
                <Parameter>
                    <ParamName><xsl:value-of  select="x:ParamName"/></ParamName>
                    <ParamValue><xsl:value-of select="x:ParamValue"/></ParamValue>
                </Parameter>
            </xsl:for-each>
        </Track>
    </xsl:template>

</xsl:stylesheet>

Its output is:

<?xml version="1.0"?>
<Information>
  <Track>
    <Parameter>
      <ParamName>Features</ParamName>
      <ParamValue>...</ParamValue>
    </Parameter>
    <Parameter>
      <ParamName>Some other Parameters</ParamName>
      <ParamValue>...</ParamValue>
    </Parameter>
  </Track>
  <Track>
    <Parameter>
      <ParamName>Features</ParamName>
      <ParamValue>...</ParamValue>
    </Parameter>
    <Parameter>
      <ParamName>Some other Parameters</ParamName>
      <ParamValue>...</ParamValue>
    </Parameter>
  </Track>
</Information>

Import XML files in MS Access using an XSL transformation

1 Answers1