xmlunix

How to add in unix a field from an xml file to a different section of that same xml file


I have a problem with an xml file. It has a section called creditnotes and inside a section called items that can have one or more subsection item.

My etl tool is not able to load both sections at the same time, so I have to do it in separate steps but I need to be able to join the information from creditnote with item.

Example of what I have:

<creditnote>
<creditmemo_increment_id>000000003</creditmemo_increment_id>
<creditmemo_entity_id>3</creditmemo_entity_id>
<order_increment_id>000000378</order_increment_id>
<order_id>371</order_id>
<total_refunded>2908.9300</total_refunded>
<tax_refunded>543.9500</tax_refunded>
<shipping_refunded>0.0000</shipping_refunded>
<shipping_tax_refunded>0.0000</shipping_tax_refunded>
<creditmemo_created_at/>
<creditmemo_updated_at/>
<payment>
<method>stripe_payments</method>
<method_title>Pay online</method_title>
<amount_refunded>2908.9300</amount_refunded>
<base_amount_refunded>2908.9300</base_amount_refunded>
</payment>
<items>
<item>
<sku>31363</sku>
<name>iPhone 16 Pro 256GB Natural Titanium</name>
<qty_ordered>2.0000</qty_ordered>
<qty_refunded>2.0000</qty_refunded>
<price>1169.9900</price>
<row_total>2339.9800</row_total>
<tax_amount>538.2000</tax_amount>
<discount_amount>0.0000</discount_amount>
<tax_refunded>538.2000</tax_refunded>
<qty_refunded>2.0000</qty_refunded>
</item>
<item>
<sku>3Broadband Unlimited 5G Gold Outdoor</sku>
<name>3Broadband Unlimited 5G Gold Outdoor [5G_outdoor]</name>
<qty_ordered>1.0000</qty_ordered>
<qty_refunded>1.0000</qty_refunded>
<price>25.0000</price>
<row_total>25.0000</row_total>
<tax_amount>5.7500</tax_amount>
<discount_amount>0.0000</discount_amount>
<tax_refunded>5.7500</tax_refunded>
<qty_refunded>1.0000</qty_refunded>
</item>
</items>
</creditnote>
<creditnote>
<creditmemo_increment_id>000000001</creditmemo_increment_id>
<creditmemo_entity_id>1</creditmemo_entity_id>
<order_increment_id>000000155</order_increment_id>
<order_id>153</order_id>
<total_refunded>13.5300</total_refunded>
<items>
<item>
<sku>48009</sku>
<name>DL My Charger Lightning Disp</name>
<qty_ordered>1.0000</qty_ordered>
<qty_refunded>1.0000</qty_refunded>
<price>11.0000</price>
<row_total>11.0000</row_total>
<tax_amount>2.5300</tax_amount>
<discount_amount>0.0000</discount_amount>
<tax_refunded>2.5300</tax_refunded>
<qty_refunded>1.0000</qty_refunded>
</item>
</items>
</creditnote>

What I want is to add inside of each item the field order_id from the related creditnote section (keep in mind there's more than one creditnote and items section)

I can figure out how to do it

This is what I want to get

<creditnote>
<creditmemo_increment_id>000000003</creditmemo_increment_id>
<creditmemo_entity_id>3</creditmemo_entity_id>
<order_increment_id>000000378</order_increment_id>
<order_id>371</order_id>
<total_refunded>2908.9300</total_refunded>
<tax_refunded>543.9500</tax_refunded>
<shipping_refunded>0.0000</shipping_refunded>
<shipping_tax_refunded>0.0000</shipping_tax_refunded>
<creditmemo_created_at/>
<creditmemo_updated_at/>
<payment>
<method>stripe_payments</method>
<method_title>Pay online</method_title>
<amount_refunded>2908.9300</amount_refunded>
<base_amount_refunded>2908.9300</base_amount_refunded>
</payment>
<items>
<item>
<order_id>371</order_id>
<sku>31363</sku>
<name>iPhone 16 Pro 256GB Natural Titanium</name>
<qty_ordered>2.0000</qty_ordered>
<qty_refunded>2.0000</qty_refunded>
<price>1169.9900</price>
<row_total>2339.9800</row_total>
<tax_amount>538.2000</tax_amount>
<discount_amount>0.0000</discount_amount>
<tax_refunded>538.2000</tax_refunded>
<qty_refunded>2.0000</qty_refunded>
</item>
<item>
<order_id>371</order_id>
<sku>3Broadband Unlimited 5G Gold Outdoor</sku>
<name>3Broadband Unlimited 5G Gold Outdoor [5G_outdoor]</name>
<qty_ordered>1.0000</qty_ordered>
<qty_refunded>1.0000</qty_refunded>
<price>25.0000</price>
<row_total>25.0000</row_total>
<tax_amount>5.7500</tax_amount>
<discount_amount>0.0000</discount_amount>
<tax_refunded>5.7500</tax_refunded>
<qty_refunded>1.0000</qty_refunded>
</item>
</items>
</creditnote>
<creditnote>
<creditmemo_increment_id>000000001</creditmemo_increment_id>
<creditmemo_entity_id>1</creditmemo_entity_id>
<order_increment_id>000000155</order_increment_id>
<order_id>153</order_id>
<total_refunded>13.5300</total_refunded>
<items>
<item>
<order_id>153</order_id>
<sku>48009</sku>
<name>DL My Charger Lightning Disp</name>
<qty_ordered>1.0000</qty_ordered>
<qty_refunded>1.0000</qty_refunded>
<price>11.0000</price>
<row_total>11.0000</row_total>
<tax_amount>2.5300</tax_amount>
<discount_amount>0.0000</discount_amount>
<tax_refunded>2.5300</tax_refunded>
<qty_refunded>1.0000</qty_refunded>
</item>
</items>
</creditnote>

Solution

  • It seems that xsltproc is currently not supporting XSLT 3.0 standard on Linux.

    Here is XSLT 1.0 conformant implementation. It is more verbose, and using the same Identity Transform XSLT pattern.

    I had to fix the XML sample to make it well-formed. It is missing a root element.

    To launch it at the command prompt:

    xsltproc -o result.xml transform.xslt input.xml
    

    Input XML

    
    <creditnotes>
        <creditnote>
            <creditmemo_increment_id>000000003</creditmemo_increment_id>
            <creditmemo_entity_id>3</creditmemo_entity_id>
            <order_increment_id>000000378</order_increment_id>
            <order_id>371</order_id>
            <total_refunded>2908.9300</total_refunded>
            <tax_refunded>543.9500</tax_refunded>
            <shipping_refunded>0.0000</shipping_refunded>
            <shipping_tax_refunded>0.0000</shipping_tax_refunded>
            <creditmemo_created_at/>
            <creditmemo_updated_at/>
            <payment>
                <method>stripe_payments</method>
                <method_title>Pay online</method_title>
                <amount_refunded>2908.9300</amount_refunded>
                <base_amount_refunded>2908.9300</base_amount_refunded>
            </payment>
            <items>
                <item>
                    <sku>31363</sku>
                    <name>iPhone 16 Pro 256GB Natural Titanium</name>
                    <qty_ordered>2.0000</qty_ordered>
                    <qty_refunded>2.0000</qty_refunded>
                    <price>1169.9900</price>
                    <row_total>2339.9800</row_total>
                    <tax_amount>538.2000</tax_amount>
                    <discount_amount>0.0000</discount_amount>
                    <tax_refunded>538.2000</tax_refunded>
                    <qty_refunded>2.0000</qty_refunded>
                </item>
                <item>
                    <sku>3Broadband Unlimited 5G Gold Outdoor</sku>
                    <name>3Broadband Unlimited 5G Gold Outdoor [5G_outdoor]</name>
                    <qty_ordered>1.0000</qty_ordered>
                    <qty_refunded>1.0000</qty_refunded>
                    <price>25.0000</price>
                    <row_total>25.0000</row_total>
                    <tax_amount>5.7500</tax_amount>
                    <discount_amount>0.0000</discount_amount>
                    <tax_refunded>5.7500</tax_refunded>
                    <qty_refunded>1.0000</qty_refunded>
                </item>
            </items>
        </creditnote>
        <creditnote>
            <creditmemo_increment_id>000000001</creditmemo_increment_id>
            <creditmemo_entity_id>1</creditmemo_entity_id>
            <order_increment_id>000000155</order_increment_id>
            <order_id>153</order_id>
            <total_refunded>13.5300</total_refunded>
            <items>
                <item>
                    <sku>48009</sku>
                    <name>DL My Charger Lightning Disp</name>
                    <qty_ordered>1.0000</qty_ordered>
                    <qty_refunded>1.0000</qty_refunded>
                    <price>11.0000</price>
                    <row_total>11.0000</row_total>
                    <tax_amount>2.5300</tax_amount>
                    <discount_amount>0.0000</discount_amount>
                    <tax_refunded>2.5300</tax_refunded>
                    <qty_refunded>1.0000</qty_refunded>
                </item>
            </items>
        </creditnote>
    </creditnotes>
    
    

    XSLT 1.0

    <xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
        <xsl:output method="xml" indent="yes" omit-xml-declaration="yes"/>
        <xsl:strip-space elements="*"/>
        
        <!-- Identity Transform -->
        <xsl:template match="node()|@*">
            <xsl:copy>
                <xsl:apply-templates select="node()|@*"/>
            </xsl:copy>
        </xsl:template>
        
        <xsl:template match="item">
            <xsl:copy>
                <xsl:copy-of select="../../order_id"/>
                <xsl:copy-of select="./*"/>
            </xsl:copy>
        </xsl:template>
    </xsl:transform>
    

    Output XML

    <creditnotes>
       <creditnote>
          <creditmemo_increment_id>000000003</creditmemo_increment_id>
          <creditmemo_entity_id>3</creditmemo_entity_id>
          <order_increment_id>000000378</order_increment_id>
          <order_id>371</order_id>
          <total_refunded>2908.9300</total_refunded>
          <tax_refunded>543.9500</tax_refunded>
          <shipping_refunded>0.0000</shipping_refunded>
          <shipping_tax_refunded>0.0000</shipping_tax_refunded>
          <creditmemo_created_at/>
          <creditmemo_updated_at/>
          <payment>
             <method>stripe_payments</method>
             <method_title>Pay online</method_title>
             <amount_refunded>2908.9300</amount_refunded>
             <base_amount_refunded>2908.9300</base_amount_refunded>
          </payment>
          <items>
             <item>
                <order_id>371</order_id>
                <sku>31363</sku>
                <name>iPhone 16 Pro 256GB Natural Titanium</name>
                <qty_ordered>2.0000</qty_ordered>
                <qty_refunded>2.0000</qty_refunded>
                <price>1169.9900</price>
                <row_total>2339.9800</row_total>
                <tax_amount>538.2000</tax_amount>
                <discount_amount>0.0000</discount_amount>
                <tax_refunded>538.2000</tax_refunded>
                <qty_refunded>2.0000</qty_refunded>
             </item>
             <item>
                <order_id>371</order_id>
                <sku>3Broadband Unlimited 5G Gold Outdoor</sku>
                <name>3Broadband Unlimited 5G Gold Outdoor [5G_outdoor]</name>
                <qty_ordered>1.0000</qty_ordered>
                <qty_refunded>1.0000</qty_refunded>
                <price>25.0000</price>
                <row_total>25.0000</row_total>
                <tax_amount>5.7500</tax_amount>
                <discount_amount>0.0000</discount_amount>
                <tax_refunded>5.7500</tax_refunded>
                <qty_refunded>1.0000</qty_refunded>
             </item>
          </items>
       </creditnote>
       <creditnote>
          <creditmemo_increment_id>000000001</creditmemo_increment_id>
          <creditmemo_entity_id>1</creditmemo_entity_id>
          <order_increment_id>000000155</order_increment_id>
          <order_id>153</order_id>
          <total_refunded>13.5300</total_refunded>
          <items>
             <item>
                <order_id>153</order_id>
                <sku>48009</sku>
                <name>DL My Charger Lightning Disp</name>
                <qty_ordered>1.0000</qty_ordered>
                <qty_refunded>1.0000</qty_refunded>
                <price>11.0000</price>
                <row_total>11.0000</row_total>
                <tax_amount>2.5300</tax_amount>
                <discount_amount>0.0000</discount_amount>
                <tax_refunded>2.5300</tax_refunded>
                <qty_refunded>1.0000</qty_refunded>
             </item>
          </items>
       </creditnote>
    </creditnotes>