Warm tip: This article is reproduced from stackoverflow.com, please click
ms-access vba xml xslt

Import XML files in MS Access using an XSL transformation

发布于 2020-04-10 16:16:21

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.

Questioner
Dimbst
Viewed
44
分享
zx485 2020-02-02 05: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>

Related issues

1
How to add attribute declaration in XSD for element with sequence of child elements?
2
Accessing an XML file via URL using HTML and JS
3
XSLT copy elements from one file to another matching two conditions
4
Parse XML tag to boolean if it exists when unmarshalling
5
ImageView in circular through xml
6
replace a string in an XML file
7
sed to match pattern across a newline
8
How to pretty print XML from the command line?
9
XSLT: How to transform the xml data into a hierarchical structure using XSLT
10
Parsing Custom Tags with PHP