0 Replies Latest reply on Feb 7, 2013 7:29 AM by MarthijnKoorn

    Importing XML (OpenTrans) into FileMaker via XSLT

    MarthijnKoorn

      Title

      Importing XML (OpenTrans) into FileMaker via XSLT

      Post

           After a lot of strugeling I decided to post my problem with importing XML into FileMaker on this forum. Hopefully I will find the answere here because it is driving me creazy! Let me explain the situation:

           My customer purchase product via an by its own designed portal (CLIP) From this site we can download the orders in PDF or in OpenTrans (XML) till now we always downloaded the PDF, printed it and put it manually back in our FileMaker Pro 11 CSM system. This is not an ideal situation...

           So I started to write a XSLT document for importing the XML-file from my customer into our system. This al worked out, but now I have the problem that I import empty records from the XML file. The problem seems to be the way of directing the METADATA to the xsl-file. Here is an example of the XML-file (OpenTrans) of my customer:

           
                <?xml version="1.0" encoding="UTF-8"?>
           
                <ORDER type="standard" version="1.0"
           
                  xmlns="http://www.opentrans.org/XMLSchema/1.0"
                 
                  <ORDER_HEADER>
           
                    <CONTROL_INFO>
           
                      <GENERATOR_INFO>CLiP2 Order V1.0 19.10.2004</GENERATOR_INFO>
           
                      <GENERATION_DATE>2013-02-04T13:23:53.0+01:00</GENERATION_DATE>
           
                    </CONTROL_INFO>
           
                    <ORDER_INFO>
           
                      <ORDER_ID>4502760764</ORDER_ID>
           
                      <ORDER_DATE>2012-11-12T00:00:00.0+01:00</ORDER_DATE>
           
                      <DELIVERY_DATE type="fixed">
           
                        <DELIVERY_START_DATE>2013-01-27T00:00:00.0+01:00</DELIVERY_START_DATE>
           
                        <DELIVERY_END_DATE>2013-01-27T00:00:00.0+01:00</DELIVERY_END_DATE>
           
                      </DELIVERY_DATE>
           
                      <ORDER_PARTIES>
           
                        <BUYER_PARTY>
           
                          <PARTY>
           
                            <PARTY_ID type="String">test test</PARTY_ID>
           
                            <ADDRESS>
           
                              <NAME>CompanyName</NAME>
           
                              <NAME2>GmbH</NAME2>
           
                              <NAME3>Test</NAME3>
           
                              <DEPARTMENT>031</DEPARTMENT>
           
                              <CONTACT>
           
                                <CONTACT_NAME>Test</CONTACT_NAME>
           
                                <PHONE type="office">00000000</PHONE>
           
                              </CONTACT>
           
                              <STREET>Street 12</STREET>
           
                              <ZIP>3132</ZIP>
           
                              <CITY>GreatGreat</CITY>
           
                              <COUNTRY>XX</COUNTRY>
           
                              <VAT_ID>13411113</VAT_ID>
           
                              <PHONE type="office"> 00000000 </PHONE>
           
                              <FAX> 00000000 </FAX>
           
                              <EMAIL>Neues IDoc-Feld TELF2; noch nicht in CLiP Tabelle BESTELLUNG enthalten.</EMAIL>
           
                              <URL>www.claas.com</URL>
           
                            </ADDRESS>
           
                          </PARTY>
           
                        </BUYER_PARTY>
           
                        <SUPPLIER_PARTY>
           
                          <PARTY>
           
                            <PARTY_ID type="String">00000000</PARTY_ID>
           
                            <ADDRESS>
           
                              <NAME>CompanyName2</NAME>
           
                              <STREET>Street 1331</STREET>
           
                              <ZIP>12313</ZIP>
           
                              <CITY>Nepalekd</CITY>
           
                              <COUNTRY>xx</COUNTRY>
           
                              <VAT_ID>124124141</VAT_ID>
           
                              <PHONE type="office">00000000</PHONE>
           
                              <FAX> 00000000 </FAX>
           
                            </ADDRESS>
           
                          </PARTY>
           
                        </SUPPLIER_PARTY>
           
                        <SHIPMENT_PARTIES>
           
                          <DELIVERY_PARTY>
           
                            <PARTY>
           
                              <PARTY_ID type="String">test test</PARTY_ID>
           
                              <ADDRESS>
           
                                <NAME> CompanyName </NAME>
           
                                <NAME2>CSP</NAME2>
           
                                <NAME3>99999</NAME3>
           
                                <STREET>street 12</STREET>
           
                                <ZIP>11222</ZIP>
           
                                <CITY>HamHam</CITY>
           
                                <COUNTRY>xx</COUNTRY>
           
                              </ADDRESS>
           
                            </PARTY>
           
                          </DELIVERY_PARTY>
           
                        </SHIPMENT_PARTIES>
           
                      </ORDER_PARTIES>
           
                      <PRICE_CURRENCY>EUR</PRICE_CURRENCY>
           
                      <PARTIAL_SHIPMENT_ALLOWED>TRUE</PARTIAL_SHIPMENT_ALLOWED>
           
                      <TRANSPORT>
           
                        <INCOTERM>FCA</INCOTERM>
           
                      </TRANSPORT>
           
                      <REMARK type="general">xxxx</REMARK>
           
                    </ORDER_INFO>
           
                  </ORDER_HEADER>
           
                  <ORDER_ITEM_LIST>
           
                    <ORDER_ITEM>
           
                      <LINE_ITEM_ID>00010</LINE_ITEM_ID>
           
                      <ARTICLE_ID>
           
                        <SUPPLIER_AID>00000000</SUPPLIER_AID>
           
                        <BUYER_AID type="String">0000000</BUYER_AID>
           
                        <DESCRIPTION_SHORT>CoolBox 1</DESCRIPTION_SHORT>
           
                        <DESCRIPTION_LONG>CoolBox 1</DESCRIPTION_LONG>
           
                        <MANUFACTURER_INFO>
           
                          <MANUFACTURER_NAME>NameCompany 2</MANUFACTURER_NAME>
           
                          <MANUFACTURER_AID>000000000</MANUFACTURER_AID>
           
                        </MANUFACTURER_INFO>
           
                      </ARTICLE_ID>
           
                      <QUANTITY>10.000</QUANTITY>
           
                      <ORDER_UNIT>PCE</ORDER_UNIT>
           
                      <ARTICLE_PRICE type="net_customer">
           
                        <PRICE_AMOUNT>55.05</PRICE_AMOUNT>
           
                        <PRICE_FLAG type="String">FALSE</PRICE_FLAG>
           
                        <PRICE_LINE_AMOUNT>550.5</PRICE_LINE_AMOUNT>
           
                        <PRICE_QUANTITY>1</PRICE_QUANTITY>
           
                      </ARTICLE_PRICE>
           
                      <DELIVERY_DATE type="fixed">
           
                        <DELIVERY_START_DATE>2013-01-27T00:00:00.0+01:00</DELIVERY_START_DATE>
           
                        <DELIVERY_END_DATE>2013-01-27T00:00:00.0+01:00</DELIVERY_END_DATE>
           
                      </DELIVERY_DATE>
           
                      <PARTIAL_SHIPMENT_ALLOWED>TRUE</PARTIAL_SHIPMENT_ALLOWED>
           
                    </ORDER_ITEM>
           
                    <ORDER_ITEM>
           
                      <LINE_ITEM_ID>00020</LINE_ITEM_ID>
           
                      <ARTICLE_ID>
           
                        <SUPPLIER_AID>000000000013108292</SUPPLIER_AID>
           
                        <BUYER_AID type="String">000000000013108292</BUYER_AID>
           
                        <DESCRIPTION_SHORT>Coolbox 2</DESCRIPTION_SHORT>
           
                        <DESCRIPTION_LONG>Coolbox 2</DESCRIPTION_LONG>
           
                        <MANUFACTURER_INFO>
           
                          <MANUFACTURER_NAME>CompanyName 2</MANUFACTURER_NAME>
           
                          <MANUFACTURER_AID>00000000</MANUFACTURER_AID>
           
                        </MANUFACTURER_INFO>
           
                      </ARTICLE_ID>
           
                      <QUANTITY>10.000</QUANTITY>
           
                      <ORDER_UNIT>PCE</ORDER_UNIT>
           
                      <ARTICLE_PRICE type="net_customer">
           
                        <PRICE_AMOUNT>88.78</PRICE_AMOUNT>
           
                        <PRICE_FLAG type="String">FALSE</PRICE_FLAG>
           
                        <PRICE_LINE_AMOUNT>887.8</PRICE_LINE_AMOUNT>
           
                        <PRICE_QUANTITY>1</PRICE_QUANTITY>
           
                      </ARTICLE_PRICE>
           
                      <DELIVERY_DATE type="fixed">
           
                        <DELIVERY_START_DATE>2013-01-27T00:00:00.0+01:00</DELIVERY_START_DATE>
           
                        <DELIVERY_END_DATE>2013-01-27T00:00:00.0+01:00</DELIVERY_END_DATE>
           
                      </DELIVERY_DATE>
           
                      <PARTIAL_SHIPMENT_ALLOWED>TRUE</PARTIAL_SHIPMENT_ALLOWED>
           
                    </ORDER_ITEM>
           
                  </ORDER_ITEM_LIST>
           
                  <ORDER_SUMMARY>
           
                    <TOTAL_ITEM_NUM>4</TOTAL_ITEM_NUM>
           
                    <TOTAL_AMOUNT>2487.9</TOTAL_AMOUNT>
           
                  </ORDER_SUMMARY>
           
                </ORDER>
            
           And this is my XSL-file that I have created:
            
                
                     <?xml version='1.0' encoding='UTF-8' ?>
                
                     <xsl:stylesheet version='1.0' xmlns:xsl='http://www.w3.org/1999/XSL/
                
                       Transform'>
                
                           <xsl:output version='1.0' encoding='UTF-8' indent='no'
                
                             method='xml' />
                
                           <xsl:template match='/'>
                
                     <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
                
                           <ERRORCODE>0</ERRORCODE> 
                
                           <PRODUCT BUILD="07-18-2011" NAME="Filemaker Pro Advanced" VERSION="11.0v4"/> 
                
                           <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="FileMaker import test.fp7" RECORDS="" TIMEFORMAT="h:mm:ss a"/> 
                
                           <METADATA> 
                
                     <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ORDER_ID" TYPE="TEXT"/> 
                
                     <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ORDER_DATE" TYPE="TEXT"/>
                
                     <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="BUYER_PARTY_NAME" TYPE="TEXT"/> 
                
                           </METADATA> 
                
                           <RESULTSET FOUND="1"> 
                
                     <xsl:for-each select="ORDER">
                
                      <ROW> 
                
                        <COL><DATA><xsl:value-of select="/ORDER/ORDER_HEADER/ORDER_INFO/ORDER_ID"/></DATA></COL>
                
                        <COL><DATA><xsl:value-of select="/ORDER/ORDER_HEADER/ORDER_INFO/ORDER_DATE"/></DATA></COL> 
                
                        <COL><DATA><xsl:value-of select="/ORDER/ORDER_HEADER/ORDER_PARTIES/BUYER_PARTY/PARTY/ADDRESS/NAME"/></DATA></COL> 
                
                      </ROW> 
                
                     </xsl:for-each>
                
                           </RESULTSET> 
                
                         </FMPXMLRESULT> 
                
                       </xsl:template> 
                
                     </xsl:stylesheet> 
           
                 
                Summary: When I try to import the XML file of my customer into my FileMaker system it will only import the record, not the data in this record. If you have any clue why this is hapening, please let me know!
                 
                Thanks upfront!