1 Reply Latest reply on Sep 25, 2014 3:09 PM by FentonJones

    XML export to FileMaker Pro

    SteveWalsh

      Title

      XML export to FileMaker Pro

      Post

      We want to develop an ERP type database for a small business niche in FileMaker Pro.

      We need to update this database with an XML feed from our eCommerce system. What is the best way to map our XML feed from our shopping cart to the data elements in FileMaker Pro so all the customer info, product and product variations, etc. come over accurately?

      Anyone with experience here?

      Here is a small XML feed sample:

      <Orders>
      <Order>
      <OrderId>1637</OrderId>
      <OrderDate>2013-05-01 10:09:24</OrderDate>
      <OrderStatus>on-hold</OrderStatus>
      <BillingFirstName>Steve</BillingFirstName>
      <BillingLastName>Jobs</BillingLastName>
      <BillingFullName>Steve Jobs</BillingFullName>
      <BillingCompany>Apple, Inc.</BillingCompany>
      <BillingAddress1>1 Infinite Loop</BillingAddress1>
      <BillingAddress2/>
      <BillingCity>Cupertino</BillingCity>
      <BillingState>CA</BillingState>
      <BillingPostCode>90143</BillingPostCode>
      <BillingCountry>US</BillingCountry>
      <BillingPhone>414-398-1412</BillingPhone>
      <BillingEmail>steve@apple.com</BillingEmail>
      <ShippingFirstName>Steve</ShippingFirstName>
      <ShippingLastName>Jobs</ShippingLastName>
      <ShippingFullName>Steve Jobs</ShippingFullName>
      <ShippingCompany>Apple, Inc.</ShippingCompany>
      <ShippingAddress1>1 Infinite Loop</ShippingAddress1>
      <ShippingAddress2/>
      <ShippingCity>Cupertino</ShippingCity>
      <ShippingState>CA</ShippingState>
      <ShippingPostCode>90143</ShippingPostCode>
      <ShippingCountry>US</ShippingCountry>
      <ShippingMethodId>flat_rate</ShippingMethodId>
      <ShippingMethod>Flat Rate</ShippingMethod>
      <PaymentMethodId>cheque</PaymentMethodId>
      <PaymentMethod>Cheque Payment</PaymentMethod>
      <OrderDiscountTotal>0.00</OrderDiscountTotal>
      <CartDiscountTotal>0.00</CartDiscountTotal>
      <DiscountTotal>0.00</DiscountTotal>
      <ShippingTotal>1.00</ShippingTotal>
      <ShippingTaxTotal>0.00</ShippingTaxTotal>
      <OrderTotal>30.00</OrderTotal>
      <TaxTotal>0.00</TaxTotal>
      <CompletedDate>2013-05-01 10:09:24</CompletedDate>
      <CustomerNote/>
      <CustomerId>0</CustomerId>
      <OrderLineItems>
      <SKU>W007</SKU>
      <Quantity>1</Quantity>
      <Price>29</Price>
      <LineTotal>29</LineTotal>
      <Meta/>
      </OrderLineItems>
      </Order>
      </Orders>

        • 1. Re: XML export to FileMaker Pro
          FentonJones

          OK, there are 2 Imports, 2 tables, needed for the above. The first is for the Order, the 2nd is for the OrderLines. Each would be an Import XML, but there would be 2 different XSL files [ well, maybe it could be done with 1, but I prever 2, as it's much simpler to read {and the Order one is going to be long} ].

          Order XSL: I've only done a few, but you can see what is needed; 
          1. Tell it what the FileMaker FIELD NAME is {and type}.
          2. Enter the exact name, as your Order XML file uses.

          <?xml version="1.0" encoding="utf-8" ?>
          <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
              <xsl:output method="xml" encoding="utf-8" indent="yes" />    
              <xsl:template match="Orders">
          <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
              <ERRORCODE>0</ERRORCODE>
              <PRODUCT BUILD="" NAME="FileMaker Pro XML Import" VERSION="6.0v1" />
              <DATABASE DATEFORMAT="yyyy.MM.dd" LAYOUT="" NAME="" RECORDS="" TIMEFORMAT="k:mm:ss" />
              <METADATA>
                  <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="OrderID" TYPE="TEXT" />
                  <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Date_txt" TYPE="TEXT" />
                  <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Status_order" TYPE="TEXT" />
                  <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Bill_FirstName" TYPE="TEXT" />
                  <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Bill_LastName" TYPE="TEXT" />
                  <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Bill_Company" TYPE="TEXT" />
              </METADATA>
              <RESULTSET FOUND="">
                  <xsl:for-each select="Order" >
                  <ROW MODID="" RECORDID="" >
                      <COL><DATA><xsl:value-of select="OrderId" /></DATA></COL>
                      <COL><DATA><xsl:value-of select="OrderDate" /></DATA></COL>
                      <COL><DATA><xsl:value-of select="OrderStatus" /></DATA></COL>
                      <COL><DATA><xsl:value-of select="BillingFirstName" /></DATA></COL>
                      <COL><DATA><xsl:value-of select="BillingLastName" /></DATA></COL>
                      <COL><DATA><xsl:value-of select="BillingCompany" /></DATA></COL>
                  </ROW>
                  </xsl:for-each>
              </RESULTSET>
          </FMPXMLRESULT>
              </xsl:template>
          </xsl:stylesheet>


          OrderLine XSL: It is also looking thru the Order (in order to get the OrderId),* then looking thru it into the "OrderLineItems" to get the data.

          <?xml version="1.0" encoding="utf-8" ?>
          <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
              <xsl:output method="xml" encoding="utf-8" indent="yes" />    
              <xsl:template match="Orders">
          <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
              <ERRORCODE>0</ERRORCODE>
              <PRODUCT BUILD="" NAME="FileMaker Pro XML Import" VERSION="6.0v1" />
              <DATABASE DATEFORMAT="yyyy.MM.dd" LAYOUT="" NAME="" RECORDS="" TIMEFORMAT="k:mm:ss" />
              <METADATA>
                  <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="OrderID" TYPE="TEXT" />
                  <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="SKU" TYPE="TEXT" />
                  <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Quantity" TYPE="NUMBER" />
                  <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Price" TYPE="NUMBER" />
                  <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="LineTotal" TYPE="NUMBER" />
              </METADATA>
              <RESULTSET FOUND="">
                  <xsl:for-each select="Order" >
                  <ROW MODID="" RECORDID="" >
                      <COL><DATA><xsl:value-of select="OrderId" /></DATA></COL>
                      <COL><DATA><xsl:value-of select="OrderLineItems/SKU" /></DATA></COL>
                      <COL><DATA><xsl:value-of select="OrderLineItems/Quantity" /></DATA></COL>
                      <COL><DATA><xsl:value-of select="OrderLineItems/Price" /></DATA></COL>
                      <COL><DATA><xsl:value-of select="OrderLineItems/LineTotal" /></DATA></COL>
                  </ROW>
                  </xsl:for-each>
              </RESULTSET>
          </FMPXMLRESULT>
              </xsl:template>
          </xsl:stylesheet>


          *There may be another way to go back and get the OrderId; but I cannot remember exactly how. I duped your Order data, and changed the OrderId, and the above method brought it the correct one (as far as I can see).

          Another question is WHERE to put the XSL file(s). If everyone had it in an exact place in their computer, that works. But where? One place is within the FileMaker application folder; which can be accessed via a calculation using (similar to): 
          "file:" & Get ( FileMakerPath ) & "folder (if any)/Orders.xsl"
          [another for "OrderLine.xsl" file, same path, but different name]
          OR
          You can put the file(s) on a web site, which all have access to (they don't have to "see" it however).
          OR
          You could Export Field Contents of the file(s) to their Temporary Items folder (when needed)
          I find Export Field Contents of the XSL some trouble, as it outputs at UTF-16le, and you're XSL must match (notice both these are UPF-8). But it can be done; the first line of the XSL files needs to be: <?xml version="1.0" encoding="utf-16le" ?>

          You can tell the Import what the path is to the XSL, but it also needs one path it can rely on, when you're setting up the a "specify import order", as it cannot see a Variable path, i.e., $path. You can however have more than 1 path (as a return separated list of paths); it will use the 1st one that matches.