XML to CSV using XSLT help

Question

I'd like to convert XML into CSV using an XSLT, but when applying the XSL from the SO thread titled XML To CSV XSLT against my input:

<WhoisRecord>
  <DomainName>127.0.0.1</DomainName>
  <RegistryData>
    <AbuseContact>
      <Email>[email protected]</Email>
      <Name>Internet Corporation for Assigned Names and Number</Name>
      <Phone>+1-310-301-5820</Phone>
    </AbuseContact>
    <AdministrativeContact i:nil="true"/>
    <BillingContact i:nil="true"/>
    <CreatedDate/>
    <RawText>...</RawText>
    <Registrant>
      <Address>4676 Admiralty Way, Suite 330</Address>
      <City>Marina del Rey</City>
      <Country>US</Country>
      <Name>Internet Assigned Numbers Authority</Name>
      <PostalCode>90292-6695</PostalCode>
      <StateProv>CA</StateProv>
    </Registrant>
    <TechnicalContact>
      <Email>[email protected]</Email>
      <Name>Internet Corporation for Assigned Names and Number</Name>
      <Phone>+1-310-301-5820</Phone>
    </TechnicalContact>
    <UpdatedDate>2010-04-14</UpdatedDate>
    <ZoneContact i:nil="true"/>
  </RegistryData>
</WhoisRecord>

I end up with:

  [email protected] Corporation for Assigned Names and Number+1-310-301-5820,
    ,
    ,
    ,
    ...,      
    4676 Admiralty Way, Suite 330Marina del ReyUSInternet Assigned Numbers Authority90292-6695CA,      
    [email protected] Corporation for Assigned Names and Number+1-310-301-5820,      
    2010-04-14,

My problem is that, the resulting transformation is missing nodes (like the DomainName element containing the IP address) and some child nodes are concatenated without commas (like the children of AbuseContact).

I'd like to see all the XML output in CSV form, and strings like: "[email protected] Corporation for Assigned Names and Number+1-310-301-5820," delimited by commas.

My XSL is pretty rusty. Your help is appreciated. :)

Here's the XSL I'm using:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="iso-8859-1"/>

<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>
</xsl:text>
  </xsl:if>
  </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

Good Question (+1). See my answer for a simple solution. :)

Dimitre Novatchev
– Dimitre Novatchev

2010-05-17 18:50:20 +00:00
Commented May 17, 2010 at 18:50 — Dimitre Novatchev
– Dimitre Novatchev, Commented May 17, 2010 at 18:50

U. Windl · Accepted Answer · 2023-08-28 09:59:04Z

5

This simple transformation produces the wanted result:

<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output method="text"/>
 <xsl:strip-space elements="*"/>
 
    <xsl:template match="/">
    <xsl:apply-templates select="//text()"/>
    </xsl:template>
    
    <xsl:template match="text()">
      <xsl:copy-of select="."/>
      <xsl:if test="not(position()=last())">,</xsl:if>
    </xsl:template>
</xsl:stylesheet>

Do note the use of:

 <xsl:strip-space elements="*"/>

to discard any white-space-only text nodes.

Update: AJ raised the problem that the results should be grouped in records/tuples per line. It isn't defined in the question what a record/tuple should exactly be. Therefore the current solution solves the two problems of white-space-only text nodes and of missing commas, but does not aim to grop the output into records/tuples.

edited Aug 28, 2023 at 9:59

U. Windl

4,73839 silver badges65 bronze badges

answered May 17, 2010 at 18:49

Dimitre Novatchev

244k27 gold badges307 silver badges438 bronze badges

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

8 Comments

user201788 Over a year ago

Doesn't CSV require a new line to separate a set/tuple of records?

Dimitre Novatchev Over a year ago

It is not clear from the question what constitutes a tuple of records -- this has meaning in the relational DB world, but for a tree needs to be explicitly defined. I also edited my answer to reflect your comment.

Adam Kahtava Over a year ago

Thanks guys! I would like a set/tuple of records. How hard would that be? I'd also like to be able to apply the XSL to similarly structured XML documents - solutions that don't reference elements by name are preferred. Thanks again. :)

Dimitre Novatchev Over a year ago

@Adam-Kahtava: It shouldn't be difficult to implement tuples/sets once you define what should a tuple consist of.

Tim C Over a year ago

Some fields (well, the address element) have commas in, so you probaly need to check for this, and enclose the field in quotation marks. And if the field contains quotation marks, I believe this have to become double-quotation marks.

|

user201788 · Accepted Answer · 2010-05-17 18:42:02Z

0

I believe that you need recursive solution to approach this problem. So, you'd require something that keeps diving into the tree till it reaches a text() node. If that text() node is the actually a child of the last node, then it puts a new line. Otherwise, it just puts the value with a comma.

If the node does not has a text() node as its child, then recursively start to dig into that tree.

<xsl:strip-space elements="*" />    

<xsl:template name="rec">        
    <xsl:param name="node"/>        
    <xsl:for-each select="child::*">
        <xsl:choose>
            <xsl:when test="child::text()">
                <xsl:choose>                        
                    <xsl:when test="local-name(.) != 'UpdatedDate'">"<xsl:value-of select="normalize-space(.)"/>", </xsl:when>
                    <xsl:otherwise>"<xsl:value-of select="normalize-space(.)"/>" <xsl:text>&#xD;</xsl:text></xsl:otherwise>
                </xsl:choose>                    
            </xsl:when>
            <xsl:when test="child::node()">
                <xsl:call-template name="rec">
                    <xsl:with-param name="node" select="child::node()"/>
                </xsl:call-template>                    
            </xsl:when>
        </xsl:choose>

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

This is not fool proof, but it produced this result on my end with Saxon:

"127.0.0.1", "[email protected]", "Internet Corporation for Assigned Names and Number", "+1-310-301-5820", "...", "4676 Admiralty Way, Suite 330", "Marina del Rey", "US", "Internet Assigned Numbers Authority", "90292-6695", "CA", "[email protected]", "Internet Corporation for Assigned Names and Number", "+1-310-301-5820", "2010-04-14"

Hope this helps.

answered May 17, 2010 at 18:42

user201788

2 Comments

user201788 Over a year ago

Why was my answer voted down? A comment about it would have been helpful. I am new to XSLT myself.

Tomalak Over a year ago

Probably because there is no explicit recursion or looping needed to move along the the child axis.

Collectives™ on Stack Overflow

XML to CSV using XSLT help

2 Answers 2

8 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related