Omit foreign key values from child elements when e

Question

I have two tables CR and RELEASE with relationship from CR (ID) to RELEASE (CRID) one to many.

CR (design):

ID: key, unique
Description: text

RELEASE (design):

ID: key, unique
CRID: number, not unique
Name: text

With the following VBA code, I am managed to export the tables to XML.

Set objOrderInfo = Application.CreateAdditionalData
objOrderInfo.Add ("RELEASE")
Application.ExportXML ObjectType:=acExportTable, DataSource:="CR", _
                      DataTarget:=pFileNAme, _
                      AdditionalData:=objOrderInfo

The exported XML is something like:

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2015-12-09T09:34:28">
<CR>
    **<ID>1</ID>**
    <Description>Test</Description>
    <RELEASE>
        <ID>1</ID>
        **<CRID>1</CRID>**
        <Name>R2016</Name>
    </RELEASE>
    <RELEASE>
        <ID>2</ID>
        **<CRID>1</CRID>**
        <Name>R2017</Name>
    </RELEASE>
</CR>

Note that CRID showed several times in the XML which is practically redundant. How to remove the CRID elements from RELEASE element in XML? Thanks,

Answer 1

I can't see how you could. After all, CRID is a field of table RELEASE:

<RELEASE>
    <ID>1</ID>
    <CRID>1</CRID>
    <Name>R2016</Name>
</RELEASE>

Answer 2

If you need to tweak the XML output after using Application.ExportXML you could do the initial export to a temporary XML file and then use an .xslt file like this:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes"/>
    <xsl:strip-space elements="*"/>

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

    <xsl:template match="RELEASE/CRID">
        <!-- omit by doing nothing -->
    </xsl:template>

</xsl:stylesheet>

and a VBA routine like this

Option Compare Database
Option Explicit

Public Sub ApplyXmlTransform(sourceFile, stylesheetFile, resultFile)
    ' ref: http://archive.oreilly.com/pub/post/transforming_xml_in_microsoft.html
    '
    ' project reference required: Microsoft XML, v4.0

    Dim source As New MSXML2.DOMDocument30
    Dim stylesheet As New MSXML2.DOMDocument30
    Dim result As New MSXML2.DOMDocument30

    ' Load data.
    source.async = False
    source.Load sourceFile  ' source .xml file

    ' Load style sheet.
    stylesheet.async = False
    stylesheet.Load stylesheetFile  ' .xslt file

    If (source.parseError.errorCode <> 0) Then
        MsgBox ("Error loading source document: " & source.parseError.reason)
    Else
        If (stylesheet.parseError.errorCode <> 0) Then
            MsgBox ("Error loading stylesheet document: " & stylesheet.parseError.reason)
        Else
            ' Do the transform.
            source.transformNodeToObject stylesheet, result
            result.Save resultFile  ' resulting .xml file
        End If
    End If

End Sub

to remove <CRID> from the <RELEASE> element.