1 2 Previous Next 15 Replies Latest reply on Mar 19, 2017 9:42 AM by quarku

    XSL style - proffesional help needed

    quarku

      Dear friends,

       

      I would like to ask you for a professional XSL help. I have almost no knowledge in the topic so I knew form the beginning that I would fail - even so I have tried to create my own XSL stylesheet...

       

      What i need is to get "Proper_result.xml" file from filemaker data organised as you can see in Excel file named "Sample_data.xlsx".

       

      Unfortunately the nearest file I can get so far, is the file created without stylesheet base on FMPDSORESULT grammar which you can check under the name "My_result.xml".

       

      I'm getting this error with my stylesheet - "Initial_error.jpg"

       

      But even without it the result will for sure not be as expected - believe me  !

       

      My stylesheet - "XSL_stylesheet.xsl"

       

      I would really appreciate your help or even more - corrected XSL file.

       

      I wish you all very great weekend.

        • 1. Re: XSL style - proffesional help needed
          beverly

          Your "raw" FMPDSORESULT should follow this format/grammar:

           

          The "Proper_result.xml" that you posted does NOT follow this format. (There are no ROW elements, for example).

          However your "My_result.xml" does seem to follow the grammar.

           

          If your aim is the XSLX, then perhaps an Export as that (or Save As Excel) should be used?

           

          FMPXMLRESULT is more closely similar to Excel (a "header" in the METADATA).

           

          beverly

          • 2. Re: XSL style - proffesional help needed
            quarku

            Hello there,

            thanks for reply.

             

            In short my aim is to get xml output similar to "Proper_result.xml" from my database thanks to some kind of XSL stylesheet.

             

            I have totally no knowledge about XML and XSL - I have just tried to do something.

            • 3. Re: XSL style - proffesional help needed
              beverly

              The Proper_result.xml is not valid XML.  What do you propose to do with the document once you get it?

               

              beverly

              • 4. Re: XSL style - proffesional help needed
                quarku

                Hello Beverly,

                 

                Out tax regulations require to sent all invoices according to the structure described in new attached file called - description. I know that it's in polish but at least the structure it's shown as graph maybe this will help you.

                 

                I have also attached original example gather form Tax goverment institute - this XML example have to be valid (it's official one).

                 

                Additionally there is XSD structure file - maybe it will help you as well.

                 

                Best regards

                • 5. Re: XSL style - proffesional help needed
                  beverly

                  Did you mean to upload new files? You need to reply and select "Use advanced editor" in the upper-right of the comment box.

                   

                  beverly

                  • 6. Re: XSL style - proffesional help needed
                    quarku

                    They are in main post. on top of this page. all three files with "NEW_" part of the name.

                     

                    I think maybe there is a way to convert XSD file to XSLT

                    • 7. Re: XSL style - proffesional help needed
                      beverly

                      no XSD cannot be converted to XSLT, because you are trying to "map" the export (your data) into the format used by the place you are sending it.

                      the XSD is a "format" of the data you need, but it still needs to be used in a way to make the map.

                      beverly

                      • 8. Re: XSL style - proffesional help needed
                        quarku

                        I have some idea - Mapforce - I will map my xsd with required xsd.

                        • 9. Re: XSL style - proffesional help needed
                          quarku

                          This works fine but it convers only 1 record (im sure that Im exporting all at once):

                           

                          <?xml version="1.0" encoding="UTF-8"?>

                           

                          <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ns0="http://www.filemaker.com/fmpdsoresult" xmlns:xs="http://www.w3.org/2001/XMLSchema" exclude-result-prefixes="ns0 xs">

                              <xsl:output method="xml" encoding="UTF-8" indent="yes"/>

                              <xsl:template match="/">

                                  <xsl:variable name="var1_initial" select="."/>

                                  <JPK xmlns="http://jpk.mf.gov.pl/wzor/2016/10/26/10261/" xmlns:kck="http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2013/05/23/eD/KodyCECHKRAJOW/" xmlns:etd="http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2016/01/25/eD/DefinicjeTypy/">

                                      <xsl:attribute name="xsi:schemaLocation" namespace="http://www.w3.org/2001/XMLSchema-instance">http://jpk.mf.gov.pl/wzor/2016/10/26/10261/ file:///C:/Users/Quarku/Downloads/Jednolity%20plik%20kontrolny/Schemat_JPK_VAT(2)_v1-0.xsd</xsl:attribute>

                                      <Naglowek>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var2_cur" select="."/>

                                              <KodFormularza>

                                                  <xsl:attribute name="kodSystemowy" namespace="">

                                                      <xsl:value-of select="ns0:ROW/ns0:kodSystemowy"/>

                                                  </xsl:attribute>

                                                  <xsl:attribute name="wersjaSchemy" namespace="">

                                                      <xsl:value-of select="ns0:ROW/ns0:wersjaSchemy"/>

                                                  </xsl:attribute>

                                                  <xsl:value-of select="ns0:ROW/ns0:KodFormularza"/>

                                              </KodFormularza>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var3_cur" select="."/>

                                              <WariantFormularza>

                                                  <xsl:value-of select="number(ns0:ROW/ns0:WariantFormularza)"/>

                                              </WariantFormularza>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var4_cur" select="."/>

                                              <CelZlozenia>

                                                  <xsl:value-of select="number(ns0:ROW/ns0:CelZlozenia)"/>

                                              </CelZlozenia>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var5_cur" select="."/>

                                              <DataWytworzeniaJPK>

                                                  <xsl:value-of select="ns0:ROW/ns0:DataWytworzeniaJPK"/>

                                              </DataWytworzeniaJPK>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var6_cur" select="."/>

                                              <DataOd>

                                                  <xsl:value-of select="ns0:ROW/ns0:DataOd"/>

                                              </DataOd>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var7_cur" select="."/>

                                              <DataDo>

                                                  <xsl:value-of select="ns0:ROW/ns0:DataDo"/>

                                              </DataDo>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var8_cur" select="."/>

                                              <DomyslnyKodWaluty>

                                                  <xsl:value-of select="ns0:ROW/ns0:DomyslnyKodWaluty"/>

                                              </DomyslnyKodWaluty>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var9_cur" select="."/>

                                              <KodUrzedu>

                                                  <xsl:value-of select="number(ns0:ROW/ns0:KodUrzedu)"/>

                                              </KodUrzedu>

                                          </xsl:for-each>

                                      </Naglowek>

                                      <Podmiot1>

                                          <IdentyfikatorPodmiotu>

                                              <xsl:for-each select="ns0:FMPDSORESULT">

                                                  <xsl:variable name="var10_cur" select="."/>

                                                  <etd:NIP>

                                                      <xsl:value-of select="number(ns0:ROW/ns0:NIP)"/>

                                                  </etd:NIP>

                                              </xsl:for-each>

                                              <xsl:for-each select="ns0:FMPDSORESULT">

                                                  <xsl:variable name="var11_cur" select="."/>

                                                  <etd:PelnaNazwa>

                                                      <xsl:value-of select="ns0:ROW/ns0:PelnaNazwa"/>

                                                  </etd:PelnaNazwa>

                                              </xsl:for-each>

                                              <xsl:for-each select="ns0:FMPDSORESULT">

                                                  <xsl:variable name="var12_cur" select="."/>

                                                  <etd:REGON>

                                                      <xsl:value-of select="number(ns0:ROW/ns0:REGON)"/>

                                                  </etd:REGON>

                                              </xsl:for-each>

                                          </IdentyfikatorPodmiotu>

                                          <AdresPodmiotu>

                                              <xsl:for-each select="ns0:FMPDSORESULT">

                                                  <xsl:variable name="var13_cur" select="."/>

                                                  <KodKraju>

                                                      <xsl:value-of select="ns0:ROW/ns0:KodKraju"/>

                                                  </KodKraju>

                                              </xsl:for-each>

                                              <xsl:for-each select="ns0:FMPDSORESULT">

                                                  <xsl:variable name="var14_cur" select="."/>

                                                  <Wojewodztwo>

                                                      <xsl:value-of select="ns0:ROW/ns0:Wojewodztwo"/>

                                                  </Wojewodztwo>

                                              </xsl:for-each>

                                              <xsl:for-each select="ns0:FMPDSORESULT">

                                                  <xsl:variable name="var15_cur" select="."/>

                                                  <Powiat>

                                                      <xsl:value-of select="ns0:ROW/ns0:Powiat"/>

                                                  </Powiat>

                                              </xsl:for-each>

                                              <xsl:for-each select="ns0:FMPDSORESULT">

                                                  <xsl:variable name="var16_cur" select="."/>

                                                  <Gmina>

                                                      <xsl:value-of select="ns0:ROW/ns0:Gmina"/>

                                                  </Gmina>

                                              </xsl:for-each>

                                              <xsl:for-each select="ns0:FMPDSORESULT">

                                                  <xsl:variable name="var17_cur" select="."/>

                                                  <Ulica>

                                                      <xsl:value-of select="ns0:ROW/ns0:Ulica"/>

                                                  </Ulica>

                                              </xsl:for-each>

                                              <xsl:for-each select="ns0:FMPDSORESULT">

                                                  <xsl:variable name="var18_cur" select="."/>

                                                  <NrDomu>

                                                      <xsl:value-of select="number(ns0:ROW/ns0:NrDomu)"/>

                                                  </NrDomu>

                                              </xsl:for-each>

                                              <xsl:for-each select="ns0:FMPDSORESULT">

                                                  <xsl:variable name="var19_cur" select="."/>

                                                  <NrLokalu/>

                                              </xsl:for-each>

                                              <xsl:for-each select="ns0:FMPDSORESULT">

                                                  <xsl:variable name="var20_cur" select="."/>

                                                  <Miejscowosc>

                                                      <xsl:value-of select="ns0:ROW/ns0:Miejscowosc"/>

                                                  </Miejscowosc>

                                              </xsl:for-each>

                                              <xsl:for-each select="ns0:FMPDSORESULT">

                                                  <xsl:variable name="var21_cur" select="."/>

                                                  <KodPocztowy>

                                                      <xsl:value-of select="ns0:ROW/ns0:KodPocztowy"/>

                                                  </KodPocztowy>

                                              </xsl:for-each>

                                              <xsl:for-each select="ns0:FMPDSORESULT">

                                                  <xsl:variable name="var22_cur" select="."/>

                                                  <Poczta>

                                                      <xsl:value-of select="ns0:ROW/ns0:Poczta"/>

                                                  </Poczta>

                                              </xsl:for-each>

                                          </AdresPodmiotu>

                                      </Podmiot1>

                                      <SprzedazWiersz>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var23_cur" select="."/>

                                              <xsl:attribute name="typ" namespace="">

                                                  <xsl:value-of select="ns0:ROW/ns0:typSprzedazy"/>

                                              </xsl:attribute>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var24_cur" select="."/>

                                              <LpSprzedazy>

                                                  <xsl:value-of select="number(ns0:ROW/ns0:LpSprzedazy)"/>

                                              </LpSprzedazy>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var25_cur" select="."/>

                                              <NrKontrahenta>

                                                  <xsl:value-of select="number(ns0:ROW/ns0:NrKontrahenta)"/>

                                              </NrKontrahenta>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var26_cur" select="."/>

                                              <NazwaKontrahenta>

                                                  <xsl:value-of select="ns0:ROW/ns0:NazwaKontrahenta"/>

                                              </NazwaKontrahenta>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var27_cur" select="."/>

                                              <AdresKontrahenta>

                                                  <xsl:value-of select="ns0:ROW/ns0:AdresKontrahenta"/>

                                              </AdresKontrahenta>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var28_cur" select="."/>

                                              <DowodSprzedazy>

                                                  <xsl:value-of select="ns0:ROW/ns0:DowodSprzedazy"/>

                                              </DowodSprzedazy>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var29_cur" select="."/>

                                              <DataWystawienia>

                                                  <xsl:value-of select="ns0:ROW/ns0:DataWystawienia"/>

                                              </DataWystawienia>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var30_cur" select="."/>

                                              <DataSprzedazy>

                                                  <xsl:value-of select="ns0:ROW/ns0:DataSprzedazy"/>

                                              </DataSprzedazy>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var31_cur" select="."/>

                                              <K_10>

                                                  <xsl:value-of select="number(ns0:ROW/ns0:K_10)"/>

                                              </K_10>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var32_cur" select="."/>

                                              <K_13>

                                                  <xsl:value-of select="number(ns0:ROW/ns0:K_13)"/>

                                              </K_13>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var33_cur" select="."/>

                                              <K_15>

                                                  <xsl:value-of select="number(ns0:ROW/ns0:K_15)"/>

                                              </K_15>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var34_cur" select="."/>

                                              <K_16>

                                                  <xsl:value-of select="number(ns0:ROW/ns0:K_16)"/>

                                              </K_16>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var35_cur" select="."/>

                                              <K_17>

                                                  <xsl:value-of select="number(ns0:ROW/ns0:K_17)"/>

                                              </K_17>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var36_cur" select="."/>

                                              <K_18>

                                                  <xsl:value-of select="number(ns0:ROW/ns0:K_18)"/>

                                              </K_18>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var37_cur" select="."/>

                                              <K_19>

                                                  <xsl:value-of select="number(ns0:ROW/ns0:K_19)"/>

                                              </K_19>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var38_cur" select="."/>

                                              <K_20>

                                                  <xsl:value-of select="number(ns0:ROW/ns0:K_20)"/>

                                              </K_20>

                                          </xsl:for-each>

                                      </SprzedazWiersz>

                                      <SprzedazCtrl>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var39_cur" select="."/>

                                              <LiczbaWierszySprzedazy/>

                                          </xsl:for-each>

                                          <xsl:for-each select="ns0:FMPDSORESULT">

                                              <xsl:variable name="var40_cur" select="."/>

                                              <PodatekNalezny/>

                                          </xsl:for-each>

                                      </SprzedazCtrl>

                                  </JPK>

                              </xsl:template>

                          </xsl:stylesheet>

                          • 10. Re: XSL style - proffesional help needed
                            beverly

                            Ah! I see you added the new files to the original post.

                             

                            your "for-each" is looping the entire document each time throughout the XSLT. You'll need a "loop" with the ROW element in the FMPDSORESULT grammar.

                            IF you only exported one record it would not matter, but you have more than one record (shown as ROW in the xml) and those must be handled correctly in the XSLT.

                             

                            It's quite difficult for me to understand what you have and what you need. (I cannot read the XSD documentation.) But you may be close and perhaps my hints will help.

                             

                            beverly

                            • 11. Re: XSL style - proffesional help needed
                              quarku

                              Hello there,

                              I create completly new xslt 1.0 script (sadly filemaker do not support xslt 2.0).

                               

                              And I'm almost there

                              Link to xslt files: Almost_there.zip - Google Drive

                               

                              The only thing I still can' solve it empty fields.

                               

                              For example I have sales document (invoice) which as a record have all fields for purchase invoice type empty (for example purchase date - empty because it is a sales document).

                              So in XML file i have a lot of empty spots.

                               

                              Is there any way to add something like "If" function into xslt? If (field is empty) - do not proceed etc.

                              • 12. Re: XSL style - proffesional help needed
                                beverly

                                Yes there is!

                                xsl:if test='..your element to test..'

                                ...

                                /xsl:if

                                 

                                However you should be getting an empty element without the "if"

                                &lt;element /&gt;

                                and your result xml is still valid.

                                • 13. Re: XSL style - proffesional help needed
                                  quarku

                                  Hello,

                                   

                                  Can you show my one example how to implement it in my code?

                                   

                                  this is the part i don't want to proceed if it is sales invoice (field typZakupu is empty).

                                   

                                  <xsl:for-each select="ns0:FMPDSORESULT/ns0:ROW">

                                                  <xsl:variable name="var6_cur" select="."/>

                                                  <ZakupWiersz>

                                                      <xsl:attribute name="typ" namespace="">

                                                          <xsl:value-of select="ns0:typZakupu"/>

                                                      </xsl:attribute>

                                                      <LpZakupu>

                                                          <xsl:value-of select="ns0:LpZakupu"/>

                                                      </LpZakupu>

                                                      <NrDostawcy>

                                                          <xsl:value-of select="ns0:NrDostawcy"/>

                                                      </NrDostawcy>

                                                      <NazwaDostawcy>

                                                          <xsl:value-of select="ns0:NazwaDostawcy"/>

                                                      </NazwaDostawcy>

                                                      <AdresDostawcy>

                                                          <xsl:value-of select="ns0:AdresDostawcy"/>

                                                      </AdresDostawcy>

                                                      <DowodZakupu>

                                                          <xsl:value-of select="ns0:DowodZakupu"/>

                                                      </DowodZakupu>

                                                      <DataZakupu>

                                                          <xsl:value-of select="ns0:DataZakupu"/>

                                                      </DataZakupu>

                                                      <DataWplywu>

                                                          <xsl:value-of select="ns0:DataWplywu"/>

                                                      </DataWplywu>

                                                      <K_43>

                                                          <xsl:value-of select="ns0:K_43"/>

                                                      </K_43>

                                                      <K_44>

                                                          <xsl:value-of select="ns0:K_44"/>

                                                      </K_44>

                                                      <K_45>

                                                          <xsl:value-of select="ns0:K_45"/>

                                                      </K_45>

                                                      <K_46>

                                                          <xsl:value-of select="ns0:K_46"/>

                                                      </K_46>

                                                      <K_47>

                                                          <xsl:value-of select="ns0:K_47"/>

                                                      </K_47>

                                                      <K_48>

                                                          <xsl:value-of select="ns0:K_48"/>

                                                      </K_48>

                                                      <K_49>

                                                          <xsl:value-of select="ns0:K_49"/>

                                                      </K_49>

                                                      <K_50>

                                                          <xsl:value-of select="ns0:K_50"/>

                                                      </K_50>

                                                  </ZakupWiersz>

                                  </xsl:for-each>

                                   

                                  &lt;element /&gt; < looks like java

                                  • 14. Re: XSL style - proffesional help needed
                                    beverly

                                    I don't know what is 'sales invoice'. Follow the link I'd posted. There are some very good tutorials on XSLT. You are correct that FM only uses v1.0

                                     

                                    As an alternative, if you are more comfortable creating scripts and variables and temporary records in a temporary table, a calculated XML can be achieved for export.

                                    1 2 Previous Next