How to print the recursive XML data into CSV file using XSLT

Question

This is my sample XML data having only attributes data and having multiple child nodes,

<FIXML  s="...." v="....">
 <Batch ID="....">
  <MktDef MktID="XEUR" MktSegID="14" EfctvBizDt="2017-05-11" NxtEfctvBizDt="2017-05-15" MktSeg="CONF" MarketSegmentDesc="FUT 8-13 Y. SWISS GOV.BONDS 6%" Sym="CH0002741988" ParentMktSegmID="FBND" Ccy="CHF" MktSegStat="1" USFirmFlag="Y" PartID="2">
    <MtchRules MtchRuleProdCmplx="5" MtchAlgo="PT"/>
    <MtchRules MtchRuleProdCmplx="1" MtchAlgo="PT"/>
    <FlexProdEligs FlexProdEligCmplx="5" FlexProdElig="Y"/>
    <BaseTrdgRules QtSideInd="1" FastMktPctg="0">
        <TickRules TickRuleProdCmplx="1" StartTickPxRng="0" EndTickPxRng="99999.9999" TickIncr="0.01"/>
        <TickRules TickRuleProdCmplx="5" StartTickPxRng="0" EndTickPxRng="99999.9999" TickIncr="0.01"/>
        <QuotSizeRules MinBidSz="1" MinOfrSz="1" FastMktInd="0"/>
        <QuotSizeRules MinBidSz="1" MinOfrSz="1" FastMktInd="1"/>
        <PxRngRules PxRngRuleID="75" PxRngProdCmplx="1" StartPxRng="0" EndPxRng="99999.9999" PxRngValu="0.15"/>
        <PxRngRules PxRngRuleID="347" PxRngProdCmplx="5" StartPxRng="0" EndPxRng="99999.9999" PxRngValu="0.12"/>
     </BaseTrdgRules>
    <MDFeedTyps MDFeedTyp="HS" MDBkTyp="2" MktDepth="10" MDRcvryTmIntvl="120000" SvcLctnID1="224.0.50.102" SvcLctnSubID1="59500" SvcLctnID2="224.0.50.230" SvcLctnSubID2="59500"/>
    <MDFeedTyps MDFeedTyp="HI" MDBkTyp="2" MktDepth="10" MktDepthTmIntvl="0" SvcLctnID1="224.0.50.103" SvcLctnSubID1="59501" SvcLctnID2="224.0.50.231" SvcLctnSubID2="59501"/>
    <MDFeedTyps MDFeedTyp="HI" MDBkTyp="3" MktDepthTmIntvl="0" SvcLctnID1="224.0.114.97" SvcLctnSubID1="59501" SvcLctnID2="224.0.114.113" SvcLctnSubID2="59501"/>
    <MDFeedTyps MDFeedTyp="HS" MDBkTyp="3" SvcLctnID1="224.0.114.96" SvcLctnSubID1="59500" SvcLctnID2="224.0.114.112" SvcLctnSubID2="59500"/>
    <MDFeedTyps MDFeedTyp="L" MDBkTyp="2" MktDepth="5" MktDepthTmIntvl="3500" MDRcvryTmIntvl="30000" SvcLctnID1="224.0.50.89" SvcLctnSubID1="59500" SvcLctnID2="224.0.50.217" SvcLctnSubID2="59500"/>
</MktDef>
<SecDef PriSetPx="158.39">
    <Instrmt ID="408805" Src="M" SecTyp="FUT" Status="1" ProdCmplx="1" CFI="FFMPSX" MatDt="2017-06-08" MMY="201706" Mult="1" ValMeth="FUT" SettlMeth="P" SettlSubMeth="4" PxPrcsn="2" MinPxIncr="0.01" MinPxIncrAmt="10">
        <AID AltID="1048612" AltIDSrc="M"/>
        <AID AltID="XF000001RQD8" AltIDSrc="4"/>
    <Evnt EventTyp="7" Dt="2017-06-08"/>
    </Instrmt>
    <MktSegGrp MktSegID="14">
    <SecTrdgRules>
        <BaseTrdgRules>
            <PxRngRules PxRngRuleID="75"/>
        </BaseTrdgRules>
    </SecTrdgRules>
    </MktSegGrp>
</SecDef>
 .
 .
 .
 .
</Batch>
</FIXML>


"<MktDef> and <SecDef>" will be continued for N number of times.

Sample XSLT file:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format" >
  <xsl:output method="text" omit-xml-declaration="yes" indent="no"/>

  <xsl:template match="/">
     <xsl:text>MktID,MktSegID,PxRngRuleID,PxRngProdCmplx,StartPxRng,EndPxRng,PxRngValu,</xsl:text>

<xsl:for-each select="FIXML/Batch/MktDef">
  <xsl:value-of select="concat(@MktID,',',@MktSegID,',',ancestor::FIXML/Batch/MktDef/BaseTrdgRules/PxRngRules/@PxRngRuleID,',',@PxRngProdCmplx,',',@StartPxRng,',',@EndPxRng,',',@PxRngValu,'&#xA;')"/>
</xsl:for-each>

  </xsl:template>
</xsl:stylesheet>

Java code to convert XML data to csv:

import java.io.File;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.Result;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.stream.StreamSource;

import org.w3c.dom.Document;

class Xml2Csv {

    public static void main(String args[]) throws Exception {
        File stylesheet = new File("style.xsl");
        //File xmlSource = new File("Testing-1.xml");
        File xmlSource = new File("95FILRDF01PUBLI20170511XEUR6NJ92000.xml");

        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();
        Document document = builder.parse(xmlSource);

        StreamSource stylesource = new StreamSource(stylesheet);
        Transformer transformer = TransformerFactory.newInstance().newTransformer(stylesource);
        Source source = new DOMSource(document);
        System.out.println(source.toString());
        System.out.println("Hello");
        Result outputTarget = new StreamResult(new File("PxRngRules.csv"));
        transformer.transform(source, outputTarget);

    }
}

Here I want to get the output in the format below.

MktID   MktSegID    PxRngRuleID PxRngProdCmplx  StartPxRng  EndPxRng    PxRngValu
XEUR    19629            75           1            0    99999.9999  0.15
XEUR    65               75           1            0    99999.9999  0.15
XEUR    66               75           1            0    99999.9999  0.15
XEUR    67               75           1            0    99999.9999  0.15

The problem I is I am only getting first "PxRngRules" tag data. I am unable to get the all the occurrences of "PxRngRules".

Are you only interested MktDef entries that have PxRngRules children? — NMGod
– NMGod, Commented Jun 19, 2017 at 5:30
Nothing to do with your problem, but don't create a DOM just to provide the input to a transformation. Let the XSLT processor build the tree in its own way by providing a StreamSource. — Michael Kay
– Michael Kay, Commented Jun 19, 2017 at 7:21
Hi NMGod, Yes I need only MktDef entries that have PxRngRules children, — Sai Chaitanya Ramisetty
– Sai Chaitanya Ramisetty, Commented Jun 19, 2017 at 7:43
Hi Michael Kay, Can you please provide me a sample program or POC to do the transformation using StreamSource. Thank you! — Sai Chaitanya Ramisetty
– Sai Chaitanya Ramisetty, Commented Jun 19, 2017 at 7:45
So, do you actually want one row for each PxRngRules under a MktDef? Will there always be at least one PxRngRules under each MktDef element? — Tim C
– Tim C, Commented Jun 19, 2017 at 8:54

Tim C · Accepted Answer · 2017-06-19 09:38:48Z

1

I may have misunderstood your requirement, but you could try this XSLT, if you wanted multiple lines for each PxRngRules under a MktDef

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format" >
  <xsl:output method="text"/>

  <xsl:template match="/">
    <xsl:text>MktID,MktSegID,PxRngRuleID,PxRngProdCmplx,StartPxRng,EndPxRng,PxRngValu</xsl:text>
    <xsl:text>&#xA;</xsl:text>
    <xsl:for-each select="FIXML/Batch/MktDef">
      <xsl:variable name="mktDef" select="concat(@MktID,',',@MktSegID)" />
      <xsl:choose>
        <xsl:when test="BaseTrdgRules/PxRngRules">
          <xsl:for-each select="BaseTrdgRules/PxRngRules">
            <xsl:value-of select="concat($mktDef, ',', @PxRngRuleID,',',@PxRngProdCmplx,',',@StartPxRng,',',@EndPxRng,',',@PxRngValu,'&#xA;')"/>    
          </xsl:for-each>
        </xsl:when>
        <xsl:otherwise>
            <xsl:value-of select="concat($mktDef, ',,,,,&#xA;')"/>    
        </xsl:otherwise>
      </xsl:choose>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>

If there was always going to be at least one PxRngRules for each MktDef (or if you didn't want output MktDef elements with no PxRngRules you could simplify the code to this

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format" >
  <xsl:output method="text"/>

  <xsl:template match="/">
    <xsl:text>MktID,MktSegID,PxRngRuleID,PxRngProdCmplx,StartPxRng,EndPxRng,PxRngValu</xsl:text>
    <xsl:text>&#xA;</xsl:text>
    <xsl:for-each select="FIXML/Batch/MktDef">
      <xsl:variable name="mktDef" select="concat(@MktID,',',@MktSegID)" />
      <xsl:for-each select="BaseTrdgRules/PxRngRules">
        <xsl:value-of select="concat($mktDef, ',', @PxRngRuleID,',',@PxRngProdCmplx,',',@StartPxRng,',',@EndPxRng,',',@PxRngValu,'&#xA;')"/>    
      </xsl:for-each>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>

answered Jun 19, 2017 at 9:38

Tim C

70.7k14 gold badges77 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sai Chaitanya Ramisetty Over a year ago

Hi Tim C, Thank you for your reply. Its perfect. It is working fine.

Sai Chaitanya Ramisetty Over a year ago

Can I ask you something more on this?

Sai Chaitanya Ramisetty Over a year ago

If I want to add 2 more columns with the values "QtSideInd and FastMktPctg" in BaseTrdgRules tag, can you tell me how can I do that? Thank you very much for your support.

Tim C Over a year ago

You can use ../@QtSideInd in the concat statment to get the attribute value of the parent.

Sai Chaitanya Ramisetty Over a year ago

Hi Tim C, Could you please look into this post share your Answer. stackoverflow.com/questions/44632725/…. Thank you!

Collectives™ on Stack Overflow

How to print the recursive XML data into CSV file using XSLT

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related