0

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!

Justin W
  • 5
  • 4
  • *״I am not 100% certain of the final format that I need the XML in"* Then your question is not (yet) about XSLT. Find out exactly **what** the result should be - then ask **how** to get it. (And what does this have to with Javascript or CSS??) – michael.hor257k Feb 17 '22 at 15:23
  • I added what the final format should now look like. I'm not honestly sure about Java or CSS, those were just suggested tags and had a large community. – Justin W Feb 17 '22 at 19:59
  • I am having trouble understanding your example, because all the values are the same. So it is difficult to determine where the values in the output are supposed to come from. – michael.hor257k Feb 17 '22 at 20:06
  • I edited to hopefully clear things up. I believe I found a similar scenario to this that ironically you also answered. The only trouble though, is it possible to achieve both the attribute to element conversion and organization within the same XSLT? Or will it require 2 XSLT? https://stackoverflow.com/questions/34758492/xslt-transform-doesnt-work-until-i-remove-root-node/34762628#34762628 – Justin W Feb 17 '22 at 20:29
  • I don't see the connection to the other question. I have posted an answer that is partly based on a guess. Hopefully it provides a solution for you. – michael.hor257k Feb 17 '22 at 20:35
  • Thank you so much! I did just comment on your solution with a piggyback question. I really appreciate the help. It's awesome that this is a such a supportive community! – Justin W Feb 17 '22 at 20:45

1 Answers1

0

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>
michael.hor257k
  • 113,275
  • 6
  • 33
  • 51
  • This is awesome! I do have a follow-up question though, in some instances (shame on me for not including this in the example XML), thing2 of LaborTask is not equal to thing2 of ItOverride. Is there a way to handle that as well? (hopefully in one XSLT file). Also, what if there is an instance where one ItOverride contains a thing10...y? Is there a way to handle that? TYIA! – Justin W Feb 17 '22 at 20:44
  • I am afraid I do not understand the problem. In my example, thing2 of LaborTask is indeed not equal to thing2 of ItOverride - and the value is taken from the attribute in ItOverride. How else would you want to handle it? – michael.hor257k Feb 17 '22 at 20:48
  • I believe the added approach provides the answer to your other problem. – michael.hor257k Feb 17 '22 at 20:50
  • Wow, I didn't even notice that you used different values, so your method already handles that one. I guess I was referring to if there are two nodes of the same name (one coming from LaborTask and one from ItOverride) but with different values. Is it possible to denote one as "thing2[2]" – Justin W Feb 17 '22 at 20:51
  • I was under the impression that override means override. If you want to preserve BOTH, then you have a significantly different question, with a significantly different result. I suggest you post this as a separate question. – michael.hor257k Feb 17 '22 at 20:55
  • Is there a way to add nodes to an instance of ItOverride where there is no information in the node. This would hopefully result in a blank cell within Access. I tested this within a notepad XML and my theory imported successfully into Access. TYIA – Justin W Feb 17 '22 at 20:57
  • https://stackoverflow.com/questions/71165487/how-to-convert-attribute-centric-xml-to-element-centric-xml-persist-parent-node Here is my new question and I have adjusted the example. All of this help is greatly appreciated! TYIA – Justin W Feb 17 '22 at 21:19
  • StackOverflow deleted my new question due to it being too similar to this one. I am happy to post updates to this one if that works? TYIA – Justin W Feb 17 '22 at 21:37
  • I have reopened your other question. – michael.hor257k Feb 17 '22 at 21:40