How to convert attribute-centric XML to element-centric XML, persist parent node into each child node, and preserve nodes of the same name?

Question

This is a different question to my prior. It was suggested by a community member that I make a new question.

I need an XSLT 1.0 as any other versions are not accepted by Microsoft Access

I have the following attribute-centric 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>

I would like to result in the following:

*Please note, I need the all nodes (regardless of null) with each instance of ItOverride, as seen in the following:

<?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>

You can see above that thing2 has two instances, that of LaborTask and that of ItOverride. I would like to preserve that of each. So within each ItOverride, there would be two instances of thing2 (open to naming convention options)

Further, I would like to be able to do this within one XSLT.

So far, this is the XSLT that a community member has provided:

<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>

Which gets me close, with this result:

<?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>

I only have a basic knowledge of XSLT, but happy to provide any other information to achieve a working answer!

I do need this transform to be operable with Microsoft Access.

TYIA!

I'm open to alternatives for the naming convention. I'm not familiar enough with XML to know how a situation like this would be resolved. I would have thought that Access could accept attribute-centric XMLs given Excel can, but Microsoft wanted to make it hard — Justin W, Feb 17 '22 at 21:35

score 1 · Accepted Answer · answered Feb 17 '22 at 21:39

Perhaps something like this can work for you:

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-group select="../@* | @*" group-by="name()">
                    <xsl:for-each select="current-group()">
                        <xsl:element name="{name()}{if (position() > 1) then concat('.', position()) else ''}">
                            <xsl:value-of select="." />
                        </xsl:element>
                    </xsl:for-each>
                </xsl:for-each-group>
            </xsl:copy>
        </xsl:for-each>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>

In your given example this will produce:

Result

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

Are you familiar with how Access will handle the fact that not all ItOverride share the same nodes? I think (based on my testing) that Access will build the table off of the first ItOverride's nodes. Is it possible to get thing10, thing11, thing12, and thing13 as nodes under all ItOverride? Also, does Access accept all versions of XSLT? — Justin W, Feb 17 '22 at 21:43
@JustinW I know nothing about Access. I suggested in your other question that you find out exactly what the required result is, before you ask how to produce it using XSLT. All these changes in requirements are wasting the time and goodwill of people that are trying to help you. — michael.hor257k, Feb 17 '22 at 21:47
I am getting an import error, "specified transform failed to successfully transform your data" when applying that transform to the base attribute-centric xml I posted above. Could this relate to the XSLT versioning? — Justin W, Feb 17 '22 at 21:47
I don't know. See: https://stackoverflow.com/a/25245033/3016153 — michael.hor257k, Feb 17 '22 at 21:50
It appears that Access only supports XLST 1.0. Is there a way to convert the above code into XSLT 1.0 easily? That solution, paired with the ability to add the null nodes to all instances of ItOverride would be the exact desired solution. Not trying to waste anyone's time. All help is greatly appreciated! — Justin W, Feb 17 '22 at 22:02
So why did you tag your question as `xslt-2.0` and `xslt-3.0`?? — michael.hor257k, Feb 17 '22 at 22:07
I was unaware that Access only supported xslt-1.0. It appears that there is very little documentation on which versions it supports. — Justin W, Feb 18 '22 at 12:38
I have edited the tags to reflect just the XSLT 1.0 and what I believe are other relevant tags to capture the correct audience. With that said, your solution still gets very close. If there is any way to convert this to XSLT-1.0 and somehow bring in all fields under each ItOverride (regardless of empty content), that would be the all encompassing solution. I am shocked that Microsoft doesn't support any versions of XSLT aside from 1.0. — Justin W, Feb 18 '22 at 15:08

How to convert attribute-centric XML to element-centric XML, persist parent node into each child node, and preserve nodes of the same name?

1 Answers1

Linked