Generic XSLT transformation of XML to CSV with dynamic multiple child node

Question

i am trying to convert xml to csv using XSLT. i am able to pull data to csv but not all child nodes. From root only 2 level child node are populating in csv. anything below level 3 child node are clubbed into one and populating in csv. i dont want to mention any elements names in xslt, as xml will be keep changing.

XML used

xml  <Tx>
<New>
    <Id>123456</Id>
    <Submitted>true</Submitted>
    <Buyer>
        <AcctOwnr>
            <Id>
                <Gender>Male</Gender>
            </Id>
            <City>GB</City>
        </AcctOwnr>
    </Buyer>
    <Seller>
        <AcctOwnr>
            <Id>
                <Gender>Female</Gender>
            </Id>
            <City>GB</City>
        </AcctOwnr>
    </Seller>
    <Order>
        <TrnsmssnInd>false</TrnsmssnInd>
    </Order>
    <Tx>
        <Date>2019-05-08</Date>
        <cty>DEAL</cty>
        <Qty>
            <Value Ccy="USD">5000</Value>
        </Qty>
        <Price>
            <Price>
                <Value>
                    <Amt Ccy="USD">95.1</Amt>
                </Value>
            </Price>
        </Price>
        <TradVn>XOFF</TradVn>
        <CtryOfBrnch>GB</CtryOfBrnch>
    </Tx>   
    <AddtlAttrbts>
        <TxInd>false</TxInd>
    </AddtlAttrbts>
</New></Tx>

XSLT

enter code here<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:template match="/*">
    <xsl:for-each select="*[1]/*">
        <xsl:value-of select="name()"/>
        <xsl:if test="position() != last()">, </xsl:if>
        <xsl:if test="position() = last()">
            <xsl:text>&#xD;</xsl:text>
        </xsl:if>
    </xsl:for-each>
    <xsl:apply-templates/>
</xsl:template>
<xsl:output method="text" encoding="iso-8859-1"/>
<xsl:param name="fieldNames" select="'yes'" />
<xsl:strip-space elements="*" />
<xsl:template match="/*/child::*">
    <xsl:for-each select="child::*">
        <xsl:if test="position() != last()">
            <xsl:value-of select="normalize-space(.)"/>, </xsl:if>
        <xsl:if test="position() = last()">
            <xsl:value-of select="normalize-space  (.)"/>
            <xsl:text>&#xD;</xsl:text>
        </xsl:if>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>`

results what i got from above is

expected results is

Please edit your question to start with a minimal but well-formed XML sample, so far the structure is not well-formed, opens with <Tx>, ends with </New>. — Martin Honnen
– Martin Honnen, Commented Jul 23, 2019 at 12:56
Why does the expected result have the value "Male" for both "Buyer.AcctOwnr.Id.Gender" and "Seller.AcctOwnr.Id.Gender"? And in general, what would happen if any third or deeper level structures were repeated e.g. if you had "<New><Id>123455</Id><Id>654321</Id>...</New>`, what kind of output mapping from XML to CSV do you want in that case? — Martin Honnen
– Martin Honnen, Commented Jul 23, 2019 at 13:27
expectation is to pull all node and values in csv, even if it repeated it need to be in CSV, new.id = 123456 and new.id= 653224 in 2 diff columns — Sush
– Sush, Commented Jul 23, 2019 at 13:42
We need a much more precise specification of requirements. XML is capable of representing much more complex structures than CSV; some things that can appear in XML are going to be very hard to map to CSV, and we need to know how you want to handle these cases. We can't work out the general rules from one example. — Michael Kay
– Michael Kay, Commented Jul 23, 2019 at 14:20

michael.hor257k · Accepted Answer · 2019-07-23 15:53:32Z

1

If you want a generic transformation that returns a data cell for each text node and attribute in the given XML (all in the same row), try something like:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="UTF-8"/>
<xsl:strip-space elements="*"/>

<xsl:template match="/">
    <xsl:variable name="fruit" select="//*[text()] | //@*" />
    <!-- LABELS -->
    <xsl:for-each select="$fruit">
        <xsl:for-each select="ancestor::*">
            <xsl:value-of select="name()" />
            <xsl:text>.</xsl:text>
        </xsl:for-each>
        <xsl:value-of select="name()" />
        <xsl:if test="position()!=last()">
            <xsl:text>,</xsl:text>
        </xsl:if>
    </xsl:for-each>
    <xsl:text>&#10;</xsl:text>
    <!-- DATA -->
    <xsl:for-each select="$fruit">
        <xsl:value-of select="." />
        <xsl:if test="position()!=last()">
            <xsl:text>,</xsl:text>
        </xsl:if>
    </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

The result of transforming your example XML will be:

Tx.New.Id,Tx.New.Submitted,Tx.New.Buyer.AcctOwnr.Id.Gender,Tx.New.Buyer.AcctOwnr.City,Tx.New.Seller.AcctOwnr.Id.Gender,Tx.New.Seller.AcctOwnr.City,Tx.New.Order.TrnsmssnInd,Tx.New.Tx.Date,Tx.New.Tx.cty,Tx.New.Tx.Qty.Value,Tx.New.Tx.Qty.Value.Ccy,Tx.New.Tx.Price.Price.Value.Amt,Tx.New.Tx.Price.Price.Value.Amt.Ccy,Tx.New.Tx.TradVn,Tx.New.Tx.CtryOfBrnch,Tx.New.AddtlAttrbts.TxInd
123456,true,Male,GB,Female,GB,false,2019-05-08,DEAL,5000,USD,95.1,USD,XOFF,GB,false

Note that this assumes none of the retrieved data contains a comma (as well as a few other assumptions - as I mentioned in the comment to your question, truly generic transformations are difficult to write).

edited Jul 23, 2019 at 15:53

answered Jul 23, 2019 at 15:40

michael.hor257k

118k6 gold badges36 silver badges55 bronze badges

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

5 Comments

Sush Over a year ago

thq Michael, but it give me only first element Tx.New.Id 123456

michael.hor257k Over a year ago

No, the result is what I posted - see: xsltfiddle.liberty-development.net/bnnZX5

Sush Over a year ago

File stylesheet = new File("C:\\style3.xsl");  File xmlSource = new File("C:\\sample.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); Result outputTarget = new StreamResult(System.out); transformer.transform(source, outputTarget);

Sush Over a year ago

i use above to pull data

michael.hor257k Over a year ago

I am afraid I cannot help you with that. If you're getting a different result using the same XML input and the same XSLT stylesheet, post a new question

Sush · Accepted Answer · 2019-07-23 21:01:35Z

0

i made some changes to above code and i am able to pull all data

xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="UTF-8"/>
<xsl:strip-space elements="*"/>
<xsl:template match="/">
    <xsl:variable name="fruit" select="//*[text()] | //@*" />
    <!-- LABELS -->
    <xsl:for-each select="$fruit">
        <xsl:for-each select="ancestor::*">
            <xsl:value-of select="name()" />
            <xsl:text>.</xsl:text>
        </xsl:for-each>
        <xsl:value-of select="name()" />
        <xsl:text>,</xsl:text>
        <xsl:for-each select="child::*">
        <xsl:if test="position()!=last()">
            <xsl:text>,</xsl:text>
        </xsl:if>
        </xsl:for-each>
    </xsl:for-each>
    <xsl:text>&#10;</xsl:text>
    <!-- DATA -->
    <xsl:for-each select="$fruit">
        <xsl:value-of select="." />
        <xsl:text>,</xsl:text>
        <xsl:for-each select="child::*">
        <xsl:if test="position()!=last()">
 <xsl:text>,</xsl:text>
</xsl:if>
</xsl:for-each>
    </xsl:for-each>
</xsl:template>
</xsl:stylesheet>```

answered Jul 23, 2019 at 21:01

Sush

711 gold badge2 silver badges11 bronze badges

1 Comment

michael.hor257k Over a year ago

This makes no sense. The "fruit" nodes do not have any child elements (at least not in your example XML) so adding the <xsl:for-each select="child::*"> part does not do anything. All you have achieved is a trailing comma after the last label and the last value.

Collectives™ on Stack Overflow

Generic XSLT transformation of XML to CSV with dynamic multiple child node

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related