XSLT - process pipe delimiter file

i am using XSLT to process pipe delimited text file to xml output...

Here is my Input file (Pipe delimited)

CLIENTID|PAYMENTID|PAYEEID|PAYEEDESCRIPTION|PAYMENTDATE|PAYMENTNUMBER|PAYMENTMETHODCODE|PAYMENTMETHODDESCRIPTION|TOTALPAYMENTAMOUNT|CURRENCYCODE|ISVOID|PAYMENTLINENUMBER|ORIGINATINGINVOICENUMBER|ORIGINATINGINVOICELINENUMBER|INVOICEDATE|PONUMBER|POLINENUMBER|DOCUMENTTYPE|DOCUMENTCOMPANY|DOCUMENTNUMBER|PAYMENTAMOUNT|REMARK|COMPANYID|GLACCOUNTCODE|SUBSIDIARY|SUBLEDGER|SUBLEDGERTYPE|LEDGERTYPE|COSTCENTER|ACCOUNTBLOCK|GLACCOUNTDESCRIPTION|CLIENTACCOUNTCODE|WBSCODE|ORIGINATINGPROJECTNUMBER|ORIGINATINGPROJECTSYSTEM|CLIENTPONUMBER|WORKORDERNUMBER|WORKORDERCOMPLETEDDATE|WORKORDERCREATEDDATE|CODINGBLOCK1|CODINGBLOCK2|CODINGBLOCK3|CODINGBLOCK4|CODINGBLOCK5|CODINGBLOCK6|CODINGDATE1|CODINGDATE2
JCP|460732|237936|24 7 GLASS STOREFRONT CORP|2013-08-12T00:00:00 |1000|PK|C|-100.0|USD|0|1|INV 1 JCP|001|2013-01-01T00:00:00 ||0.0|PV|03200|2161050|-100.0|Invoice 1 Rema                |03200|50490 |001|PRO|C|AA|   JCP00001M|001.50490|Cust - Lamp Replacement|J1|||||work ord 1               ||||||||||
JCP|460733|237937|A-OK SECURITY|2013-08-12T00:00:00 |1001|PK|C|-200.0|USD|0|1|INV 2 JCP|001|2013-01-01T00:00:00 ||0.0|PV|03200|2161051|-200.0|Invoice 2 Rema                |03200|50490 |001|PRO|C|AA|   JCP00004M|001.50490|Cust - Lamp Replacement|J1|||||work ord 2               ||||||||||
JCP|460734|237938|ACTION DOOR COMPANY|2013-08-12T00:00:00 |1002|PK|C|-300.0|USD|0|1|INV 3 JCP|001|2013-01-01T00:00:00 ||0.0|PV|03200|2161052|-300.0|Invoice 3 Rema                |03200|50490 |001|PRO|C|AA|   JCP00005M|001.50490|Cust - Lamp Replacement|J1|||||work ord 3               ||||||||||
JCP|460735|237939|ALADDIN ELECTRIC INC MI|2013-08-12T00:00:00 |1003|PK|C|-400.0|USD|0|1|INV 4 JCP|001|2013-01-01T00:00:00 ||0.0|PV|03200|2161053|-400.0|Invoice 4 Rema                |03200|50490 |001|PRO|C|AA|   JCP00005T|001.50490|Cust - Lamp Replacement|J1|||||work ord 4               ||||||||||
JCP|460736|237940|ALPINE ELECTRIC LODI|2013-08-12T00:00:00 |1004|PK|C|-500.0|USD|0|1|INV 5 JCP|001|2013-01-01T00:00:00 ||0.0|PV|03200|2161054|-500.0|Invoice 5 Rema                |03200|50490 |001|PRO|C|AA|   JCP00007M|001.50490|Cust - Lamp Replacement|J1|||||work ord 5               ||||||||||
JCP|460737|237941|ANTHONY ROOFING A TECTA AM CO|2013-08-12T00:00:00 |1005|PK|C|-600.0|USD|0|1|INV 6 JCP|001|2013-01-01T00:00:00 ||0.0|PV|03200|2161055|-600.0|Invoice 6 Rema                |03200|50490 |001|PRO|C|AA|   JCP00012M|001.50490|Cust - Lamp Replacement|J1|||||work ord 6               ||||||||||

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


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="http://integration.cbre.com/schemas/gcs/remittance/v1">                  
      

      <xsl:output method="xml" indent="yes" omit-xml-declaration="yes"/>
      <xsl:strip-space elements="*"/>
      
      <xsl:template match="/">
            <Remittance>
                  <xsl:for-each select="//batch/row">
                        
                        <xsl:element name="ClientId">
                              <xsl:value-of select="CLIENTID" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="PaymentId">
                              <xsl:value-of select="PAYMENTID" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="PayeeId">
                              <xsl:value-of select="PAYEEID" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="PayeeDescription">
                              <xsl:value-of select="PAYEEDESCRIPTION" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="PaymentDate">
                              <xsl:value-of select="normalize-space(PAYMENTDATE)" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="PaymentNumber">
                              <xsl:value-of select="PAYMENTNUMBER" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="PaymentMethodCode">
                              <xsl:value-of select="PAYMENTMETHODCODE" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="PaymentMethodDescription">
                              <xsl:value-of select="PAYMENTMETHODDESCRIPTION" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="TotalPaymentAmount">
                              <xsl:value-of select="TOTALPAYMENTAMOUNT" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="CurrencyCode">
                              <xsl:value-of select="CURRENCYCODE" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="IsVoid">
                              <xsl:value-of select="ISVOID" disable-output-escaping="yes"/>
                        </xsl:element>
                  
                        <!-- Payment Details -->
                        <PaymentDetails>
                              <PaymentDetail>
                                    <xsl:element name="PaymentLineNumber">
                                          <xsl:value-of select="PAYMENTLINENUMBER" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="OriginatingInvoiceNumber">
                                          <xsl:value-of select="ORIGINATINGINVOICENUMBER" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="OriginatingInvoiceLineNumber">
                                          <xsl:value-of select="ORIGINATINGINVOICELINENUMBER" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="InvoiceDate">
                                          <xsl:value-of select="normalize-space(INVOICEDATE)" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="PONumber">
                                          <xsl:value-of select="PONUMBER" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="POLineNumber">
                                          <xsl:value-of select="POLINENUMBER" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="DocumentType">
                                          <xsl:value-of select="DOCUMENTTYPE" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="DocumentCompany">
                                          <xsl:value-of select="DOCUMENTCOMPANY" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="DocumentNumber">
                                          <xsl:value-of select="DOCUMENTNUMBER" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="PaymentAmount">
                                          <xsl:value-of select="PAYMENTAMOUNT" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="Remark">
                                          <xsl:value-of select="normalize-space(REMARK)" disable-output-escaping="yes"/>
                                    </xsl:element>

                                    <!-- GL Account -->
                                    <GLAccount>
                                          <xsl:element name="CompanyId">
                                                <xsl:value-of select="COMPANYID" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="GLAccountCode">
                                                <xsl:value-of select="GLACCOUNTCODE" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="Subsidiary">
                                                <xsl:value-of select="SUBSIDIARY" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="SubLedger">
                                                <xsl:value-of select="SUBLEDGER" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="SubLedgerType">
                                                <xsl:value-of select="SUBLEDGERTYPE" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="CostCenter">
                                                <xsl:value-of select="normalize-space(COSTCENTER)" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="AccountBlock">
                                                <xsl:value-of select="ACCOUNTBLOCK" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="GLAccountDescription">
                                                <xsl:value-of select="GLACCOUNTDESCRIPTION" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="ClientAccountCode">
                                                <xsl:value-of select="CLIENTACCOUNTCODE" disable-output-escaping="yes"/>
                                          </xsl:element>
                                    </GLAccount>

                                    <!-- Additional Coding -->
                                    <AdditionalCoding>
                                          <xsl:element name="WBSCode">
                                                <xsl:value-of select="WBSCode" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="OriginatingProjectNumber">
                                                <xsl:value-of select="ORIGINATINGPROJECTNUMBER" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="OriginatingProjectSystem">
                                                <xsl:value-of select="ORIGINATINGPROJECTSYSTEM" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="ClientPONumber">
                                                <xsl:value-of select="CLIENTPONUMBER" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="WorkOrderNumber">
                                                <xsl:value-of select="WORKORDERNUMBER" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="WorkOrderCompletedDate">
                                                <xsl:value-of select="normalize-space(WORKORDERCOMPLETEDDATE)" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="WorkOrderCreatedDate">
                                                <xsl:value-of select="normalize-space(WORKORDERCREATEDDATE)" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="CodingBlock1">
                                                <xsl:value-of select="CODINGBLOCK1" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="CodingBlock2">
                                                <xsl:value-of select="CODINGBLOCK2" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="CodingBlock3">
                                                <xsl:value-of select="CODINGBLOCK3" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="CodingBlock4">
                                                <xsl:value-of select="CODINGBLOCK4" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="CodingBlock5">
                                                <xsl:value-of select="CODINGBLOCK5" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="CodingBlock6">
                                                <xsl:value-of select="CODINGBLOCK6" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="CodingDate1">
                                                <xsl:value-of select="normalize-space(CODINGDATE1)" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="CodingDate2">
                                                <xsl:value-of select="normalize-space(CODINGDATE2)" disable-output-escaping="yes"/>
                                          </xsl:element>
                                    </AdditionalCoding>
                              </PaymentDetail>
                        </PaymentDetails>
                  </xsl:for-each>      
            </Remittance>
      </xsl:template>

</xsl:stylesheet>

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

i am getting this error:

XmlException
------------
Data at the root level is invalid. Line 1, position 1.



Any Idea?

Thanks
LVL 8
mani_saiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert BormansInformation ArchitectCommented:
input data for XSLT requires to be wellformed XML
try to pass it in as a parameter
0
mani_saiAuthor Commented:
can you please provide me sample code?
0
Geert BormansInformation ArchitectCommented:
make an xsl:param at the global level

and pass a value to it, being the pipe string

http://stackoverflow.com/questions/1521064/passing-parameters-to-xslt-stylesheet-via-net

or even better, use a generic pipe 2 xml as a first step.
XSLT is a real mess for processing pipe delimited files
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Geert BormansInformation ArchitectCommented:
google for " pipe delimited to xml converter "

or import in excel and export as xml
0
mani_saiAuthor Commented:
Thanks.
0
mani_saiAuthor Commented:
Here is my issue:
 Remark tag is having some issue by wrapping into two lines... (we are seeing CR LF in the output)

Here is the Input XMl:

<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>1970</OriginatingInvoiceId>
            <VendorId>242354</VendorId>
            <AlternativeVendorId>PRV-13-1307</AlternativeVendorId>
            <VendorInvoiceNumber>13-8041</VendorInvoiceNumber>
            <InvoiceDate>2013-08-08T00:00:00</InvoiceDate>
            <InvoiceReceiveDate>2013-08-15T00:00:00</InvoiceReceiveDate>
            <GLDate>2013-08-21T20:53:03.81-05:00</GLDate>
            <Remark>Please repair torn and worn carpet squares on the sales floor.
972-409-0026</Remark>
           <TotalInvoiceAmount>239.23</TotalInvoiceAmount>
            <CurrencyCode>USD</CurrencyCode>
            <StatusCode>
                  <common:Code>Authorized</common:Code>
                  <common:Description>Authorized</common:Description>
            </StatusCode>
            <ClientApprovalNumber>1000119702</ClientApprovalNumber>
            <InvoiceSource>COR</InvoiceSource>
            <NumberOfLines>3</NumberOfLines>
            <InvoiceLineItems>
                  <InvoiceLineItem>
                        <OriginatingInvoiceLineID>4637</OriginatingInvoiceLineID>
                        <POLineNumber>34</POLineNumber>
                        <LineItemDescription>Replace case of carpet tiles on sales floor. 1 tech 2 hrs 2013-08-08T00:00:00</LineItemDescription>
                        <LocationId>ATR002037</LocationId>
                        <BuildingName>7800 N MACARTHUR BLVD SPACE#17</BuildingName>
                        <LineItemAmount>76.000000</LineItemAmount>
                        <GLAccount>
                              <CompanyId>51765</CompanyId>
                              <GLAccountCode>51765</GLAccountCode>
                              <AccountBlock>51765</AccountBlock>
                              <GLAccountDescription>51765</GLAccountDescription>
                        </GLAccount>
                        <AdditionalCoding>
                              <ClientPONumber />
                              <WorkOrderNumber>2037000001</WorkOrderNumber>
                              <WorkOrderCompletedDate>2013-08-19T16:12:38</WorkOrderCompletedDate>
                              <WorkOrderCreatedDate>2013-08-01T12:49:00</WorkOrderCreatedDate>
                              <CodingBlock1>Labor</CodingBlock1>
                              <CodingBlock2>Carpet Replace</CodingBlock2>
                              <CodingBlock4>Replace case of carpet tiles on sales floor. 1 tech 2 hrs</CodingBlock4>
                              <CodingBlock5>Flooring</CodingBlock5>
                        </AdditionalCoding>
                  </InvoiceLineItem>
                  <InvoiceLineItem>
                        <OriginatingInvoiceLineID>4638</OriginatingInvoiceLineID>
                        <POLineNumber>35</POLineNumber>
                        <LineItemDescription>carpet, glue</LineItemDescription>
                        <LocationId>ATR002037</LocationId>
                        <BuildingName>7800 N MACARTHUR BLVD SPACE#17</BuildingName>
                        <LineItemAmount>145.000000</LineItemAmount>
                        <GLAccount>
                              <CompanyId>51765</CompanyId>
                              <GLAccountCode>51765</GLAccountCode>
                              <AccountBlock>51765</AccountBlock>
                              <GLAccountDescription>51765</GLAccountDescription>
                        </GLAccount>
                        <AdditionalCoding>
                              <ClientPONumber />
                              <WorkOrderNumber>2037000001</WorkOrderNumber>
                              <WorkOrderCompletedDate>2013-08-19T16:12:38</WorkOrderCompletedDate>
                              <WorkOrderCreatedDate>2013-08-01T12:49:00</WorkOrderCreatedDate>
                              <CodingBlock1>Materials</CodingBlock1>
                              <CodingBlock2>Carpet Replace</CodingBlock2>
                              <CodingBlock4>carpet, glue</CodingBlock4>
                              <CodingBlock5>Flooring</CodingBlock5>
                        </AdditionalCoding>
                  </InvoiceLineItem>
                  <InvoiceLineItem>
                        <OriginatingInvoiceLineID>4636</OriginatingInvoiceLineID>
                        <POLineNumber>36</POLineNumber>
                        <LineItemDescription>Tax</LineItemDescription>
                        <LocationId>ATR002037</LocationId>
                        <BuildingName>7800 N MACARTHUR BLVD SPACE#17</BuildingName>
                        <LineItemAmount>18.230000</LineItemAmount>
                        <GLAccount>
                              <CompanyId>51765</CompanyId>
                              <GLAccountCode>51765</GLAccountCode>
                              <AccountBlock>51765</AccountBlock>
                              <GLAccountDescription>51765</GLAccountDescription>
                        </GLAccount>
                        <AdditionalCoding>
                              <ClientPONumber />
                              <WorkOrderNumber>2037000001</WorkOrderNumber>
                              <WorkOrderCompletedDate>2013-08-19T16:12:38</WorkOrderCompletedDate>
                              <WorkOrderCreatedDate>2013-08-01T12:49:00</WorkOrderCreatedDate>
                              <CodingBlock1>Tax</CodingBlock1>
                              <CodingBlock2>Carpet Replace</CodingBlock2>
                              <CodingBlock4>Tax</CodingBlock4>
                              <CodingBlock5>Flooring</CodingBlock5>
                        </AdditionalCoding>
                  </InvoiceLineItem>
            </InvoiceLineItems>
      </Invoice>
</root>


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

XSLT:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
                        xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                        xmlns="http://integration.cbre.com/schemas/gcs/batchinvoice/v2">
      <!--Client: AT&T  V1
      Corrigo XML File to Invoice CIM format.

      Version 1.0            Amy Force      Initial Version of the file based on batch/row/ nodes in file
            7/9/2013            Amy Force      Pickup and compare against invoice (root)
            7/23/2013            Amy Force      REST of batch of Invoices-->

      <xsl:output method="xml" indent="yes" />
      
      <xsl:variable name="new-root-node">
            <xsl:text>root</xsl:text>
      </xsl:variable>
 
      <!-- ITP is expecting multiple rows to be wrapped with batch -->
      <xsl:template match="/*">
 
            <!-- Create a new element with new node name. -->
            <xsl:element name="{$new-root-node}">
                  <!-- Copy all children of the root node. -->
                  <xsl:copy-of select="*" />
            </xsl:element>
      </xsl:template>
 
</xsl:stylesheet>


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

can we fix the wrapping issue <Remark> </Remark> in the XSLT?

Thanks
0
mani_saiAuthor Commented:
Gertone,
do you want me to post as new question?

Thanks
0
Geert BormansInformation ArchitectCommented:
I saw the new question first,
you might need to explain what exactly you want
0
mani_saiAuthor Commented:
Thanks
0
Geert BormansInformation ArchitectCommented:
welcome
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
XML

From novice to tech pro — start learning today.