Search code examples
xmlcsvxsltxslt-2.0

CSV2XML: How to insert additional nodes/elements

I have the following code that I found on another question. It works great at reading the source CSV file and parsing it. I need to insert additional nodes/elements into the parsed elements. I need the ZipDateCount and ZipCount element names inserted in the specified location defined in the Desired Output example.

The code I found works great. I can't figure out how to generate the desired result.

I've tried using a template and using another position() call. All of my attempts have failed miserably.

I can't figure out how to isolate the fields that need to be enclosed within the additional elements.

Thanks in advance for any assistance provided.

Source data

Name,Description,Active,start-date,end-date,DeliverMethod,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,DeliverMethod,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,DeliverMethod,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,DeliverMethod,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday
zip11111,Zip Code 11111,FALSE,3012019,3312019,S,100,100,100,100,100,100,100,NS,50,50,50,50,50,50,50,DM,35,35,35,35,35,35,35,D,20,20,20,20,20,20,20
zip22222,Zip Code 11111,FALSE,3012019,3312019,S,100,100,100,100,100,100,100,NS,50,50,50,50,50,50,50,DM,35,35,35,35,35,35,35,D,20,20,20,20,20,20,20
zip33333,Zip Code 11111,FALSE,3012019,3312019,S,100,100,100,100,100,100,100,NS,50,50,50,50,50,50,50,DM,35,35,35,35,35,35,35,D,20,20,20,20,20,20,20
zip44444,Zip Code 11111,FALSE,3012019,3312019,S,100,100,100,100,100,100,100,NS,50,50,50,50,50,50,50,DM,35,35,35,35,35,35,35,D,20,20,20,20,20,20,20
zip55555,Zip Code 11111,FALSE,3012019,3312019,S,100,100,100,100,100,100,100,NS,50,50,50,50,50,50,50,DM,35,35,35,35,35,35,35,D,20,20,20,20,20,20,20

Source xslt

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" exclude-result-prefixes="xs">
  <xsl:output indent="yes"/>
  <xsl:strip-space elements="*"/>

  <xsl:param name="csv-data" as="xs:string">Name,Description,Active,start-date,end-date,DeliverMethod,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,DeliverMethod,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,DeliverMethod,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,DeliverMethod,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday
zip11111,Zip Code 11111,FALSE,3012019,3312019,S,100,100,100,100,100,100,100,NS,50,50,50,50,50,50,50,DM,35,35,35,35,35,35,35,D,20,20,20,20,20,20,20
zip22222,Zip Code 11111,FALSE,3012019,3312019,S,100,100,100,100,100,100,100,NS,50,50,50,50,50,50,50,DM,35,35,35,35,35,35,35,D,20,20,20,20,20,20,20
zip33333,Zip Code 11111,FALSE,3012019,3312019,S,100,100,100,100,100,100,100,NS,50,50,50,50,50,50,50,DM,35,35,35,35,35,35,35,D,20,20,20,20,20,20,20
zip44444,Zip Code 11111,FALSE,3012019,3312019,S,100,100,100,100,100,100,100,NS,50,50,50,50,50,50,50,DM,35,35,35,35,35,35,35,D,20,20,20,20,20,20,20
zip55555,Zip Code 11111,FALSE,3012019,3312019,S,100,100,100,100,100,100,100,NS,50,50,50,50,50,50,50,DM,35,35,35,35,35,35,35,D,20,20,20,20,20,20,20
</xsl:param>

  <xsl:template match="/" name="csv2xml">
    <Root>
        <!--Get Header-->
        <xsl:variable name="header-tokens" as="xs:string*">
          <xsl:analyze-string select="$csv-data" regex="\r\n?|\n">
            <xsl:non-matching-substring>
              <xsl:if test="position()=1">
                <xsl:copy-of select="tokenize(.,',')"/>                                        
              </xsl:if>
            </xsl:non-matching-substring>
          </xsl:analyze-string>
        </xsl:variable>                    
        <xsl:analyze-string select="$csv-data" regex="\r\n?|\n">
          <xsl:non-matching-substring>
            <xsl:if test="not(position()=1)">
              <ZipCode>
                <xsl:for-each select="tokenize(.,',')">
                  <xsl:variable name="pos" select="position()"/>
                  <xsl:element name="{$header-tokens[$pos]}">
                    <xsl:value-of select="."/>
                  </xsl:element>
                </xsl:for-each>
              </ZipCode>
            </xsl:if>
          </xsl:non-matching-substring>
        </xsl:analyze-string>
    </Root>
  </xsl:template>
</xsl:stylesheet>

Output:

<?xml version="1.0" encoding="UTF-8"?>
<Root>
   <ZipCode>
      <Name>zip11111</Name>
      <Description>Zip Code 11111</Description>
      <Active>FALSE</Active>
      <start-date>3012019</start-date>
      <end-date>3312019</end-date>
      <DeliverMethod>S</DeliverMethod>
      <Monday>100</Monday>
      <Tuesday>100</Tuesday>
      <Wednesday>100</Wednesday>
      <Thursday>100</Thursday>
      <Friday>100</Friday>
      <Saturday>100</Saturday>
      <Sunday>100</Sunday>
      <DeliverMethod>NS</DeliverMethod>
      <Monday>50</Monday>
      <Tuesday>50</Tuesday>
      <Wednesday>50</Wednesday>
      <Thursday>50</Thursday>
      <Friday>50</Friday>
      <Saturday>50</Saturday>
      <Sunday>50</Sunday>
      <DeliverMethod>DM</DeliverMethod>
      <Monday>35</Monday>
      <Tuesday>35</Tuesday>
      <Wednesday>35</Wednesday>
      <Thursday>35</Thursday>
      <Friday>35</Friday>
      <Saturday>35</Saturday>
      <Sunday>35</Sunday>
      <DeliverMethod>D</DeliverMethod>
      <Monday>20</Monday>
      <Tuesday>20</Tuesday>
      <Wednesday>20</Wednesday>
      <Thursday>20</Thursday>
      <Friday>20</Friday>
      <Saturday>20</Saturday>
      <Sunday>20</Sunday>
   </ZipCode>
   <ZipCode>
      <Name>zip22222</Name>
      <Description>Zip Code 11111</Description>
      <Active>FALSE</Active>
      <start-date>3012019</start-date>
      <end-date>3312019</end-date>
      <DeliverMethod>S</DeliverMethod>
      <Monday>100</Monday>
      <Tuesday>100</Tuesday>
      <Wednesday>100</Wednesday>
      <Thursday>100</Thursday>
      <Friday>100</Friday>
      <Saturday>100</Saturday>
      <Sunday>100</Sunday>
      <DeliverMethod>NS</DeliverMethod>
      <Monday>50</Monday>
      <Tuesday>50</Tuesday>
      <Wednesday>50</Wednesday>
      <Thursday>50</Thursday>
      <Friday>50</Friday>
      <Saturday>50</Saturday>
      <Sunday>50</Sunday>
      <DeliverMethod>DM</DeliverMethod>
      <Monday>35</Monday>
      <Tuesday>35</Tuesday>
      <Wednesday>35</Wednesday>
      <Thursday>35</Thursday>
      <Friday>35</Friday>
      <Saturday>35</Saturday>
      <Sunday>35</Sunday>
      <DeliverMethod>D</DeliverMethod>
      <Monday>20</Monday>
      <Tuesday>20</Tuesday>
      <Wednesday>20</Wednesday>
      <Thursday>20</Thursday>
      <Friday>20</Friday>
      <Saturday>20</Saturday>
      <Sunday>20</Sunday>
   </ZipCode>
...
   </ZipCode>
</Root>

Desired Output:

<?xml version="1.0" encoding="UTF-8"?>
<Root>
   <ZipCode>
      <Name>zip11111</Name>
      <Description>Zip Code 11111</Description>
      <Active>FALSE</Active>
    <ZipDateCount>
      <start-date>3012019</start-date>
      <end-date>3312019</end-date>
    <ZipCounts>
      <DeliverMethod>S</DeliverMethod>
      <Monday>100</Monday>
      <Tuesday>100</Tuesday>
      <Wednesday>100</Wednesday>
      <Thursday>100</Thursday>
      <Friday>100</Friday>
      <Saturday>100</Saturday>
      <Sunday>100</Sunday>
    </ZipCounts>
    <ZipCounts>
      <DeliverMethod>NS</DeliverMethod>
      <Monday>50</Monday>
      <Tuesday>50</Tuesday>
      <Wednesday>50</Wednesday>
      <Thursday>50</Thursday>
      <Friday>50</Friday>
      <Saturday>50</Saturday>
      <Sunday>50</Sunday>
    </ZipCounts>
    <ZipCounts>
      <DeliverMethod>DM</DeliverMethod>
      <Monday>35</Monday>
      <Tuesday>35</Tuesday>
      <Wednesday>35</Wednesday>
      <Thursday>35</Thursday>
      <Friday>35</Friday>
      <Saturday>35</Saturday>
      <Sunday>35</Sunday>
    </ZipCounts>
    <ZipCounts>
      <DeliverMethod>D</DeliverMethod>
      <Monday>20</Monday>
      <Tuesday>20</Tuesday>
      <Wednesday>20</Wednesday>
      <Thursday>20</Thursday>
      <Friday>20</Friday>
      <Saturday>20</Saturday>
      <Sunday>20</Sunday>
    </ZipCounts>
    </ZipDateCount>
   </ZipCode>
   <ZipCode>
      <Name>zip22222</Name>
      <Description>Zip Code 11111</Description>
      <Active>FALSE</Active>
    <ZipDateCount>
      <start-date>3012019</start-date>
      <end-date>3312019</end-date>
    <ZipCounts>   
      <DeliverMethod>S</DeliverMethod>
      <Monday>100</Monday>
      <Tuesday>100</Tuesday>
      <Wednesday>100</Wednesday>
      <Thursday>100</Thursday>
      <Friday>100</Friday>
      <Saturday>100</Saturday>
      <Sunday>100</Sunday>
    </ZipCounts>
    <ZipCounts>   
      <DeliverMethod>NS</DeliverMethod>
      <Monday>50</Monday>
      <Tuesday>50</Tuesday>
      <Wednesday>50</Wednesday>
      <Thursday>50</Thursday>
      <Friday>50</Friday>
      <Saturday>50</Saturday>
      <Sunday>50</Sunday>
    </ZipCounts>
    <ZipCounts>   
      <DeliverMethod>DM</DeliverMethod>
      <Monday>35</Monday>
      <Tuesday>35</Tuesday>
      <Wednesday>35</Wednesday>
      <Thursday>35</Thursday>
      <Friday>35</Friday>
      <Saturday>35</Saturday>
      <Sunday>35</Sunday>
    </ZipCounts>
    <ZipCounts>   
      <DeliverMethod>D</DeliverMethod>
      <Monday>20</Monday>
      <Tuesday>20</Tuesday>
      <Wednesday>20</Wednesday>
      <Thursday>20</Thursday>
      <Friday>20</Friday>
      <Saturday>20</Saturday>
      <Sunday>20</Sunday>
    </ZipCounts>
    </ZipDateCount>
   </ZipCode>
</Root>
Solution

  • You can store the result of the CSV to XML transformation in a variable and then push it to some templates to perform any needed XML to XML transformation; for that particular task of wrapping any group of adjacent elements starting with DeliverMethod into a ZipCounts you can use xsl:for-each-group group-starting-with="DeliverMethod":

    <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" exclude-result-prefixes="xs">
  <xsl:output indent="yes"/>
  <xsl:strip-space elements="*"/>

  <xsl:param name="csv-data" as="xs:string">Name,Description,Active,start-date,end-date,DeliverMethod,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,DeliverMethod,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,DeliverMethod,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,DeliverMethod,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday
zip11111,Zip Code 11111,FALSE,3012019,3312019,S,100,100,100,100,100,100,100,NS,50,50,50,50,50,50,50,DM,35,35,35,35,35,35,35,D,20,20,20,20,20,20,20
zip22222,Zip Code 11111,FALSE,3012019,3312019,S,100,100,100,100,100,100,100,NS,50,50,50,50,50,50,50,DM,35,35,35,35,35,35,35,D,20,20,20,20,20,20,20
zip33333,Zip Code 11111,FALSE,3012019,3312019,S,100,100,100,100,100,100,100,NS,50,50,50,50,50,50,50,DM,35,35,35,35,35,35,35,D,20,20,20,20,20,20,20
zip44444,Zip Code 11111,FALSE,3012019,3312019,S,100,100,100,100,100,100,100,NS,50,50,50,50,50,50,50,DM,35,35,35,35,35,35,35,D,20,20,20,20,20,20,20
zip55555,Zip Code 11111,FALSE,3012019,3312019,S,100,100,100,100,100,100,100,NS,50,50,50,50,50,50,50,DM,35,35,35,35,35,35,35,D,20,20,20,20,20,20,20
</xsl:param>

  <xsl:template match="/" name="csv2xml">
    <Root>
        <!--Get Header-->
        <xsl:variable name="header-tokens" as="xs:string*">
          <xsl:analyze-string select="$csv-data" regex="\r\n?|\n">
            <xsl:non-matching-substring>
              <xsl:if test="position()=1">
                <xsl:copy-of select="tokenize(.,',')"/>                                        
              </xsl:if>
            </xsl:non-matching-substring>
          </xsl:analyze-string>
        </xsl:variable>  
        <xsl:variable name="csv-xml">
            <xsl:analyze-string select="$csv-data" regex="\r\n?|\n">
              <xsl:non-matching-substring>
                <xsl:if test="not(position()=1)">
                  <ZipCode>
                    <xsl:for-each select="tokenize(.,',')">
                      <xsl:variable name="pos" select="position()"/>
                      <xsl:element name="{$header-tokens[$pos]}">
                        <xsl:value-of select="."/>
                      </xsl:element>
                    </xsl:for-each>
                  </ZipCode>
                </xsl:if>
              </xsl:non-matching-substring>
            </xsl:analyze-string>            
        </xsl:variable>
        <xsl:apply-templates select="$csv-xml/*"/>
    </Root>
  </xsl:template>

  <xsl:template match="ZipCode">
      <xsl:copy>
          <xsl:variable name="header-elements" select="Name, Description, Active"/>
          <xsl:copy-of select="$header-elements"/>
          <ZipDateCount>
              <xsl:variable name="date-elements" select="start-date, end-date"/>
              <xsl:copy-of select="$date-elements"/>
              <xsl:for-each-group select="* except ($header-elements, $date-elements)" group-starting-with="DeliverMethod">
                  <ZipCounts>
                      <xsl:copy-of select="current-group()"/>
                  </ZipCounts>
              </xsl:for-each-group>
          </ZipDateCount>
      </xsl:copy>
  </xsl:template>

</xsl:stylesheet>

    https://xsltfiddle.liberty-development.net/94rmq6s