Home > Enterprise >  create multiple xml documents of one object schema through a mapping of a csv base64 xml document
create multiple xml documents of one object schema through a mapping of a csv base64 xml document

Time:09-07

I have the following situation. A CSV file is transformed into an object schema, i.e. the lines of the CSV are encoded in Base64 code and put as raw data into an XML object schema. This object schema is then mapped to an existing other object schema. Everything works fine, if you have only one header position in the CSV file, which can have several line positions. The case that there are several header positions must also be covered.

So I have to look after the decoding of base64, if a Line at position 9 equals 2, then this is a HeaderLine and a separate XML file has to be created for this.

Is it possible at all or how to adapt the code to realize it. I have tried different possibilities, but I can't get any further.

Thanks for your help.

Current XSLT:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:extensions="http://www.infor.com/ION/XSL/extensions" exclude-result-prefixes="extensions" version="2.0">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="SyncMySupplierInvoice">
<SyncPurchaseOrder>
<xsl:apply-templates select="ApplicationArea"/>
<xsl:apply-templates select="DataArea"/>
</SyncPurchaseOrder>
</xsl:template>
<xsl:template match="ApplicationArea">
<xsl:element name="ApplicationArea">
<xsl:copy-of select="*"/>
</xsl:element>
</xsl:template>
<xsl:template match="DataArea">
<xsl:element name="DataArea">
<xsl:apply-templates select="Sync"/>
<xsl:apply-templates select="MySupplierInvoice/RawData"/>
</xsl:element>
</xsl:template>
<xsl:template match="Sync">
<xsl:element name="Load">
<TenantID>XXXXXXX</TenantID>
<AccountingEntityID>510</AccountingEntityID>
<LocationID/>
<ActionCriteria>
<ActionExpression actionCode="Add"/>
</ActionCriteria>
</xsl:element>
</xsl:template>
<xsl:template match="MySupplierInvoice/RawData">
<xsl:variable name="b64" select="extensions:decodeString(string(.))"/>
<xsl:variable name="Header" select="$b64"/>
<xsl:variable name="locCurrency" select="tokenize($Header,',')[position() = 8]"/>
<SupplierInvoice>
<SupplierInvoiceHeader>
<DocumentID>
<ID>
<xsl:value-of select="tokenize($Header,',')[position()=1]"/>
</ID>
</DocumentID>
<AlternateDocumentID>
<ID>
<xsl:value-of select="tokenize($Header,',')[position()=4]"/>
</ID>
</AlternateDocumentID>
<DocumentDateTime>
<xsl:value-of select=" tokenize($Header,',')[position()=6]"/>
</DocumentDateTime>
<LastModificationDateTime/>
<Description>
<xsl:value-of select="tokenize($Header,',')[position()=5]"/>
</Description>
<xsl:element name="TotalAmount">
<xsl:attribute name="currencyID">
<xsl:value-of select="$locCurrency"/>
</xsl:attribute>
<xsl:value-of select="tokenize($Header,',')[position() = 7]"/>
</xsl:element>
<RemitToParty/>
<PurchaseOrderReference>
<DocumentID>
<ID/>
</DocumentID>
</PurchaseOrderReference>
<BillFromParty>
<PartyIDs>
<ID>
<xsl:value-of select="tokenize($Header,',')[position() = 3]"/>
</ID>
<TaxID/>
<ID/>
</PartyIDs>
<Name/>
</BillFromParty>
</SupplierInvoiceHeader>
<xsl:for-each select="tokenize($Header,' ')">
<xsl:variable name="Line" select="."/>
<xsl:if test="position() != 1">
<!--    <xsl:if test="not(tokenize($Line,',')[position() = 9] = tokenize($Header,',')[position() = 9])">  -->
<SupplierInvoiceLine>
<LineNumber>
<xsl:value-of select="tokenize($Line,',')[position() = 9]"/>
</LineNumber>
<Description>
<xsl:value-of select="tokenize($Line,',')[position() =11]"/>
<xsl:text>/</xsl:text>
<xsl:value-of select="tokenize($Line,',')[position() =12]"/>
<xsl:text>/</xsl:text>
<xsl:value-of select="tokenize($Line,',')[position() =13]"/>
<xsl:text>/</xsl:text>
<xsl:value-of select="tokenize($Line,',')[position() =14]"/>
<xsl:text>/</xsl:text>
<xsl:value-of select="tokenize($Line,',')[position() =15]"/>
<xsl:text>/</xsl:text>
<xsl:value-of select="tokenize($Line,',')[position() =16]"/>
</Description>
<Quantity unitCode="PCE">1.00</Quantity>
<ItemID> </ItemID>
<UnitPrice>
<xsl:element name="Amount">
<xsl:attribute name="currencyID">
<xsl:value-of select="$locCurrency"/>
</xsl:attribute>
<xsl:value-of select="tokenize($Line,',')[position() = 22]"/>
</xsl:element>
<PerQuantity unitCode="PCE">1</PerQuantity>
</UnitPrice>
<xsl:element name="ExtendedAmount">
<xsl:attribute name="currencyID">
<xsl:value-of select="$locCurrency"/>
</xsl:attribute>
<xsl:value-of select="tokenize($Line,',')[position() = 22]"/>
</xsl:element>
<PurchaseOrderReference>
<DocumentID>
<ID/>
</DocumentID>
</PurchaseOrderReference>
<Tax>
<xsl:element name="BasisAmount">
<xsl:attribute name="currencyID">
<xsl:value-of select="$locCurrency"/>
</xsl:attribute>
<xsl:value-of select="tokenize($Line,',')[position() = 22]"/>
</xsl:element>
<Calculation>
<xsl:variable name="BaseAmount" select="tokenize($Line,',')[position() = 22]"/>
<xsl:variable name="VatAmount" select="tokenize($Line,',')[position() = 23]"/>
<!-- <xsl:variable name="VatAmount" select="substring-before($VatAmount, '&#xD;&#xA;')"/>  -->
<xsl:variable name="VatAmount" as="xs:double" select="if($VatAmount castable as xs:double) then xs:double($VatAmount) else 0"/>
<!--  <xsl:value-of select="$VatAmount"/>  -->
<RateNumeric>
<xsl:value-of select="round($VatAmount div number($BaseAmount),3)"/>
</RateNumeric>
</Calculation>
<xsl:element name="Amount">
<xsl:attribute name="currencyID">
<xsl:value-of select="$locCurrency"/>
</xsl:attribute>
<xsl:variable name="Amount" select="tokenize($Line,',')[position() = 23]"/>
<xsl:value-of select="$Amount"/>
</xsl:element>
</Tax>
</SupplierInvoiceLine>
<!--     <xsl:if test="not(tokenize($Line,',')[position() = 9] = tokenize($Header,',')[position() = 9])">  -->
<!--     Is this the right position to create a second XML Document Load.SupplierInvoice ?                 -->
<!--     How do I have to configure the code ???                                                           -->
<!--     </xsl:if>                                                                                         -->
</xsl:if>
</xsl:for-each>
</SupplierInvoice>
</xsl:template>
</xsl:stylesheet>

CSV Example:

11,Kostenrechnung,200000001,5511,701 -XXXXX,2022-08-15T09:00:00Z,1191.95,EUR,2,XXXXX,,,,,,,,,,,Haben,1191.95,0
11,Kostenrechnung,200000001,5511,701 - XXXXX,2022-08-15T09:00:00Z,1191.95,EUR,3,XXXXX,49450,210001,,,,,Einkauf,DE,120,Netto,Soll,328.23,22.98
11,Kostenrechnung,200000001,5511,701 - XXXXX,2022-08-15T09:00:00Z,1191.95,EUR,4,XXXXX,49450,210001,,,,,Einkauf,,0,Netto,Soll,254.54,0
11,Kostenrechnung,200000001,5511,701 - XXXXX,2022-08-15T09:00:00Z,1191.95,EUR,5,XXXXX,49480,410002,,,,,Einkauf,,0,Netto,Soll,78,0
11,Kostenrechnung,200000001,5511,701 - XXXXX,2022-08-15T09:00:00Z,1191.95,EUR,6,XXXXX,49800,999998,3000,,,,Einkauf,,0,Netto,Soll,139.8,0
11,Kostenrechnung,200000001,5511,701 - XXXXX,2022-08-15T09:00:00Z,1191.95,EUR,7,XXXXX,49690,210001,,,,,Einkauf,,0,Netto,Soll,368.4,0
13,Kostenrechnung,200000003,5512,701 - XXXXX,2022-08-30T09:00:00Z,773.55,EUR,2,XXXXX,,,,,,,,,,,Haben,773.55,0
13,Kostenrechnung,200000003,5512,701 - XXXXX,2022-08-30T09:00:00Z,773.55,EUR,3,XXXXX,49450,210001,,,,,Einkauf,DEU,000,Netto,Soll,36.40,0.00
13,Kostenrechnung,200000003,5512,701 - XXXXX,2022-08-30T09:00:00Z,773.55,EUR,4,XXXXX,49450,210001,,,,,Einkauf,DEU,000,Netto,Soll,54.00,0.00
13,Kostenrechnung,200000003,5512,701 - XXXXX,2022-08-30T09:00:00Z,773.55,EUR,5,XXXXX,49450,210001,,,,,Einkauf,DEU,000,Netto,Soll,97.20,0.00
13,Kostenrechnung,200000003,5512,701 - XXXXX,2022-08-30T09:00:00Z,773.55,EUR,6,XXXXX,49800,999998,3000,,,,Einkauf,DEU,000,Netto,Soll,488.40,0.00
13,Kostenrechnung,200000003,5512,701 - XXXXX,2022-08-30T09:00:00Z,773.55,EUR,7,XXXXX,49800,999998,3000,,,,Einkauf,DEU,132,Netto,Soll,81.97,15.58

CodePudding user response:

The way to create multiple output documents within a single XSLT is to use the result-document instruction:

https://www.w3.org/TR/xslt20/#element-result-document

What that means for this example is that it's necessary to group the CSV lines that are going into the same document.

To give an example of how to break up the CSV lines that you have presented into two different documents, I've put your input as the text in an XML element:

<csvLines>
11,Kostenrechnung,200000001,5511,701 -XXXXX,2022-08-15T09:00:00Z,1191.95,EUR,2,XXXXX,,,,,,,,,,,Haben,1191.95,0
11,Kostenrechnung,200000001,5511,701 - XXXXX,2022-08-15T09:00:00Z,1191.95,EUR,3,XXXXX,49450,210001,,,,,Einkauf,DE,120,Netto,Soll,328.23,22.98
11,Kostenrechnung,200000001,5511,701 - XXXXX,2022-08-15T09:00:00Z,1191.95,EUR,4,XXXXX,49450,210001,,,,,Einkauf,,0,Netto,Soll,254.54,0
11,Kostenrechnung,200000001,5511,701 - XXXXX,2022-08-15T09:00:00Z,1191.95,EUR,5,XXXXX,49480,410002,,,,,Einkauf,,0,Netto,Soll,78,0
11,Kostenrechnung,200000001,5511,701 - XXXXX,2022-08-15T09:00:00Z,1191.95,EUR,6,XXXXX,49800,999998,3000,,,,Einkauf,,0,Netto,Soll,139.8,0
11,Kostenrechnung,200000001,5511,701 - XXXXX,2022-08-15T09:00:00Z,1191.95,EUR,7,XXXXX,49690,210001,,,,,Einkauf,,0,Netto,Soll,368.4,0
13,Kostenrechnung,200000003,5512,701 - XXXXX,2022-08-30T09:00:00Z,773.55,EUR,2,XXXXX,,,,,,,,,,,Haben,773.55,0
13,Kostenrechnung,200000003,5512,701 - XXXXX,2022-08-30T09:00:00Z,773.55,EUR,3,XXXXX,49450,210001,,,,,Einkauf,DEU,000,Netto,Soll,36.40,0.00
13,Kostenrechnung,200000003,5512,701 - XXXXX,2022-08-30T09:00:00Z,773.55,EUR,4,XXXXX,49450,210001,,,,,Einkauf,DEU,000,Netto,Soll,54.00,0.00
13,Kostenrechnung,200000003,5512,701 - XXXXX,2022-08-30T09:00:00Z,773.55,EUR,5,XXXXX,49450,210001,,,,,Einkauf,DEU,000,Netto,Soll,97.20,0.00
13,Kostenrechnung,200000003,5512,701 - XXXXX,2022-08-30T09:00:00Z,773.55,EUR,6,XXXXX,49800,999998,3000,,,,Einkauf,DEU,000,Netto,Soll,488.40,0.00
13,Kostenrechnung,200000003,5512,701 - XXXXX,2022-08-30T09:00:00Z,773.55,EUR,7,XXXXX,49800,999998,3000,,,,Einkauf,DEU,132,Netto,Soll,81.97,15.58  
</csvLines>

I have an xslt that writes out a "main" document that simple shows the indices of the starts/ends of the lines:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="2.0"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  exclude-result-prefixes="#all">
  
  <xsl:output method="xml" indent="yes" />
  
  <xsl:variable name="inputLines" as="xs:string*" 
                select="tokenize(/csvLines/text(), '\n')[. ne '']" />
                
  <xsl:variable name="startIndices" as="xs:integer*"
                select="for $i in (1 to count($inputLines))
                        return $i[tokenize($inputLines[$i], ',')[9] eq '2']" />
  <xsl:variable name="endIndices" as="xs:integer*" 
                select="for $j in $startIndices
                        return min((count($inputLines), $startIndices[$j   1]-1))" />

  <xsl:template match="/" >
    <indices>
      <xsl:for-each select="(1 to count($startIndices))" >
        <xsl:variable name="index" as="xs:integer" select="." />
        <run startIndex="{$startIndices[$index]}" endIndex="{$endIndices[$index]}" />
      </xsl:for-each>
    </indices>

    <xsl:for-each select="(1 to count($startIndices))" >
      <xsl:result-document href="output{position()}" >
        <xsl:variable name="index" as="xs:integer" select="." />
        <lines>
          <xsl:for-each select="$inputLines[position() ge $startIndices[$index]
                                           and 
                                            position() le $endIndices[$index]]" >
            <line><xsl:value-of select="." /></line>
          </xsl:for-each>
        </lines>
      </xsl:result-document>
    </xsl:for-each>

  </xsl:template>
  
</xsl:stylesheet>


This produces a "main" output document just for demonstration:

<?xml version="1.0" encoding="UTF-8"?>
<indices>
   <run startIndex="1" endIndex="6"/>
   <run startIndex="7" endIndex="12"/>
</indices>

and two separate "result" documents, output1 and output2

<?xml version="1.0" encoding="UTF-8"?>
<lines>
   <line>11,Kostenrechnung,200000001,5511,701 -XXXXX,2022-08-15T09:00:00Z,1191.95,EUR,2,XXXXX,,,,,,,,,,,Haben,1191.95,0</line>
   <line>11,Kostenrechnung,200000001,5511,701 - XXXXX,2022-08-15T09:00:00Z,1191.95,EUR,3,XXXXX,49450,210001,,,,,Einkauf,DE,120,Netto,Soll,328.23,22.98</line>
   <line>11,Kostenrechnung,200000001,5511,701 - XXXXX,2022-08-15T09:00:00Z,1191.95,EUR,4,XXXXX,49450,210001,,,,,Einkauf,,0,Netto,Soll,254.54,0</line>
   <line>11,Kostenrechnung,200000001,5511,701 - XXXXX,2022-08-15T09:00:00Z,1191.95,EUR,5,XXXXX,49480,410002,,,,,Einkauf,,0,Netto,Soll,78,0</line>
   <line>11,Kostenrechnung,200000001,5511,701 - XXXXX,2022-08-15T09:00:00Z,1191.95,EUR,6,XXXXX,49800,999998,3000,,,,Einkauf,,0,Netto,Soll,139.8,0</line>
   <line>11,Kostenrechnung,200000001,5511,701 - XXXXX,2022-08-15T09:00:00Z,1191.95,EUR,7,XXXXX,49690,210001,,,,,Einkauf,,0,Netto,Soll,368.4,0</line>
</lines>
<?xml version="1.0" encoding="UTF-8"?>
<lines>
   <line>13,Kostenrechnung,200000003,5512,701 - XXXXX,2022-08-30T09:00:00Z,773.55,EUR,2,XXXXX,,,,,,,,,,,Haben,773.55,0</line>
   <line>13,Kostenrechnung,200000003,5512,701 - XXXXX,2022-08-30T09:00:00Z,773.55,EUR,3,XXXXX,49450,210001,,,,,Einkauf,DEU,000,Netto,Soll,36.40,0.00</line>
   <line>13,Kostenrechnung,200000003,5512,701 - XXXXX,2022-08-30T09:00:00Z,773.55,EUR,4,XXXXX,49450,210001,,,,,Einkauf,DEU,000,Netto,Soll,54.00,0.00</line>
   <line>13,Kostenrechnung,200000003,5512,701 - XXXXX,2022-08-30T09:00:00Z,773.55,EUR,5,XXXXX,49450,210001,,,,,Einkauf,DEU,000,Netto,Soll,97.20,0.00</line>
   <line>13,Kostenrechnung,200000003,5512,701 - XXXXX,2022-08-30T09:00:00Z,773.55,EUR,6,XXXXX,49800,999998,3000,,,,Einkauf,DEU,000,Netto,Soll,488.40,0.00</line>
   <line>13,Kostenrechnung,200000003,5512,701 - XXXXX,2022-08-30T09:00:00Z,773.55,EUR,7,XXXXX,49800,999998,3000,,,,Einkauf,DEU,132,Netto,Soll,81.97,15.58  </line>
</lines>

Hoping that this demonstrates the principle so that you can apply it in your environment.

CodePudding user response:

Thats the current Code, I dont think it will help but who knows

<?xml version="1.0" encoding="UTF-8"?>
<!--
    Mapping: SplitDocument
    Description: 
    Version: 1
    Created by: XXXXXXX
    Created on: 2022-09-05 12:38:24 UTC
    Last updated by: XXXXXXX
    Last updated on: 2022-09-05 12:52:09 UTC
    Input: Sync.SplitDocument
    Output: Sync.SplitDocument
    Product Version: Infor ION 2022.08.02
    XSLT Code Generator: V1 (Deprecated)

    Copyright © 2022 Infor. All rights reserved. 
    The word and design marks set forth herein are trademarks and/or registered trademarks of Infor and/or its affiliates and subsidiaries. 
    All rights reserved. All other trademarks listed herein are the property of their respective owners. 
    www.infor.com
-->
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:extensions="http://www.infor.com/ION/XSL/extensions"  xmlns="http://schema.infor.com/InforOAGIS/2" xmlns:expath-zip="http://expath.org/ns/zip" xmlns:fn="http://www.w3.org/2005/xpath-functions" xmlns:dns="http://schema.infor.com/InforOAGIS/2" xmlns:saxon="http://saxon.sf.net/" xmlns:expath-file="http://expath.org/ns/file" xmlns:exslt-random="http://exslt.org/random" xmlns:exslt-common="http://exslt.org/common" xmlns:exslt-math="http://exslt.org/math" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:math="http://www.w3.org/2005/xpath-functions/math" xmlns:map="http://www.w3.org/2005/xpath-functions/map" exclude-result-prefixes="xs xsl fn expath-file expath-zip exslt-common exslt-math exslt-random map math saxon">
    <xsl:output method="xml" indent="yes" />
    <xsl:strip-space elements="*"/>
    
    <xsl:variable name="inputLines" as="xs:string*" 
                select="tokenize(/csvLines/text(), '\n')[. ne '']" />
                
    <xsl:variable name="startIndices" as="xs:integer*"
                select="for $i in (1 to count($inputLines))
                        return $i[tokenize($inputLines[$i], ',')[9] eq '2']" />
    <xsl:variable name="endIndices" as="xs:integer*" 
                select="for $j in $startIndices
                        return min((count($inputLines), $startIndices[$j   1]-1))" />

    <xsl:template match="/">
    <indices>
      <xsl:for-each select="(1 to count($startIndices))" >
        <xsl:variable name="index" as="xs:integer" select="." />
        <run startIndex="{$startIndices[$index]}" endIndex="{$endIndices[$index]}" />
      </xsl:for-each>
    </indices>

    <xsl:for-each select="(1 to count($startIndices))" >
      <xsl:result-document href="output{position()}" >
        <xsl:variable name="index" as="xs:integer" select="." />
        <lines>
          <xsl:for-each select="$inputLines[position() ge $startIndices[$index]
                                           and 
                                            position() le $endIndices[$index]]" >
            <line><xsl:value-of select="." /></line>
          </xsl:for-each>
        </lines>
      </xsl:result-document>
    </xsl:for-each>

    </xsl:template>

    <indices>
        <run startIndex="1" endIndex="6"/>
        <run startIndex="7" endIndex="12"/>
    </indices>
    
</xsl:stylesheet> 
  • Related