Search code examples
xmlms-accessxsltxslt-1.0xslt-2.0

How can I convert this attribute-centric XML to an element-centric XML that can import into one table in MS-Access?

I have the following attribute-centric XML format:

<?xml version="1.0" encoding="UTF-8"?>
<LaborTaskInterface>
      <LaborTask thing1="a" thing2="b" thing3="c" thing4="d" thing5="e" 
      thing6="f" thing7="g" thing8="h" thing9="i">
            <ltOverride unit_id="1" value="1" thing2="b" thing3="c" thing4="d"/>
            <ltOverride unit_id="2" value="1" thing2="b" thing3="c" thing4="d"/>
            <ltOverride unit_id="3" value="1" thing2="b" thing3="c" thing4="d"/>
            <ltOverride unit_id="4" value="1" thing2="b" thing3="c" thing4="d"/>
      </LaborTask>
</LaborTaskInterface>

I have gotten it to this element-centric XML format:

<?xml version="1.0" encoding="UTF-8"?>
<LaborTaskInterface>
   <LaborTask>
      <thing1>a</thing1>
      <thing2>b</thing2>
      <thing3>c</thing3>
      <thing4>d</thing4>
      <thing5>e</thing5>
      <thing6>f</thing6>
      <thing7>g</thing7>
      <thing8>h</thing8>
      <thing9>i</thing9>
      <ltOverride>
         <unit_id>1</unit_id>
         <value>1</value>
         <thing2>b</thing2>
         <thing3>c</thing3>
         <thing4>d</thing4>
      </ltOverride>
      <ltOverride>
         <unit_id>2</unit_id>
         <value>10</value>
         <thing2>b</thing2>
         <thing3>c</thing3>
         <thing4>d</thing4>
      </ltOverride>
      <ltOverride>
         <unit_id>3</unit_id>
         <value>100</value>
         <thing2>b</thing2>
         <thing3>c</thing3>
         <thing4>d</thing4>
      </ltOverride>
      <ltOverride>
         <unit_id>4</unit_id>
         <value>1000</value>
         <thing2>b</thing2>
         <thing3>c</thing3>
         <thing4>d</thing4>
      </ltOverride>
   </LaborTask>
</LaborTaskInterface>

Using this XSL Transform:

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

<xsl:template match="@*" >
    <xsl:element name="{name()}">
        <xsl:value-of select="."/>
    </xsl:element>
</xsl:template>

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

</xsl:stylesheet>

The issue that I am running into however, is that when importing into Microsoft Access (since Access only accepts element centric) is that it wants to create two separate tables - LaborTask and ItOverride.

I would like all of the information to come in to one table. This means that the entirety of the LaborTask will be repeated with each ItOverride occurrence (this is the desire, so that is perfectly acceptable).

This is what the final format should look like:

<?xml version="1.0" encoding="UTF-8"?>
<LaborTaskInterface>
      <ltOverride>
         <unit_id>1</unit_id>
         <value>1</value>
         <thing1>a</thing1>
         <thing2>b</thing2>
         <thing3>c</thing3>
         <thing4>d</thing4>
         <thing5>e</thing5>
         <thing6>f</thing6>
         <thing7>g</thing7>
         <thing8>h</thing8>
         <thing9>i</thing9>
      </ltOverride>
      <ltOverride>
         <unit_id>2</unit_id>
         <value>10</value>
         <thing2>b</thing2>
         <thing3>c</thing3>
         <thing4>d</thing4>
         <thing5>e</thing5>
         <thing6>f</thing6>
         <thing7>g</thing7>
         <thing8>h</thing8>
         <thing9>i</thing9>
      </ltOverride>
      <ltOverride>
         <unit_id>3</unit_id>
         <value>100</value>
         <thing2>b</thing2>
         <thing3>c</thing3>
         <thing4>d</thing4>
         <thing5>e</thing5>
         <thing6>f</thing6>
         <thing7>g</thing7>
         <thing8>h</thing8>
         <thing9>i</thing9>
      </ltOverride>
      <ltOverride>
         <unit_id>4</unit_id>
         <value>1000</value>
         <thing2>b</thing2>
         <thing3>c</thing3>
         <thing4>d</thing4>
         <thing5>e</thing5>
         <thing6>f</thing6>
         <thing7>g</thing7>
         <thing8>h</thing8>
         <thing9>i</thing9>
      </ltOverride>
</LaborTaskInterface>

I have zero clue how to get to the final format, which leads me to my question...How do I get there? What should my XSLT look like?

Example of nodes within one ItOverride and not in another:

<?xml version="1.0" encoding="UTF-8"?>
<LaborTaskInterface>
      <ltOverride>
         <unit_id>1573</unit_id>
         <thing1>7.000</thing1>
         <thing2>1.000</thing2>
         <thing3></thing3>
         <thing4></thing4>
         <thing5></thing5>
         <value>13.7097</value>
         <thing6>Minutes</thing6>
         <thing7>7.000</thing7>
         <thing8>1.000</thing8>
         <thing9>xxx</thing9>
         <thing10>n</thing10>
         <thing11>yyy</thing11>
         <thing12>756</thing12>
         <thing13>d</thing13>
         <thing14>Minutes</thing14>
         <thing15></thing15>
         <thing16></thing16>
         <thing17></thing17>
      </ltOverride>
      <ltOverride>
         <unit_id>1573</unit_id>
         <thing1>7.000</thing1>
         <thing2>1.000</thing2>
         <thing3></thing3>
         <thing4></thing4>
         <thing5></thing5>
         <value>13.7097</value>
         <thing6>Minutes</thing6>
         <thing7>7.000</thing7>
         <thing8>1.000</thing8>
         <thing9>xxx</thing9>
         <thing10>n</thing10>
         <thing11>yyy</thing11>
         <thing12>756</thing12>
         <thing13>d</thing13>
         <thing14>Minutes</thing14>
         <thing15>bb</thing15>
         <thing16></thing16>
         <thing17>eee</thing17>
      </ltOverride>
      <ltOverride>
         <unit_id>1573</unit_id>
         <thing1>7.000</thing1>
         <thing2>1.000</thing2>
         <thing3></thing3>
         <thing4></thing4>
         <thing5></thing5>
         <value>13.7097</value>
         <thing6>Minutes</thing6>
         <thing7>7.000</thing7>
         <thing8>1.000</thing8>
         <thing9>xxx</thing9>
         <thing10>n</thing10>
         <thing11>yyy</thing11>
         <thing12>756</thing12>
         <thing13>d</thing13>
         <thing14>Minutes</thing14>
         <thing15>aaa</thing15>
         <thing16>bbb</thing16>
         <thing17>c</thing17>
      </ltOverride>

      
</LaborTaskInterface>

Progress so far with updates to base XML.

Please see the base XML:

<?xml version="1.0" encoding="UTF-8"?>
<LaborTaskInterface>
      <LaborTask thing1="a" thing2="c" thing3="d" thing4="e" thing5="f" 
      thing6="g" thing7="h" thing8="i" thing9="j">
            <ltOverride unit_id="1" value="1" thing2="k" thing3="c" thing4="d" thing10="o"/>
            <ltOverride unit_id="2" value="1" thing2="l" thing3="c" thing4="d" thing11="p"/>
            <ltOverride unit_id="3" value="1" thing2="m" thing3="c" thing4="d" thing12="q"/>
            <ltOverride unit_id="4" value="1" thing2="n" thing3="c" thing4="d" thing13="r"/>
      </LaborTask>
</LaborTaskInterface>

Desired end result:

<?xml version="1.0" encoding="UTF-8"?>
<LaborTaskInterface>
   <ltOverride>
      <unit_id>1</unit_id>
      <value>1</value>
      <thing1>a</thing1>
      <thing2>c</thing2>
      <thing2[2]>k</thing2[2]>
      <thing3>c</thing3>
      <thing4>d</thing4>
      <thing5>f</thing5>
      <thing6>g</thing6>
      <thing7>h</thing7>
      <thing8>i</thing8>
      <thing9>j</thing9>
      <thing10>o</thing10>
      <thing11></thing11>
      <thing12></thing12>
      <thing13></thing13>
   </ltOverride>
   <ltOverride>
      <unit_id>2</unit_id>
      <value>1</value>
      <thing1>a</thing1>
      <thing2>c</thing2>
      <thing2[2]>l</thing2[2]>
      <thing3>c</thing3>
      <thing4>d</thing4>
      <thing5>f</thing5>
      <thing6>g</thing6>
      <thing7>h</thing7>
      <thing8>i</thing8>
      <thing9>j</thing9>
      <thing10></thing10>
      <thing11>p</thing11>
      <thing12></thing12>
      <thing13></thing13>
   </ltOverride>
   <ltOverride>
      <unit_id>3</unit_id>
      <value>1</value>
      <thing1>a</thing1>
      <thing2>c</thing2>
      <thing2[2]>m</thing2[2]>
      <thing3>c</thing3>
      <thing4>d</thing4>
      <thing5>f</thing5>
      <thing6>g</thing6>
      <thing7>h</thing7>
      <thing8>i</thing8>
      <thing9>j</thing9>
      <thing10></thing10>
      <thing11></thing11>
      <thing12>q</thing12>
      <thing13></thing13>
   </ltOverride>
   <ltOverride>
      <unit_id>4</unit_id>
      <value>1</value>
      <thing1>a</thing1>
      <thing2>c</thing2>
      <thing2[2]>n</thing2[2]>
      <thing3>c</thing3>
      <thing4>d</thing4>
      <thing5>f</thing5>
      <thing6>g</thing6>
      <thing7>h</thing7>
      <thing8>i</thing8>
      <thing9>j</thing9>
      <thing10></thing10>
      <thing11></thing11>
      <thing12>r</thing12>
      <thing13></thing13>
   </ltOverride>
</LaborTaskInterface>
Expa

I have applied this XSLT (Thank you Michael.hor257k):

<xsl:stylesheet version="2.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/LaborTaskInterface" >
    <xsl:copy>
        <xsl:for-each select="LaborTask/ltOverride">
            <xsl:variable name="temp">
                <dummy>
                    <xsl:copy-of select="../@*"/>
                    <xsl:copy-of select="@*"/>
                </dummy>
            </xsl:variable>
            <xsl:copy>
                <xsl:for-each select="$temp/dummy/@*">
                    <xsl:element name="{name()}">
                        <xsl:value-of select="." />
                    </xsl:element>
                </xsl:for-each>
            </xsl:copy>
        </xsl:for-each>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>

It has got me to this result which is very close:

<?xml version="1.0" encoding="UTF-8"?>
<LaborTaskInterface>
   <ltOverride>
      <thing1>a</thing1>
      <thing2>k</thing2>
      <thing3>c</thing3>
      <thing4>d</thing4>
      <thing5>f</thing5>
      <thing6>g</thing6>
      <thing7>h</thing7>
      <thing8>i</thing8>
      <thing9>j</thing9>
      <thing10>o</thing10>
      <unit_id>1</unit_id>
      <value>1</value>
   </ltOverride>
   <ltOverride>
      <thing1>a</thing1>
      <thing2>l</thing2>
      <thing3>c</thing3>
      <thing4>d</thing4>
      <thing5>f</thing5>
      <thing6>g</thing6>
      <thing7>h</thing7>
      <thing8>i</thing8>
      <thing9>j</thing9>
      <thing11>p</thing11>
      <unit_id>2</unit_id>
      <value>1</value>
   </ltOverride>
   <ltOverride>
      <thing1>a</thing1>
      <thing2>m</thing2>
      <thing3>c</thing3>
      <thing4>d</thing4>
      <thing5>f</thing5>
      <thing6>g</thing6>
      <thing7>h</thing7>
      <thing8>i</thing8>
      <thing9>j</thing9>
      <thing12>q</thing12>
      <unit_id>3</unit_id>
      <value>1</value>
   </ltOverride>
   <ltOverride>
      <thing1>a</thing1>
      <thing2>n</thing2>
      <thing3>c</thing3>
      <thing4>d</thing4>
      <thing5>f</thing5>
      <thing6>g</thing6>
      <thing7>h</thing7>
      <thing8>i</thing8>
      <thing9>j</thing9>
      <thing13>r</thing13>
      <unit_id>4</unit_id>
      <value>1</value>
   </ltOverride>
</LaborTaskInterface>

Any and all help is greatly appreciated!

Thank you!

Solution

  • Consider the following example:

    XML

    <LaborTaskInterface>
      <LaborTask thing1="1" thing2="2" thing3="3" thing4="4" thing5="5" 
      thing6="6" thing7="7" thing8="8" thing9="9">
            <ltOverride unit_id="1" value="a" thing2="102" thing3="103" thing4="104"/>
            <ltOverride unit_id="2" value="b" thing2="202" thing3="203" thing4="204"/>
            <ltOverride unit_id="3" value="c" thing2="302" thing3="303" thing4="304"/>
            <ltOverride unit_id="4" value="d" thing2="402" thing3="403" thing4="404"/>
      </LaborTask>
</LaborTaskInterface>

    XSLT 2.0

    <xsl:stylesheet version="2.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/LaborTaskInterface" >
    <xsl:copy>
        <xsl:for-each select="LaborTask/ltOverride">
            <xsl:copy>
                <xsl:for-each select="@unit_id, @value, ../@thing1, @thing2, @thing3, @thing4, ../(@thing5, @thing6, @thing7, @thing8, thing9)">
                    <xsl:element name="{name()}">
                        <xsl:value-of select="." />
                    </xsl:element>
                </xsl:for-each>
            </xsl:copy>
        </xsl:for-each>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>

    Result

    <?xml version="1.0" encoding="UTF-8"?>
<LaborTaskInterface>
   <ltOverride>
      <unit_id>1</unit_id>
      <value>a</value>
      <thing1>1</thing1>
      <thing2>102</thing2>
      <thing3>103</thing3>
      <thing4>104</thing4>
      <thing5>5</thing5>
      <thing6>6</thing6>
      <thing7>7</thing7>
      <thing8>8</thing8>
   </ltOverride>
   <ltOverride>
      <unit_id>2</unit_id>
      <value>b</value>
      <thing1>1</thing1>
      <thing2>202</thing2>
      <thing3>203</thing3>
      <thing4>204</thing4>
      <thing5>5</thing5>
      <thing6>6</thing6>
      <thing7>7</thing7>
      <thing8>8</thing8>
   </ltOverride>
   <ltOverride>
      <unit_id>3</unit_id>
      <value>c</value>
      <thing1>1</thing1>
      <thing2>302</thing2>
      <thing3>303</thing3>
      <thing4>304</thing4>
      <thing5>5</thing5>
      <thing6>6</thing6>
      <thing7>7</thing7>
      <thing8>8</thing8>
   </ltOverride>
   <ltOverride>
      <unit_id>4</unit_id>
      <value>d</value>
      <thing1>1</thing1>
      <thing2>402</thing2>
      <thing3>403</thing3>
      <thing4>404</thing4>
      <thing5>5</thing5>
      <thing6>6</thing6>
      <thing7>7</thing7>
      <thing8>8</thing8>
   </ltOverride>
</LaborTaskInterface>

    Here's another approach that does not require hard-coding of the attribute names. It simply allows the attributes of the child ltOverride element to override the similarly-named attributes of the parent LaborTask. After that, the remaining attributes are converted to elements.

    XSLT 2.0

    <xsl:stylesheet version="2.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/LaborTaskInterface" >
    <xsl:copy>
        <xsl:for-each select="LaborTask/ltOverride">
            <xsl:variable name="temp">
                <dummy>
                    <xsl:copy-of select="../@*"/>
                    <xsl:copy-of select="@*"/>
                </dummy>
            </xsl:variable>
            <xsl:copy>
                <xsl:for-each select="$temp/dummy/@*">
                    <xsl:element name="{name()}">
                        <xsl:value-of select="." />
                    </xsl:element>
                </xsl:for-each>
            </xsl:copy>
        </xsl:for-each>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>