flatten xml structure

[Balaji_Ponnam]

Hi Everyone,

I am trying to flatten an XML structure and convert it into a CSV file. I tried using the message mapping but could not get it. Please let me know how we can achieve this.

Input XML
<User>
    <User>
      <matrixManager>
        <User>
          <userId>MM1</userId>
        </User>
        <User>
          <userId>MM2</userId>
        </User>
      </matrixManager>
      <manager>
        <User>
          <userId>MGR1</userId>
        </User>
      </manager>
      <userId>USR01</userId>
      <username>USRNM01</username>
    </User>
    <User>
      <matrixManager>
        <User>
          <userId>MM3</userId>
        </User>
      </matrixManager>
      <manager>
        <User>
          <userId>MGR2</userId>
        </User>
      </manager>
      <userId>USR02</userId>
      <username>USRNM02</username>
    </User>    
</User>

Output XML or CSV file
<User>
    <User>
      <matrixManager>MM1</matrixManager>
      <matrixManager>MM2</matrixManager>
      <manager>MGR1</manager>
      <userId>USR01</userId>
      <username>USRNM01</username>
    </User>
    <User>
      <matrixManager>MM3</matrixManager>
      <manager>MGR2</manager>
      <userId>USR02</userId>
      <username>USRNM02</username>
    </User>    
</User>

Regards,

[former_member1924 · Accepted solution]

Hi,

could you try this

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="3.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:output method="xml" indent="yes" omit-xml-declaration="yes" />
	<xsl:template match="User"> 
		<User>
			<xsl:for-each select="User">
				<User>
					<xsl:for-each select="matrixManager/User">
						<matrixManager><xsl:value-of select="userId" /></matrixManager>
					</xsl:for-each>	
					<xsl:for-each select="manager/User">
						<manager><xsl:value-of select="userId" /></manager>
					</xsl:for-each>
					<userId><xsl:value-of select="userId" /></userId>
				</User>
			</xsl:for-each>	
		</User>
	</xsl:template>
</xsl:stylesheet>

[former_member1924]

Hi Balaji,

Please refer below link.

https://answers.sap.com/questions/13036118/flatten-a-nested-xml-hci.html?childToView=13035144#answer...

hope this helps!

thanks and regards,

Praveen T

[Balaji_Ponnam]

Hi Praveen,

I tried similar approach but the output xml is including all values for each node.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="3.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:output method="xml" indent="yes" omit-xml-declaration="yes" />
	<xsl:template match="User"> 
            <User>
                               <matrixManager>
					<xsl:for-each select="User/matrixManager/User">
						<userId><xsl:value-of select="userId" /></userId>
					</xsl:for-each>	
				</matrixManager>
                               <manager>
					<xsl:for-each select="User/manager/User">
						<userId><xsl:value-of select="userId" /></userId>
					</xsl:for-each>	
				</manager>
				<userId><xsl:value-of select="User/userId" /></userId>         </User>	
	</xsl:template>
</xsl:stylesheet>


Output --


<User>
   <matrixManager>
      <userId>206466466</userId>
      <userId>206466455</userId>
      <userId>206471844</userId>
   </matrixManager>
   <manager>
      <userId>206448407</userId>
      <userId>206471844</userId>
   </manager>
   <userId>206462347 206471855</userId>
</User>

Regards,

Balaji Ponnam