views:

68

answers:

0

The following XSLT is transforming file1.xml and file2.xml. How do i replace those 2 files with a full folder listing of all the XML files? I am using VBA in Microsoft Access, and this stylesheet solves the problem of merging multiple files (credit to Alejandro) without dropping any newer fields as was the case during XML import. It works as intended for two specific files, however, i have thousand files to import and can't specify each directly. Solution might be to "pass an XML document or URI to retrieve all the names".

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
<xsl:key name="kElementByName" match="/*/*/*" use="name()"/>
<xsl:param name="pCommaSeparateFilesNames" select="'file1.xml,file2.xml'"/>
<xsl:template match="/" name="root">
    <xsl:param name="pFilesNames"
               select="concat($pCommaSeparateFilesNames,',')"/>
    <xsl:param name="pDocuments" select="/.."/>
    <xsl:param name="pFields" select="/.."/>
    <xsl:param name="pFieldsNames" select="''"/>
    <xsl:choose>
        <xsl:when test="contains($pFilesNames,',')">
            <xsl:variable name="vDocument"
                 select="document(substring-before($pFilesNames,','),.)"/>
            <xsl:variable name="vFields"
                          select="$vDocument/*/*/*
                                   [count(.|key('kElementByName',
                                                name())[1])=1]
                                   [not(contains(concat('|',$pFieldsNames),
                                                concat('|',name(),'|')))]"/>
            <xsl:call-template name="root">
                <xsl:with-param name="pFilesNames"
                                select="substring-after($pFilesNames,',')"/>
                <xsl:with-param name="pDocuments"
                                select="$pDocuments | $vDocument"/>
                <xsl:with-param name="pFields" select="$pFields|$vFields"/>
                <xsl:with-param name="pFieldsNames">
                    <xsl:value-of select="$pFieldsNames"/>
                    <xsl:apply-templates select="$vFields" mode="names"/>
                </xsl:with-param>
            </xsl:call-template>
        </xsl:when>
        <xsl:otherwise>
            <form>
                <xsl:apply-templates select="$pDocuments/*">
                    <xsl:with-param name="pFields" select="$pFields"/>
                </xsl:apply-templates>
            </form>
        </xsl:otherwise>
    </xsl:choose>
</xsl:template>
<xsl:template match="/*">
    <xsl:param name="pFields"/>
    <xsl:variable name="vCurrent" select="."/>
    <page>
        <xsl:for-each select="$pFields">
            <xsl:variable name="vField" select="."/>
            <xsl:for-each select="$vCurrent">
                <xsl:element name="{name($vField)}">
                    <xsl:value-of 
                             select="key('kElementByName',name($vField))"/>
                </xsl:element>
            </xsl:for-each>
        </xsl:for-each>
    </page>
</xsl:template>
<xsl:template match="*" mode="names">
    <xsl:value-of select="concat(name(),'|')"/>
</xsl:template>

VBA - XML Bulk Import code

 Private Sub cmdImport_Click()
 Dim strFile As String 'Filename
 Dim strFileList() As String 'File Array
 Dim intFile As Integer 'File Number
 Dim strPath As String ' Path to file folder

 strPath = "C:\Users\Main\Desktop\XML-files"
 strFile = Dir(strPath & "*.XML")

 While strFile <> ""
  'add files to the list
 intFile = intFile + 1
 ReDim Preserve strFileList(1 To intFile)
 strFileList(intFile) = strFile
 strFile = Dir()
 Wend
 'see if any files were found
 If intFile = 0 Then
 MsgBox "No files found"
 Exit Sub
 End If

 'cycle through the list of files
 For intFile = 1 To UBound(strFileList)
 Application.TransformXML strPath & strFileList(intFile), _
 "C:\Users\Main\Desktop\stylesheet.xslt", _
 "C:\Users\Main\Desktop\temp.xml"
 Next intFile

 Application.ImportXML "C:\Users\Main\Desktop\temp.xml", acAppendData

 MsgBox "Import Completed"
End Sub