Link to home
Start Free TrialLog in
Avatar of Mani Pazhana
Mani PazhanaFlag for United States of America

asked on

XSLT: Remove double quotes from text fields

I need to remove double quotes from any text fields (but leave them around the fields when we output to the comma-delimited file.

Here is my XSLT:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:csv="csv:csv"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
      
      <xsl:output method="text" encoding="iso-8859-1"/>

      <xsl:key name="kInvoiceDetailId" match="/Invoice/InvoiceLineItemDetail" use="concat(../VendorInvoiceNumber,'-',OriginatingInvoiceLineId)"/>

      
      <!-- DL being used (Comma in this case) -->
      <xsl:param name="DL" select="'&#44;'"/>
      <!-- Checking for carriage return -->
      <xsl:param name="CR" select="'&#xA;'"/>

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

      <!-- Column Headers -->
      <!--Create header row of element names
            (All fields are sent to JDE that are in the CIM, single line per invoice details)-->


      <xsl:variable name="headers" >
            <headers>ClientId</headers> <!-- 1 -->
            <headers>OriginatingInvoiceId</headers> <!-- 2 -->
            <headers>VendorId</headers> <!-- 3 -->
            <headers>AlternativeVendorId</headers> <!-- 4 -->
            <headers>VendorInvoiceNumber</headers> <!-- 5 -->
            <headers>InvoiceDate</headers> <!-- 6 -->
            <headers>InvoiceReceiveDate</headers> <!-- 7 -->
            <headers>GLDate</headers> <!-- 8 -->
            <headers>Remark</headers> <!-- 9 -->
            <headers>TotalInvoiceAmount</headers> <!-- 10 -->
            <headers>CurrencyCode</headers> <!-- 11 -->
            <headers>TaxRateAreaCode</headers> <!-- 12 -->
            <headers>StatusCodeCode</headers> <!-- 13 -->
            <headers>StatusCodeDescription</headers> <!-- 14 -->
            <headers>ClientApprovalNumber</headers> <!-- 15 -->
            <headers>AuthorizedBy</headers> <!-- 16 -->
            <headers>BatchReferenceNumber</headers> <!-- 17 -->
            <headers>InvoiceSource</headers> <!-- 18 -->
            <headers>NumberOfLines</headers> <!-- 19 -->
            <headers>OriginatingInvoiceLineId</headers> <!-- 20 -->
            <headers>POLineNumber</headers> <!-- 21 -->
            <headers>LineItemDescription</headers> <!-- 22 -->
            <headers>LocationId</headers> <!-- 23 -->
            <headers>BuildingName</headers> <!-- 24 -->
            <headers>LineItemAmount</headers> <!-- 25 -->
            <headers>TaxableLineItemAmount</headers> <!-- 26 -->
            <headers>TaxAmountLineItem</headers> <!-- 27 -->
            <headers>CompanyId</headers> <!-- 28 -->
            <headers>GLAccountCode</headers> <!-- 29 -->
            <headers>Subsidiary</headers> <!-- 30 -->
            <headers>SubLedger</headers> <!-- 31 -->
            <headers>SubLedgerType</headers> <!-- 32 -->
            <headers>LedgerType</headers> <!-- 33 -->
            <headers>CostCenter</headers> <!-- 34 -->
            <headers>AccountBlock</headers> <!-- 35 -->
            <headers>GLAccountDescription</headers> <!-- 36 -->
            <headers>ClientAccountCode</headers> <!-- 37 -->
            <headers>WBSCode</headers> <!-- 38 -->
            <headers>OriginatingProjectNumber</headers> <!-- 39 -->
            <headers>OriginatingProjectSystem</headers> <!-- 40 -->
            <headers>ClientPONumber</headers> <!-- 41 -->
            <headers>WorkOrderNumber</headers> <!-- 42 -->
            <headers>WorkOrderCompletedDate</headers> <!-- 43 -->
            <headers>WorkOrderCreatedDate</headers> <!-- 44 -->
            <headers>CodingBlock1</headers> <!-- 45 -->
            <headers>CodingBlock2</headers> <!-- 46 -->
            <headers>CodingBlock3</headers> <!-- 47 -->
            <headers>CodingBlock4</headers> <!-- 48 -->
            <headers>CodingBlock5</headers> <!-- 49 -->
            <headers>CodingBlock6</headers> <!-- 50 -->
            <headers>CodingDate1</headers> <!-- 51 -->
            <headers>CodingDate2</headers> <!-- 52 -->
      </xsl:variable>      

      <xsl:variable name="columns" >
            <columns>ClientId</columns> <!-- 1 -->
            <columns>OriginatingInvoiceId</columns> <!-- 2 -->
            <columns>VendorId</columns> <!-- 3 -->
            <columns>AlternativeVendorId</columns> <!-- 4 -->
            <columns>VendorInvoiceNumber</columns> <!-- 5 -->
            <columns>InvoiceDate</columns> <!-- 6 -->
            <columns>InvoiceReceiveDate</columns> <!-- 7 -->
            <columns>GLDate</columns> <!-- 8 -->
            <columns>Remark</columns> <!-- 9 -->
            <columns>TotalInvoiceAmount</columns> <!-- 10 -->
            <columns>CurrencyCode</columns> <!-- 11 -->
            <columns>TaxRateAreaCode</columns> <!-- 12-->
            <columns>common:Code</columns> <!-- 13 -->
            <columns>common:Description</columns> <!-- 14 -->
            <columns>ClientApprovalNumber</columns> <!-- 15 -->
            <columns>AuthorizedBy</columns> <!-- 16 -->
            <columns>BatchReferenceNumber</columns> <!-- 17 -->
            <columns>InvoiceSource</columns> <!-- 18 -->
            <columns>NumberOfLines</columns> <!-- 19 -->
            <columns>OriginatingInvoiceLineId</columns> <!-- 20 -->
            <columns>POLineNumber</columns> <!-- 21 -->
            <columns>LineItemDescription</columns> <!-- 22 -->
            <columns>LocationId</columns> <!-- 23 -->
            <columns>BuildingName</columns> <!-- 24 -->
            <columns>LineItemAmount</columns> <!-- 25 -->
            <columns>TaxableLineItemAmount</columns> <!-- 26 -->
            <columns>TaxAmountLineItem</columns> <!-- 27 -->
            <columns>CompanyId</columns> <!-- 28 -->
            <columns>GLAccountCode</columns> <!-- 29 -->
            <columns>Subsidiary</columns> <!-- 30 -->
            <columns>SubLedger</columns> <!-- 31 -->
            <columns>SubLedgerType</columns> <!-- 32 -->
            <columns>LedgerType</columns> <!-- 33 -->
            <columns>CostCenter</columns> <!-- 34 -->
            <columns>AccountBlock</columns> <!-- 35 -->
            <columns>GLAccountDescription</columns> <!-- 36 -->
            <columns>ClientAccountCode</columns> <!-- 37 -->
            <columns>WBSCode</columns> <!-- 38 -->
            <columns>OriginatingProjectNumber</columns> <!-- 39 -->
            <columns>OriginatingProjectSystem</columns> <!-- 40 -->
            <columns>ClientPONumber</columns> <!-- 41 -->
            <columns>WorkOrderNumber</columns> <!-- 42 -->
            <columns>WorkOrderCompletedDate</columns> <!-- 43 -->
            <columns>WorkOrderCreatedDate</columns> <!-- 44 -->
            <columns>CodingBlock1</columns> <!-- 45 -->
            <columns>CodingBlock2</columns> <!-- 46 -->
            <columns>CodingBlock3</columns> <!-- 47 -->
            <columns>CodingBlock4</columns> <!-- 48 -->
            <columns>CodingBlock5</columns> <!-- 49 -->
            <columns>CodingBlock6</columns> <!-- 50 -->
            <columns>CodingDate1</columns> <!-- 51 -->
            <columns>CodingDate2</columns> <!-- 52 -->
      </xsl:variable>      

      <!--Invoice hierarchy structure of Invoice/InvoiceLineItemDetail is flattened into a single line per InvoiceLineItemDetail-->
      <xsl:template match="/">
      
            <!--Create header row of attribute names-->
            <xsl:for-each select="msxsl:node-set($headers)/headers">
                  <xsl:text>"</xsl:text>
                  <xsl:value-of select="."/>
                  <xsl:text>"</xsl:text>
                  <xsl:if test="position() != last()">
                        <xsl:value-of select="$DL"/>
                  </xsl:if>
            </xsl:for-each>
            <xsl:value-of select="$CR"/>

            <xsl:for-each select="/*/*/*/*">
                  <xsl:variable name="NodeName" select="name(current())"/>
                  
                  <!-- If node is InvoiceLineItemDetail, get the values based on their element name (from the columns above -->
                  <xsl:if test="$NodeName = 'InvoiceLineItem'">
                        <xsl:variable name="property" select="ancestor-or-self::node()|descendant::node()"/>
                        <xsl:for-each select="msxsl:node-set($columns)/columns">
                              <!-- Extract the column name and value -->                  
                              <xsl:variable name="column" select="."/>      
                              <xsl:variable name="value" select="$property/*[name() = $column]"/>            
                              <xsl:text>"</xsl:text>
                              <xsl:choose>

            <xsl:when test="$column = 'WorkOrderCreatedDate' or $column = 'CodingDate1'">
              <xsl:text>null</xsl:text>
            </xsl:when>
           
                                    <xsl:when test="contains($column,'Date')">
                                          <xsl:variable name="year" select="msxsl:format-date($value, 'yyyy')"/>
                                          <xsl:variable name="month" select="msxsl:format-date($value, 'M')"/>
                                          <xsl:variable name="day" select="msxsl:format-date($value, 'd')"/>
                                          <xsl:variable name="isLeap" select="(($year mod 4)=0 and ($year mod 100)!=0) or ($year mod 400)=0"/>
                                          <xsl:choose>
                                                <xsl:when test="$isLeap = true()">
                                                      <xsl:choose>
                                                            <xsl:when test="$month = 1"><xsl:value-of select="($year*1000) + (0 + $day) - 1900000"/></xsl:when>
                                                            <xsl:when test="$month = 2"><xsl:value-of select="($year*1000) + (31 + $day) - 1900000"/></xsl:when> <!-- Jan = 31 -->
                                                            <xsl:when test="$month = 3"><xsl:value-of select="($year*1000) + (60 + $day) - 1900000"/></xsl:when> <!-- Feb = 29 -->
                                                            <xsl:when test="$month = 4"><xsl:value-of select="($year*1000) + (91 + $day) - 1900000"/></xsl:when> <!-- Mar = 31 -->
                                                            <xsl:when test="$month = 5"><xsl:value-of select="($year*1000) + (121 + $day) - 1900000"/></xsl:when> <!-- Apr = 30 -->
                                                            <xsl:when test="$month = 6"><xsl:value-of select="($year*1000) + (152 + $day) - 1900000"/></xsl:when> <!-- May = 31 -->
                                                            <xsl:when test="$month = 7"><xsl:value-of select="($year*1000) + (182 + $day) - 1900000"/></xsl:when> <!-- Jun = 30 -->
                                                            <xsl:when test="$month = 8"><xsl:value-of select="($year*1000) + (213 + $day) - 1900000"/></xsl:when> <!-- Jul = 31 -->
                                                            <xsl:when test="$month = 9"><xsl:value-of select="($year*1000) + (244 + $day) - 1900000"/></xsl:when> <!-- Aug = 31 -->
                                                            <xsl:when test="$month = 10"><xsl:value-of select="($year*1000) + (274 + $day) - 1900000"/></xsl:when> <!-- Sep = 30 -->
                                                            <xsl:when test="$month = 11"><xsl:value-of select="($year*1000) + (305 + $day) - 1900000"/></xsl:when> <!-- Oct = 31 -->
                                                            <xsl:when test="$month = 12"><xsl:value-of select="($year*1000) + (335 + $day) - 1900000"/></xsl:when> <!-- Nov = 30 -->
                                                      </xsl:choose>
                                                </xsl:when>
                                                <xsl:when test="$isLeap = false()">      
                                                      <xsl:choose>
                                                            <xsl:when test="$month = 1"><xsl:value-of select="($year*1000) + (0 + $day) - 1900000"/></xsl:when>
                                                            <xsl:when test="$month = 2"><xsl:value-of select="($year*1000) + (31 + $day) - 1900000"/></xsl:when> <!-- Jan = 31 -->
                                                            <xsl:when test="$month = 3"><xsl:value-of select="($year*1000) + (59 + $day) - 1900000"/></xsl:when> <!-- Feb = 28 -->
                                                            <xsl:when test="$month = 4"><xsl:value-of select="($year*1000) + (90 + $day) - 1900000"/></xsl:when> <!-- Mar = 31 -->
                                                            <xsl:when test="$month = 5"><xsl:value-of select="($year*1000) + (120 + $day) - 1900000"/></xsl:when> <!-- Apr = 30 -->
                                                            <xsl:when test="$month = 6"><xsl:value-of select="($year*1000) + (151 + $day) - 1900000"/></xsl:when> <!-- May = 31 -->
                                                            <xsl:when test="$month = 7"><xsl:value-of select="($year*1000) + (181 + $day) - 1900000"/></xsl:when> <!-- Jun = 30 -->
                                                            <xsl:when test="$month = 8"><xsl:value-of select="($year*1000) + (212 + $day) - 1900000"/></xsl:when> <!-- Jul = 31 -->
                                                            <xsl:when test="$month = 9"><xsl:value-of select="($year*1000) + (243 + $day) - 1900000"/></xsl:when> <!-- Aug = 31 -->
                                                            <xsl:when test="$month = 10"><xsl:value-of select="($year*1000) + (273 + $day) - 1900000"/></xsl:when> <!-- Sep = 30 -->
                                                            <xsl:when test="$month = 11"><xsl:value-of select="($year*1000) + (304 + $day) - 1900000"/></xsl:when> <!-- Oct = 31 -->
                                                            <xsl:when test="$month = 12"><xsl:value-of select="($year*1000) + (334 + $day) - 1900000"/></xsl:when> <!-- Nov = 30 -->
                                                      </xsl:choose>
                                                </xsl:when>
                                          </xsl:choose>        

            </xsl:when>      
                                    <xsl:otherwise>
                                          <xsl:value-of select="$value" disable-output-escaping="yes"/>
                                    </xsl:otherwise>
                              </xsl:choose>
                              <xsl:text>"</xsl:text>          
                              <xsl:if test="position() != last()">
                                    <xsl:value-of select="$DL"/>
                              </xsl:if>
                        </xsl:for-each>        
                        <xsl:value-of select="$CR"/>
                  </xsl:if>
            </xsl:for-each>

      </xsl:template>

</xsl:stylesheet>

----------------------------------------------

Here is my XML ( i need to clean all double quotes from text fields)

<?xml version="1.0" encoding="utf-8"?>
<root xmlns="http://integration.cbre.com/schemas/gcs/batchinvoice/v2">
  <Invoice xmlns:common="http://integration.cbre.com/schemas/gcs/common/v1">
    <ClientId>ATT</ClientId>
    <OriginatingInvoiceId>1752</OriginatingInvoiceId>
    <VendorId>236998</VendorId>
    <AlternativeVendorId>PRV-09-1301</AlternativeVendorId>
    <VendorInvoiceNumber>28</VendorInvoiceNumber>
    <InvoiceDate>2013-08-12T00:00:00</InvoiceDate>
    <InvoiceReceiveDate>2013-08-20T00:00:00</InvoiceReceiveDate>
    <GLDate>2013-08-21T20:53:03.779-05:00</GLDate>
    <Remark></Remark>
    <TotalInvoiceAmount>378.59</TotalInvoiceAmount>
    <CurrencyCode>USD</CurrencyCode>
    <StatusCode>
      <common:Code>Authorized</common:Code>
      <common:Description>Authorized</common:Description>
    </StatusCode>
    <ClientApprovalNumber>3000517524</ClientApprovalNumber>
    <InvoiceSource>COR</InvoiceSource>
    <NumberOfLines>2</NumberOfLines>
    <InvoiceLineItems>
      <InvoiceLineItem>
        <OriginatingInvoiceLineID>6790</OriginatingInvoiceLineID>
        <POLineNumber>5</POLineNumber>
        <LineItemDescription>1 man "Electrician" 2013-08-12T00:00:00</LineItemDescription>
        <LocationId>ATR000596</LocationId>
        <BuildingName>Canyon Rd Store</BuildingName>
        <LineItemAmount>316.000000</LineItemAmount>
        <GLAccount>
          <CompanyId>51310</CompanyId>
          <GLAccountCode>51310</GLAccountCode>
          <AccountBlock>51310</AccountBlock>
          <GLAccountDescription>51310</GLAccountDescription>
        </GLAccount>
        <AdditionalCoding>
          <ClientPONumber />
          <WorkOrderNumber>0596000002</WorkOrderNumber>
          <WorkOrderCompletedDate>2013-08-12T16:46:21</WorkOrderCompletedDate>
          <WorkOrderCreatedDate>2013-08-01T06:06:00</WorkOrderCreatedDate>
          <CodingBlock1>Labor</CodingBlock1>
          <CodingBlock2>Electrical Wiring</CodingBlock2>
          <CodingBlock4>1 "man" Electrician</CodingBlock4>
          <CodingBlock5>Electrical</CodingBlock5>
        </AdditionalCoding>
      </InvoiceLineItem>
      <InvoiceLineItem>
        <OriginatingInvoiceLineID>6791</OriginatingInvoiceLineID>
        <POLineNumber>6</POLineNumber>
        <LineItemDescription>wiring, euipment, trip charge</LineItemDescription>
        <LocationId>ATR000596</LocationId>
        <BuildingName>Canyon Rd Store</BuildingName>
        <LineItemAmount>62.590000</LineItemAmount>
        <GLAccount>
          <CompanyId>51310</CompanyId>
          <GLAccountCode>51310</GLAccountCode>
          <AccountBlock>51310</AccountBlock>
          <GLAccountDescription>51310</GLAccountDescription>
        </GLAccount>
        <AdditionalCoding>
          <ClientPONumber />
          <WorkOrderNumber>0596000002</WorkOrderNumber>
          <WorkOrderCompletedDate>2013-08-12T16:46:21</WorkOrderCompletedDate>
          <WorkOrderCreatedDate>2013-08-01T06:06:00</WorkOrderCreatedDate>
          <CodingBlock1>Miscellaneous</CodingBlock1>
          <CodingBlock2>Electrical Wiring</CodingBlock2>
          <CodingBlock4>wiring, euipment, trip charge</CodingBlock4>
          <CodingBlock5>Electrical</CodingBlock5>
        </AdditionalCoding>
      </InvoiceLineItem>
    </InvoiceLineItems>
  </Invoice>
  <Invoice xmlns:common="http://integration.cbre.com/schemas/gcs/common/v1">
    <ClientId>ATT</ClientId>
    <OriginatingInvoiceId>5188</OriginatingInvoiceId>
    <VendorId>242349</VendorId>
    <AlternativeVendorId>PRV-03-207</AlternativeVendorId>
    <VendorInvoiceNumber>0313000005</VendorInvoiceNumber>
    <InvoiceDate>2013-08-22T00:00:00</InvoiceDate>
    <InvoiceReceiveDate>2013-08-22T00:00:00</InvoiceReceiveDate>
    <GLDate>2013-09-09T18:50:42.734-07:00</GLDate>
    <Remark>Glass in door was replaced and the stickers stating the store hours and "use other door" and arrow need to be replaced on the glass.</Remark>
    <TotalInvoiceAmount>359.9</TotalInvoiceAmount>
    <CurrencyCode>USD</CurrencyCode>
    <StatusCode>
      <common:Code>Authorized</common:Code>
      <common:Description>Authorized</common:Description>
    </StatusCode>
    <ClientApprovalNumber>6000151886</ClientApprovalNumber>
    <InvoiceSource>COR</InvoiceSource>
    <NumberOfLines>3</NumberOfLines>
    <InvoiceLineItems>
      <InvoiceLineItem>
        <OriginatingInvoiceLineID>7998</OriginatingInvoiceLineID>
        <POLineNumber>48</POLineNumber>
        <LineItemDescription>Change hours and "use other door" sign 2013-08-22T00:00:00</LineItemDescription>
        <LocationId>ATR000313</LocationId>
        <BuildingName>Conyers Store</BuildingName>
        <LineItemAmount>230.000000</LineItemAmount>
        <GLAccount>
          <CompanyId>51835</CompanyId>
          <GLAccountCode>51835</GLAccountCode>
          <AccountBlock>51835</AccountBlock>
          <GLAccountDescription>51835</GLAccountDescription>
        </GLAccount>
        <AdditionalCoding>
          <ClientPONumber />
          <WorkOrderNumber>0313000005</WorkOrderNumber>
          <WorkOrderCompletedDate>2013-08-22T09:58:58</WorkOrderCompletedDate>
          <WorkOrderCreatedDate>2013-08-12T09:46:00</WorkOrderCreatedDate>
          <CodingBlock1>Labor</CodingBlock1>
          <CodingBlock2>Store Hours Signage</CodingBlock2>
          <CodingBlock4>Change hours and "use other door" sign</CodingBlock4>
          <CodingBlock5>Signage</CodingBlock5>
        </AdditionalCoding>
      </InvoiceLineItem>
      <InvoiceLineItem>
        <OriginatingInvoiceLineID>7999</OriginatingInvoiceLineID>
        <POLineNumber>49</POLineNumber>
        <LineItemDescription>vinyl hours and custom sign</LineItemDescription>
        <LocationId>ATR000313</LocationId>
        <BuildingName>Conyers Store</BuildingName>
        <LineItemAmount>120.000000</LineItemAmount>
        <GLAccount>
          <CompanyId>51835</CompanyId>
          <GLAccountCode>51835</GLAccountCode>
          <AccountBlock>51835</AccountBlock>
          <GLAccountDescription>51835</GLAccountDescription>
        </GLAccount>
        <AdditionalCoding>
          <ClientPONumber />
          <WorkOrderNumber>0313000005</WorkOrderNumber>
          <WorkOrderCompletedDate>2013-08-22T09:58:58</WorkOrderCompletedDate>
          <WorkOrderCreatedDate>2013-08-12T09:46:00</WorkOrderCreatedDate>
          <CodingBlock1>Materials</CodingBlock1>
          <CodingBlock2>Store Hours Signage</CodingBlock2>
          <CodingBlock4>vinyl hours and custom sign</CodingBlock4>
          <CodingBlock5>Signage</CodingBlock5>
        </AdditionalCoding>
      </InvoiceLineItem>
      <InvoiceLineItem>
        <OriginatingInvoiceLineID>7997</OriginatingInvoiceLineID>
        <POLineNumber>50</POLineNumber>
        <LineItemDescription>Tax</LineItemDescription>
        <LocationId>ATR000313</LocationId>
        <BuildingName>Conyers Store</BuildingName>
        <LineItemAmount>9.900000</LineItemAmount>
        <GLAccount>
          <CompanyId>51835</CompanyId>
          <GLAccountCode>51835</GLAccountCode>
          <AccountBlock>51835</AccountBlock>
          <GLAccountDescription>51835</GLAccountDescription>
        </GLAccount>
        <AdditionalCoding>
          <ClientPONumber />
          <WorkOrderNumber>0313000005</WorkOrderNumber>
          <WorkOrderCompletedDate>2013-08-22T09:58:58</WorkOrderCompletedDate>
          <WorkOrderCreatedDate>2013-08-12T09:46:00</WorkOrderCreatedDate>
          <CodingBlock1>Tax</CodingBlock1>
          <CodingBlock2>Store Hours Signage</CodingBlock2>
          <CodingBlock4>Tax</CodingBlock4>
          <CodingBlock5>Signage</CodingBlock5>
        </AdditionalCoding>
      </InvoiceLineItem>
    </InvoiceLineItems>
  </Invoice>
</root>

-----------------------------------

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Gertone (Geert Bormans)
Gertone (Geert Bormans)
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mani Pazhana

ASKER

Thanks. It worked.
welcome,

Note that in fact you should not delete the " from a datafield in csv
you should actually tripple the " to be correct
(and basically you only need " around fields that have a comma inside, or a pipe in this example)
Got it.