Hi have a complex XML structured as per attached below. How can I flatten the XML using XSLT? I have attached the expected sample flatten xml( expected_ouput_flat_oder_xml.xml ) based on the complex xml( Order_xml.xml ) I provided. I have tried using the XSLT code I written but I didnt get the output I want. I have shown below the XLST I wrote as well for your reference. Anyone able to help me with this? Appreciate the response. expected_ouput_flat_oder_xml.xml Order_xml.xml <?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="xml" indent="yes"/> <xsl:template match="/Orders"> <Orders> <xsl:for-each select="Order"> <xsl:variable name="orderID" select="OrderID"/> <xsl:variable name="customerID" select="CustomerID"/> <xsl:variable name="orderDate" select="OrderDate"/> <xsl:for-each select="OrderDetails/Detail"> <xsl:variable name="productID" select="ProductID"/> <xsl:variable name="quantity" select="Quantity"/> <xsl:for-each select="../..//ShippedRemarks"> <xsl:variable name="ShipID" select="ShipID"/> <xsl:variable name="ShipQuantity" select="ShipQuantity"/> <xsl:for-each select="../../..//DetailName"> <xsl:variable name="productName" select="ProductName"/> <xsl:variable name="PaymentReqDate" select="PaymentReqDate"/> <xsl:variable name="PaymentAmt" select="PaymentAmt"/> <xsl:variable name="PaymentMode" select="PaymentMode"/> <xsl:for-each select="../..//PaymentInfo"> <Order> <OrderID><xsl:value-of select="$orderID"/></OrderID> <CustomerID><xsl:value-of select="$customerID"/></CustomerID> <OrderDate><xsl:value-of select="$orderDate"/></OrderDate> <ProductID><xsl:value-of select="$productID"/></ProductID> <Quantity><xsl:value-of select="$quantity"/></Quantity> <ShipID><xsl:value-of select="$ShipID"/></ShipID> <ShipQuantity><xsl:value-of select="$ShipQuantity"/></ShipQuantity> <ProductName><xsl:value-of select="$productName"/></ProductName> <PaymentVchNo><xsl:value-of select="PaymentVchNo"/></PaymentVchNo> <PaymentReqDate><xsl:value-of select="PaymentReqDate"/></PaymentReqDate> <PaymentAmt><xsl:value-of select="PaymentAmt"/></PaymentAmt> <PaymentMode><xsl:value-of select="PaymentMode"/></PaymentMode> </Order> </xsl:for-each> </xsl:for-each> </xsl:for-each> </xsl:for-each> </xsl:for-each> </Orders> </xsl:template> </xsl:stylesheet>

How to flatten the XML - Microsoft Q&A

Accepted answer

Viorel 114.1K

Try this:

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

    <xsl:template match="/Orders">
        <Orders>
            <xsl:apply-templates select="Order"/>
        </Orders>
    </xsl:template>

    <xsl:template match="Order">
        <xsl:variable name="o" select="."/>
        <xsl:variable name="pi" select="PaymentInfo"/>

        <xsl:for-each select="OrderDetails/Detail/ShippedRemarks">
            <xsl:variable name="shr" select="."/>
            <xsl:variable name="d" select=".."/>

            <xsl:for-each select="$o/ProductDetails/DetailName">
                <xsl:variable name="dn" select="."/>

                <Order>
                    <xsl:copy-of select="$o/OrderID | $o/CustomerID | $o/OrderDate"/>
                    <xsl:copy-of select="$d/ProductID | $d/Quantity"/>
                    <xsl:copy-of select="$shr/*"/>
                    <xsl:copy-of select="$dn/ProductName"/>
                    <xsl:copy-of select="$pi/*"/>
                </Order>

            </xsl:for-each>
        </xsl:for-each>
    </xsl:template>

</xsl:stylesheet>

Instead of “*”, you can specify the names explicitly.

jn93 671

Hi Viorel, Thanks for the solution. I have modified your xslt to improve handling the relationship. Thank you so much again.

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

    <xsl:template match="/Orders">
        <Orders>
            <xsl:apply-templates select="Order"/>
        </Orders>
    </xsl:template>

    <xsl:template match="Order">
        <xsl:variable name="o" select="."/>


        <xsl:for-each select="OrderDetails/Detail/ShippedRemarks">
            <xsl:variable name="shr" select="."/>
            <xsl:variable name="d" select=".."/>

            <xsl:for-each select="$o/ProductDetails/DetailName">
                <xsl:variable name="dn" select="."/>
				
	            <xsl:for-each select="$o/PaymentInfo">
					<xsl:variable name="pi" select="."/>		

                <Order>
                    <xsl:copy-of select="$o/OrderID | $o/CustomerID | $o/OrderDate"/>
                    <xsl:copy-of select="$d/ProductID | $d/Quantity"/>
                    <xsl:copy-of select="$shr/*"/>
                    <xsl:copy-of select="$dn/ProductName"/>
                    <xsl:copy-of select="$pi/*"/>
                </Order>
                
				</xsl:for-each>
            </xsl:for-each>
        </xsl:for-each>
    </xsl:template>

</xsl:stylesheet>

jn93 671

Hi @Viorel Currently the XSLT is working fine. But do you know on how to handle empty nodes. Currently that particular order is not populated under flat xml if any of the nodes is missing like for example below the payment info is missing for OrderID=1.

I would like to include OrderID=1 as well but without payment info(as per attached in( expected_ouput_flat_oder_xml.xml) if the payment info nodes is missing. Appreciate it Thanks.

<Orders>
  <Order>
    <OrderID>1</OrderID>
    <CustomerID>ALFKI</CustomerID>
    <OrderDate>2023-06-01</OrderDate>
    <OrderDetails>
      <Detail>
        <ProductID>1</ProductID>
        <Quantity>10</Quantity>
		   <ShippedRemarks>
		         <ShipID>abc</ShipID>
                 <ShipQuantity>10</ShipQuantity>
		   </ShippedRemarks>
      </Detail>
      <Detail>
        <ProductID>2</ProductID>
        <Quantity>7</Quantity>
		    <ShippedRemarks>
		         <ShipID>def</ShipID>
                 <ShipQuantity>70</ShipQuantity>
		   </ShippedRemarks>		
      </Detail>	  
    </OrderDetails>
    <ProductDetails>
      <DetailName>
        <ProductName></ProductName>
      </DetailName>
    </ProductDetails>		
  </Order>
   <Order>
    <OrderID>2</OrderID>
    <CustomerID>ATKL</CustomerID>
    <OrderDate>2024-06-01</OrderDate>
    <OrderDetails>
      <Detail>
        <ProductID>1</ProductID>
        <Quantity>10</Quantity>
		    <ShippedRemarks>
		         <ShipID>def</ShipID>
                 <ShipQuantity>70</ShipQuantity>
		   </ShippedRemarks>			
      </Detail>
      <Detail>
        <ProductID>2</ProductID>
        <Quantity>5</Quantity>
		    <ShippedRemarks>
		         <ShipID>def</ShipID>
                 <ShipQuantity>80</ShipQuantity>
		   </ShippedRemarks>			
      </Detail>
    </OrderDetails>
    <ProductDetails>
      <DetailName>
        <ProductName>NA</ProductName>
      </DetailName>
      <DetailName>
        <ProductName>bs</ProductName>
      </DetailName>	  
    </ProductDetails>	
	<PaymentInfo>
		<PaymentVchNo></PaymentVchNo>
		<PaymentReqDate>07/12/2023</PaymentReqDate>
		<PaymentAmt>1.88</PaymentAmt>
		<PaymentMode></PaymentMode>
	</PaymentInfo>	
	<PaymentInfo>
		<PaymentVchNo></PaymentVchNo>
		<PaymentReqDate>08/01/2024</PaymentReqDate>
		<PaymentAmt>2.77</PaymentAmt>
		<PaymentMode></PaymentMode>
	</PaymentInfo>	
  </Order> 
</Orders>

Viorel 114.1K

In case of PaymentInfo, try something like this:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:msxsl="urn:schemas-microsoft-com:xslt">

    <xsl:output method="xml" indent="yes"/>

    <xsl:template match="/Orders">
        <Orders>
            <xsl:apply-templates select="Order"/>
        </Orders>
    </xsl:template>

    <xsl:template match="Order">
        <xsl:variable name="o" select="."/>

        <xsl:for-each select="OrderDetails/Detail/ShippedRemarks">
            <xsl:variable name="shr" select="."/>
            <xsl:variable name="d" select=".."/>

            <xsl:for-each select="$o/ProductDetails/DetailName">
                <xsl:variable name="dn" select="."/>

                <xsl:variable name="epi">
                    <PaymentInfo/>
                </xsl:variable>

                <xsl:for-each select="$o/PaymentInfo | msxsl:node-set($epi)/PaymentInfo[not($o/PaymentInfo)]">
                    <xsl:variable name="pi" select="."/>

                    <Order>
                        <xsl:copy-of select="$o/OrderID | $o/CustomerID | $o/OrderDate"/>
                        <xsl:copy-of select="$d/ProductID | $d/Quantity"/>
                        <xsl:copy-of select="$shr/*"/>
                        <xsl:copy-of select="$dn/ProductName"/>
                        <xsl:copy-of select="$pi/*"/>
                    </Order>

                </xsl:for-each>
            </xsl:for-each>
        </xsl:for-each>
    </xsl:template>

</xsl:stylesheet>

jn93 671

Hi, Good Day. Your code is working great. I try to readjust the code if there missing node happen for others but its not get the output I want. Since I'm new in XSLT, I try to digest and understand the XSLT. What if there is a missing nodes for ShippedRemarks or Detail. Is there a way to handle since Detail is the parent node for ShippedRemarks. Here below is the expected output if ShippedRemarks is missing.

<?xml version="1.0" encoding="utf-8"?>
<Orders>
  <Order>
    <OrderID>1</OrderID>
    <CustomerID>ALFKI</CustomerID>
    <OrderDate>2023-06-01</OrderDate>
    <ProductID>1</ProductID>
    <Quantity>10</Quantity>
    <ProductName />
  </Order>
  <Order>
    <OrderID>1</OrderID>
    <CustomerID>ALFKI</CustomerID>
    <OrderDate>2023-06-01</OrderDate>
    <ProductID>2</ProductID>
    <Quantity>7</Quantity>
    <ProductName />
  </Order>
  <Order>
    <OrderID>2</OrderID>
    <CustomerID>ATKL</CustomerID>
    <OrderDate>2024-06-01</OrderDate>
    <ProductID>1</ProductID>
    <Quantity>10</Quantity>
    <ShipID>def</ShipID>
    <ShipQuantity>70</ShipQuantity>
    <ProductName>NA</ProductName>
    <PaymentVchNo />
    <PaymentReqDate>07/12/2023</PaymentReqDate>
    <PaymentAmt>1.88</PaymentAmt>
    <PaymentMode />
  </Order>
  <Order>
    <OrderID>2</OrderID>
    <CustomerID>ATKL</CustomerID>
    <OrderDate>2024-06-01</OrderDate>
    <ProductID>1</ProductID>
    <Quantity>10</Quantity>
    <ShipID>def</ShipID>
    <ShipQuantity>70</ShipQuantity>
    <ProductName>NA</ProductName>
    <PaymentVchNo />
    <PaymentReqDate>08/01/2024</PaymentReqDate>
    <PaymentAmt>2.77</PaymentAmt>
    <PaymentMode />
  </Order>
  <Order>
    <OrderID>2</OrderID>
    <CustomerID>ATKL</CustomerID>
    <OrderDate>2024-06-01</OrderDate>
    <ProductID>1</ProductID>
    <Quantity>10</Quantity>
    <ShipID>def</ShipID>
    <ShipQuantity>70</ShipQuantity>
    <ProductName>bs</ProductName>
    <PaymentVchNo />
    <PaymentReqDate>07/12/2023</PaymentReqDate>
    <PaymentAmt>1.88</PaymentAmt>
    <PaymentMode />
  </Order>
  <Order>
    <OrderID>2</OrderID>
    <CustomerID>ATKL</CustomerID>
    <OrderDate>2024-06-01</OrderDate>
    <ProductID>1</ProductID>
    <Quantity>10</Quantity>
    <ShipID>def</ShipID>
    <ShipQuantity>70</ShipQuantity>
    <ProductName>bs</ProductName>
    <PaymentVchNo />
    <PaymentReqDate>08/01/2024</PaymentReqDate>
    <PaymentAmt>2.77</PaymentAmt>
    <PaymentMode />
  </Order>
  <Order>
    <OrderID>2</OrderID>
    <CustomerID>ATKL</CustomerID>
    <OrderDate>2024-06-01</OrderDate>
    <ProductID>2</ProductID>
    <Quantity>5</Quantity>
    <ShipID>def</ShipID>
    <ShipQuantity>80</ShipQuantity>
    <ProductName>NA</ProductName>
    <PaymentVchNo />
    <PaymentReqDate>07/12/2023</PaymentReqDate>
    <PaymentAmt>1.88</PaymentAmt>
    <PaymentMode />
  </Order>
  <Order>
    <OrderID>2</OrderID>
    <CustomerID>ATKL</CustomerID>
    <OrderDate>2024-06-01</OrderDate>
    <ProductID>2</ProductID>
    <Quantity>5</Quantity>
    <ShipID>def</ShipID>
    <ShipQuantity>80</ShipQuantity>
    <ProductName>NA</ProductName>
    <PaymentVchNo />
    <PaymentReqDate>08/01/2024</PaymentReqDate>
    <PaymentAmt>2.77</PaymentAmt>
    <PaymentMode />
  </Order>
  <Order>
    <OrderID>2</OrderID>
    <CustomerID>ATKL</CustomerID>
    <OrderDate>2024-06-01</OrderDate>
    <ProductID>2</ProductID>
    <Quantity>5</Quantity>
    <ShipID>def</ShipID>
    <ShipQuantity>80</ShipQuantity>
    <ProductName>bs</ProductName>
    <PaymentVchNo />
    <PaymentReqDate>07/12/2023</PaymentReqDate>
    <PaymentAmt>1.88</PaymentAmt>
    <PaymentMode />
  </Order>
  <Order>
    <OrderID>2</OrderID>
    <CustomerID>ATKL</CustomerID>
    <OrderDate>2024-06-01</OrderDate>
    <ProductID>2</ProductID>
    <Quantity>5</Quantity>
    <ShipID>def</ShipID>
    <ShipQuantity>80</ShipQuantity>
    <ProductName>bs</ProductName>
    <PaymentVchNo />
    <PaymentReqDate>08/01/2024</PaymentReqDate>
    <PaymentAmt>2.77</PaymentAmt>
    <PaymentMode />
  </Order>
</Orders>

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Viorel 114.1K

Check an adjusted XSLT:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:msxsl="urn:schemas-microsoft-com:xslt">

    <xsl:output method="xml" indent="yes"/>

    <xsl:template match="/Orders">
        <Orders>
            <xsl:apply-templates select="Order"/>
        </Orders>
    </xsl:template>

    <xsl:template match="Order">
        <xsl:variable name="o" select="."/>

        <xsl:variable name="ed">
            <Detail/>
        </xsl:variable>

        <xsl:for-each select="$o/OrderDetails/Detail | msxsl:node-set($ed)/Detail[not($o/OrderDetails/Detail)]">
            <xsl:variable name="d" select="."/>

            <xsl:variable name="eshr">
                <ShippedRemarks/>
            </xsl:variable>

            <xsl:for-each select="$d/ShippedRemarks | msxsl:node-set($eshr)/ShippedRemarks[not($d/ShippedRemarks)]">
                <xsl:variable name="shr" select="."/>

                <xsl:variable name="edn">
                    <DetailName/>
                </xsl:variable>

                <xsl:for-each select="$o/ProductDetails/DetailName | msxsl:node-set($edn)/DetailName[not($o/ProductDetails/DetailName)]">
                    <xsl:variable name="dn" select="."/>

                    <xsl:variable name="epi">
                        <PaymentInfo/>
                    </xsl:variable>

                    <xsl:for-each select="$o/PaymentInfo | msxsl:node-set($epi)/PaymentInfo[not($o/PaymentInfo)]">
                        <xsl:variable name="pi" select="."/>

                        <Order>
                            <xsl:copy-of select="$o/OrderID | $o/CustomerID | $o/OrderDate"/>
                            <xsl:copy-of select="$d/ProductID | $d/Quantity"/>
                            <xsl:copy-of select="$shr/*"/>
                            <xsl:copy-of select="$dn/ProductName"/>
                            <xsl:copy-of select="$pi/*"/>
                        </Order>

                    </xsl:for-each>
                </xsl:for-each>
            </xsl:for-each>
        </xsl:for-each>
    </xsl:template>

</xsl:stylesheet>

Share via

How to flatten the XML

0 additional answers