'How to add null fields to preceding sibling elements where the subsequent elements do have a value? See example
I have the following 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>
<LaborTask thing1="aa" thing2="bb" thing3="dd" thing4="ee" thing5="ff"
thing6="gg" thing7="hh" thing8="ii" thing9="jj">
<ltOverride unit_id="11" value="11" thing2="kk" thing3="cc" thing4="dd" thing10="oo"/>
<ltOverride unit_id="22" value="12" thing2="ll" thing3="cc" thing4="dd" thing11="pp"/>
<ltOverride unit_id="33" value="13" thing2="mm" thing3="cc" thing4="dd" thing12="qq"/>
<ltOverride unit_id="44" value="14" thing2="nn" thing3="cc" thing4="dd" thing13="rr"/>
</LaborTask>
</LaborTaskInterface>I have currently been able to transform and import this into Microsoft Access using the following XSLT-1.0:
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0"
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
exclude-result-prefixes="msxsl">
<xsl:output method="xml" indent="yes" />
<xsl:template match="@* | node()">
<xsl:copy>
<xsl:apply-templates select="@* | node()"/>
</xsl:copy>
</xsl:template>
<xsl:key name="group" match="ltOverride/*" use="concat(generate-id(..), '|', name())"/>
<xsl:template match="LaborTask">
<xsl:variable name="temp-data">
<xsl:apply-templates select="ltOverride" mode="attributes-to-elements"/>
</xsl:variable>
<xsl:for-each select="msxsl:node-set($temp-data)/ltOverride">
<xsl:copy>
<xsl:for-each select="*[generate-id() = generate-id(key('group', concat(generate-id(..), '|', name()))[1])]">
<xsl:for-each select="key('group', concat(generate-id(..), '|', name()))">
<xsl:variable name="index">
<xsl:if test="position() > 1">
<xsl:value-of select="concat('.', position())"/>
</xsl:if>
</xsl:variable>
<xsl:element name="{name()}{$index}">
<xsl:value-of select="."/>
</xsl:element>
</xsl:for-each>
</xsl:for-each>
</xsl:copy>
</xsl:for-each>
</xsl:template>
<xsl:template match="ltOverride" mode="attributes-to-elements">
<xsl:copy>
<xsl:apply-templates select="../@* | @*" mode="attributes-to-elements"/>
</xsl:copy>
</xsl:template>
<xsl:template match="@*" mode="attributes-to-elements">
<xsl:element name="{name()}">
<xsl:value-of select="."/>
</xsl:element>
</xsl:template>
</xsl:stylesheet>This provides the following output:
<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>
<ltOverride>
<thing1>aa</thing1>
<thing2>bb</thing2>
<thing2.2>kk</thing2.2>
<thing3>dd</thing3>
<thing3.2>cc</thing3.2>
<thing4>ee</thing4>
<thing4.2>dd</thing4.2>
<thing5>ff</thing5>
<thing6>gg</thing6>
<thing7>hh</thing7>
<thing8>ii</thing8>
<thing9>jj</thing9>
<unit_id>11</unit_id>
<value>11</value>
<thing10>oo</thing10>
</ltOverride>
<ltOverride>
<thing1>aa</thing1>
<thing2>bb</thing2>
<thing2.2>ll</thing2.2>
<thing3>dd</thing3>
<thing3.2>cc</thing3.2>
<thing4>ee</thing4>
<thing4.2>dd</thing4.2>
<thing5>ff</thing5>
<thing6>gg</thing6>
<thing7>hh</thing7>
<thing8>ii</thing8>
<thing9>jj</thing9>
<unit_id>22</unit_id>
<value>12</value>
<thing11>pp</thing11>
</ltOverride>
<ltOverride>
<thing1>aa</thing1>
<thing2>bb</thing2>
<thing2.2>mm</thing2.2>
<thing3>dd</thing3>
<thing3.2>cc</thing3.2>
<thing4>ee</thing4>
<thing4.2>dd</thing4.2>
<thing5>ff</thing5>
<thing6>gg</thing6>
<thing7>hh</thing7>
<thing8>ii</thing8>
<thing9>jj</thing9>
<unit_id>33</unit_id>
<value>13</value>
<thing12>qq</thing12>
</ltOverride>
<ltOverride>
<thing1>aa</thing1>
<thing2>bb</thing2>
<thing2.2>nn</thing2.2>
<thing3>dd</thing3>
<thing3.2>cc</thing3.2>
<thing4>ee</thing4>
<thing4.2>dd</thing4.2>
<thing5>ff</thing5>
<thing6>gg</thing6>
<thing7>hh</thing7>
<thing8>ii</thing8>
<thing9>jj</thing9>
<unit_id>44</unit_id>
<value>14</value>
<thing13>rr</thing13>
</ltOverride>
</LaborTaskInterface>I would like the following output (note how thing10 - thing13 exist for all instances of ItOverride, each instance has the value filled for the respective "thingXX" but null for the non-existent values. Also, for the sake of simplicity, aa and bb . . . xx represent completely new values, not 2xa etc):
<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>
<thing11></thing11>
<thing12></thing12>
<thing13></thing13>
</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>
<thing10></thing10>
<thing11>p</thing11>
<thing12></thing12>
<thing13></thing13>
</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>
<thing10></thing10>
<thing11></thing11>
<thing12>q</thing12>
<thing13></thing13>
</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>
<thing10></thing10>
<thing11></thing11>
<thing12></thing12>
<thing13>r</thing13>
</ltOverride>
<ltOverride>
<thing1>aa</thing1>
<thing2>bb</thing2>
<thing2.2>kk</thing2.2>
<thing3>dd</thing3>
<thing3.2>cc</thing3.2>
<thing4>ee</thing4>
<thing4.2>dd</thing4.2>
<thing5>ff</thing5>
<thing6>gg</thing6>
<thing7>hh</thing7>
<thing8>ii</thing8>
<thing9>jj</thing9>
<unit_id>11</unit_id>
<value>11</value>
<thing10>oo</thing10>
<thing11></thing11>
<thing12></thing12>
<thing13></thing13>
</ltOverride>
<ltOverride>
<thing1>aa</thing1>
<thing2>bb</thing2>
<thing2.2>ll</thing2.2>
<thing3>dd</thing3>
<thing3.2>cc</thing3.2>
<thing4>ee</thing4>
<thing4.2>dd</thing4.2>
<thing5>ff</thing5>
<thing6>gg</thing6>
<thing7>hh</thing7>
<thing8>ii</thing8>
<thing9>jj</thing9>
<unit_id>22</unit_id>
<value>12</value>
<thing10></thing10>
<thing11>pp</thing11>
<thing12></thing12>
<thing13></thing13>
</ltOverride>
<ltOverride>
<thing1>aa</thing1>
<thing2>bb</thing2>
<thing2.2>mm</thing2.2>
<thing3>dd</thing3>
<thing3.2>cc</thing3.2>
<thing4>ee</thing4>
<thing4.2>dd</thing4.2>
<thing5>ff</thing5>
<thing6>gg</thing6>
<thing7>hh</thing7>
<thing8>ii</thing8>
<thing9>jj</thing9>
<unit_id>33</unit_id>
<value>13</value>
<thing10></thing10>
<thing11></thing11>
<thing12>qq</thing12>
<thing13></thing13>
</ltOverride>
<ltOverride>
<thing1>aa</thing1>
<thing2>bb</thing2>
<thing2.2>nn</thing2.2>
<thing3>dd</thing3>
<thing3.2>cc</thing3.2>
<thing4>ee</thing4>
<thing4.2>dd</thing4.2>
<thing5>ff</thing5>
<thing6>gg</thing6>
<thing7>hh</thing7>
<thing8>ii</thing8>
<thing9>jj</thing9>
<unit_id>44</unit_id>
<value>14</value>
<thing10></thing10>
<thing11></thing11>
<thing12></thing12>
<thing13>rr</thing13>
</ltOverride>
</LaborTaskInterface>Is the above output possible to achieve?
I am thinking that it will involve an edit in the following portion of the XSLT:
<xsl:template match="LaborTask">
<xsl:variable name="temp-data">
<xsl:apply-templates select="ltOverride" mode="attributes-to-elements"/>
</xsl:variable>
<xsl:for-each select="msxsl:node-set($temp-data)/ltOverride">
<xsl:copy>
<xsl:for-each select="*[generate-id() = generate-id(key('group', concat(generate-id(..), '|', name()))[1])]">
<xsl:for-each select="key('group', concat(generate-id(..), '|', name()))">
<xsl:variable name="index">
<xsl:if test="position() > 1">
<xsl:value-of select="concat('.', position())"/>
</xsl:if>
</xsl:variable>
<xsl:element name="{name()}{$index}">
<xsl:value-of select="."/>
</xsl:element>
</xsl:for-each>
</xsl:for-each>
</xsl:copy>
</xsl:for-each>
</xsl:template>Aside from feasbility, is there a method to add/change a prior point within an XML when the transform is beyond said point?
As a side note, I have achieved the import to Access using the following link's guidance: https://docs.microsoft.com/en-us/office/troubleshoot/access/import-attribute-centric-xml
After running the suggested xslt-1.0 below against the updated xml above:
<LaborTaskInterface>
<ltOverride>
<thing1>a</thing1>
<thing2>c</thing2>
<thing3>d</thing3>
<thing4>e</thing4>
<thing5>f</thing5>
<thing6>g</thing6>
<thing7>h</thing7>
<thing8>i</thing8>
<thing9>j</thing9>
<thing1>aa</thing1>
<thing2>bb</thing2>
<thing3>dd</thing3>
<thing4>ee</thing4>
<thing5>ff</thing5>
<thing6>gg</thing6>
<thing7>hh</thing7>
<thing8>ii</thing8>
<thing9>jj</thing9>
<unit_id>1</unit_id>
<value>1</value>
<thing2.2>k</thing2.2>
<thing3.2>c</thing3.2>
<thing4.2>d</thing4.2>
<thing10>o</thing10>
<thing11></thing11>
<thing12></thing12>
<thing13></thing13>
</ltOverride>
<ltOverride>
<thing1>a</thing1>
<thing2>c</thing2>
<thing3>d</thing3>
<thing4>e</thing4>
<thing5>f</thing5>
<thing6>g</thing6>
<thing7>h</thing7>
<thing8>i</thing8>
<thing9>j</thing9>
<thing1>aa</thing1>
<thing2>bb</thing2>
<thing3>dd</thing3>
<thing4>ee</thing4>
<thing5>ff</thing5>
<thing6>gg</thing6>
<thing7>hh</thing7>
<thing8>ii</thing8>
<thing9>jj</thing9>
<unit_id>2</unit_id>
<value>1</value>
<thing2.2>l</thing2.2>
<thing3.2>c</thing3.2>
<thing4.2>d</thing4.2>
<thing10></thing10>
<thing11>p</thing11>
<thing12></thing12>
<thing13></thing13>
</ltOverride>
<ltOverride>
<thing1>a</thing1>
<thing2>c</thing2>
<thing3>d</thing3>
<thing4>e</thing4>
<thing5>f</thing5>
<thing6>g</thing6>
<thing7>h</thing7>
<thing8>i</thing8>
<thing9>j</thing9>
<thing1>aa</thing1>
<thing2>bb</thing2>
<thing3>dd</thing3>
<thing4>ee</thing4>
<thing5>ff</thing5>
<thing6>gg</thing6>
<thing7>hh</thing7>
<thing8>ii</thing8>
<thing9>jj</thing9>
<unit_id>3</unit_id>
<value>1</value>
<thing2.2>m</thing2.2>
<thing3.2>c</thing3.2>
<thing4.2>d</thing4.2>
<thing10></thing10>
<thing11></thing11>
<thing12>q</thing12>
<thing13></thing13>
</ltOverride>
<ltOverride>
<thing1>a</thing1>
<thing2>c</thing2>
<thing3>d</thing3>
<thing4>e</thing4>
<thing5>f</thing5>
<thing6>g</thing6>
<thing7>h</thing7>
<thing8>i</thing8>
<thing9>j</thing9>
<thing1>aa</thing1>
<thing2>bb</thing2>
<thing3>dd</thing3>
<thing4>ee</thing4>
<thing5>ff</thing5>
<thing6>gg</thing6>
<thing7>hh</thing7>
<thing8>ii</thing8>
<thing9>jj</thing9>
<unit_id>4</unit_id>
<value>1</value>
<thing2.2>n</thing2.2>
<thing3.2>c</thing3.2>
<thing4.2>d</thing4.2>
<thing10></thing10>
<thing11></thing11>
<thing12></thing12>
<thing13>r</thing13>
</ltOverride>
<ltOverride>
<thing1>a</thing1>
<thing2>c</thing2>
<thing3>d</thing3>
<thing4>e</thing4>
<thing5>f</thing5>
<thing6>g</thing6>
<thing7>h</thing7>
<thing8>i</thing8>
<thing9>j</thing9>
<thing1>aa</thing1>
<thing2>bb</thing2>
<thing3>dd</thing3>
<thing4>ee</thing4>
<thing5>ff</thing5>
<thing6>gg</thing6>
<thing7>hh</thing7>
<thing8>ii</thing8>
<thing9>jj</thing9>
<unit_id>11</unit_id>
<value>11</value>
<thing2.2>kk</thing2.2>
<thing3.2>cc</thing3.2>
<thing4.2>dd</thing4.2>
<thing10>oo</thing10>
<thing11></thing11>
<thing12></thing12>
<thing13></thing13>
</ltOverride>
<ltOverride>
<thing1>a</thing1>
<thing2>c</thing2>
<thing3>d</thing3>
<thing4>e</thing4>
<thing5>f</thing5>
<thing6>g</thing6>
<thing7>h</thing7>
<thing8>i</thing8>
<thing9>j</thing9>
<thing1>aa</thing1>
<thing2>bb</thing2>
<thing3>dd</thing3>
<thing4>ee</thing4>
<thing5>ff</thing5>
<thing6>gg</thing6>
<thing7>hh</thing7>
<thing8>ii</thing8>
<thing9>jj</thing9>
<unit_id>22</unit_id>
<value>12</value>
<thing2.2>ll</thing2.2>
<thing3.2>cc</thing3.2>
<thing4.2>dd</thing4.2>
<thing10></thing10>
<thing11>pp</thing11>
<thing12></thing12>
<thing13></thing13>
</ltOverride>
<ltOverride>
<thing1>a</thing1>
<thing2>c</thing2>
<thing3>d</thing3>
<thing4>e</thing4>
<thing5>f</thing5>
<thing6>g</thing6>
<thing7>h</thing7>
<thing8>i</thing8>
<thing9>j</thing9>
<thing1>aa</thing1>
<thing2>bb</thing2>
<thing3>dd</thing3>
<thing4>ee</thing4>
<thing5>ff</thing5>
<thing6>gg</thing6>
<thing7>hh</thing7>
<thing8>ii</thing8>
<thing9>jj</thing9>
<unit_id>33</unit_id>
<value>13</value>
<thing2.2>mm</thing2.2>
<thing3.2>cc</thing3.2>
<thing4.2>dd</thing4.2>
<thing10></thing10>
<thing11></thing11>
<thing12>qq</thing12>
<thing13></thing13>
</ltOverride>
<ltOverride>
<thing1>a</thing1>
<thing2>c</thing2>
<thing3>d</thing3>
<thing4>e</thing4>
<thing5>f</thing5>
<thing6>g</thing6>
<thing7>h</thing7>
<thing8>i</thing8>
<thing9>j</thing9>
<thing1>aa</thing1>
<thing2>bb</thing2>
<thing3>dd</thing3>
<thing4>ee</thing4>
<thing5>ff</thing5>
<thing6>gg</thing6>
<thing7>hh</thing7>
<thing8>ii</thing8>
<thing9>jj</thing9>
<unit_id>44</unit_id>
<value>14</value>
<thing2.2>nn</thing2.2>
<thing3.2>cc</thing3.2>
<thing4.2>dd</thing4.2>
<thing10></thing10>
<thing11></thing11>
<thing12></thing12>
<thing13>rr</thing13>
</ltOverride>
</LaborTaskInterface>Solution 1:[1]
I believe you will have to make a radical change to your approach. Maybe something like this:
XSLT 1.0
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
extension-element-prefixes="msxsl">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:key name="attr-by-name" match="ltOverride/@*" use="name()"/>
<xsl:key name="value" match="ltOverride/@*" use="concat(name(), generate-id(..))"/>
<xsl:template match="/LaborTaskInterface">
<xsl:variable name="xml" select="/" />
<!-- base -->
<xsl:variable name="base-attr" select="LaborTask/@*"/>
<xsl:variable name="base-elems">
<xsl:for-each select="$base-attr">
<xsl:element name="{name()}">
<xsl:value-of select="." />
</xsl:element>
</xsl:for-each>
</xsl:variable>
<!-- override -->
<xsl:variable name="override-columns">
<xsl:for-each select="LaborTask/ltOverride/@*[generate-id() = generate-id(key('attr-by-name', name())[1])]">
<col name="{name()}"/>
</xsl:for-each>
</xsl:variable>
<!-- output -->
<xsl:copy>
<xsl:for-each select="LaborTask/ltOverride">
<xsl:variable name="id" select="generate-id()" />
<xsl:copy>
<xsl:copy-of select="$base-elems"/>
<xsl:for-each select="msxsl:node-set($override-columns)/col">
<xsl:variable name="name" select="@name" />
<xsl:variable name="dup">
<xsl:if test="$base-attr[name() = $name]">.2</xsl:if>
</xsl:variable>
<xsl:element name="{@name}{$dup}">
<!-- switch context back to input document in order to use key -->
<xsl:for-each select="$xml">
<xsl:value-of select="key('value', concat($name, $id))" />
</xsl:for-each>
</xsl:element>
</xsl:for-each>
</xsl:copy>
</xsl:for-each>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
AFAICT the result is identical to the expected output, except for the order of elements representing the columns.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | michael.hor257k |