Barry685 Posted October 16, 2013 Share Posted October 16, 2013 I would like to import an XML file into a Filemaker table. Filemaker is requesting a XSL stylesheet. How do I create one? Link to comment Share on other sites More sharing options...
comment Posted October 16, 2013 Share Posted October 16, 2013 You need to write one - see: http://www.w3schools.com/xsl/ If you post an example of your XML file and explain what do you want to import from there, we might be able to help. Link to comment Share on other sites More sharing options...
Barry685 Posted October 16, 2013 Author Share Posted October 16, 2013 <?xml version="1.0"?> <Client format="xmlstd" proselectversion="12.2.3"> <Client_ID></Client_ID> <Street></Street> <Street2></Street2> <City></City> <Email_Address></Email_Address> <First_Name>Group 1</First_Name> <Last_Name></Last_Name> <Home_Phone></Home_Phone> <Work_Phone></Work_Phone> <Cell_Phone></Cell_Phone> <State></State> <Zip_Code></Zip_Code> <Client_Notes></Client_Notes> <Album_Path>W:2361223612_Pessi_Grossman.psa</Album_Path> <Order> <Date>10/16/2013</Date> <DateSQL>2013-10-16</DateSQL> <Album_ID>23612_Pessi_Grossman-1</Album_ID> <Order_Notes></Order_Notes> <Total_Amount>1556.69</Total_Amount> <Shipping_Name></Shipping_Name> <Shipping_Street></Shipping_Street> <Shipping_City></Shipping_City> <Shipping_Date></Shipping_Date> <Shipping_DateSQL></Shipping_DateSQL> <Shipping_Phone></Shipping_Phone> <Shipping_State></Shipping_State> <Shipping_Zip_Code></Shipping_Zip_Code> <Ordered_Items> <Ordered_Item> <ItemType>Print</ItemType> <Total_Discount>0</Total_Discount> <Description>8 x 10 in Economy</Description> <Product_Name></Product_Name> <Product_Code>ECO-810</Product_Code> <Extended_Price>39.95</Extended_Price> <ID>1</ID> <Price>39.95</Price> <Quantity>1</Quantity> <Item_Notes></Item_Notes> <Images> <Ordered_Image>Print_1.jpg</Ordered_Image> <Original_Image>W:2361223612-002.jpg</Original_Image> <Image_Name>23612-002.jpg</Image_Name> </Images> <Tax>3.55</Tax> <Tax1 label="Sales Tax" rate="8.875">3.55</Tax1> <Tax2 label="" rate="0">0</Tax2> <ProductLineName>Portraits</ProductLineName> </Ordered_Item> <Ordered_Item> <ItemType>Print</ItemType> <Total_Discount>0</Total_Discount> <Description>5 x 7 in Economy</Description> <Product_Name></Product_Name> <Product_Code>ECO-57</Product_Code> <Extended_Price>29.95</Extended_Price> <ID>2</ID> <Price>29.95</Price> <Quantity>1</Quantity> <Item_Notes></Item_Notes> <Images> <Ordered_Image>Print_2.jpg</Ordered_Image> <Original_Image>W:2361223612-008.jpg</Original_Image> <Image_Name>23612-008.jpg</Image_Name> </Images> <Tax>2.66</Tax> <Tax1 label="Sales Tax" rate="8.875">2.66</Tax1> <Tax2 label="" rate="0">0</Tax2> <ProductLineName>Portraits</ProductLineName> </Ordered_Item> <Ordered_Item> <ItemType>Print</ItemType> <Total_Discount>0</Total_Discount> <Description>5 x 7 in Economy</Description> <Product_Name></Product_Name> <Product_Code>ECO-57</Product_Code> <Extended_Price>29.95</Extended_Price> <ID>3</ID> <Price>29.95</Price> <Quantity>1</Quantity> <Item_Notes></Item_Notes> <Images> <Ordered_Image>Print_3.jpg</Ordered_Image> <Original_Image>W:2361223612-010.jpg</Original_Image> <Image_Name>23612-010.jpg</Image_Name> </Images> <Tax>2.66</Tax> <Tax1 label="Sales Tax" rate="8.875">2.66</Tax1> <Tax2 label="" rate="0">0</Tax2> <ProductLineName>Portraits</ProductLineName> </Ordered_Item> <Ordered_Item> <ItemType>Print</ItemType> <Total_Discount>0</Total_Discount> <Description>5 x 7 in Economy</Description> <Product_Name></Product_Name> <Product_Code>ECO-57</Product_Code> <Extended_Price>29.95</Extended_Price> <ID>4</ID> <Price>29.95</Price> <Quantity>1</Quantity> <Item_Notes></Item_Notes> <Images> <Ordered_Image>Print_4.jpg</Ordered_Image> <Original_Image>W:2361223612-012.jpg</Original_Image> <Image_Name>23612-012.jpg</Image_Name> </Images> <Tax>2.66</Tax> <Tax1 label="Sales Tax" rate="8.875">2.66</Tax1> <Tax2 label="" rate="0">0</Tax2> <ProductLineName>Portraits</ProductLineName> </Ordered_Item> <Ordered_Item> <ItemType>Print</ItemType> <Total_Discount>0</Total_Discount> <Description>24 x 30 in Heritage</Description> <Product_Name></Product_Name> <Product_Code>HER-2430</Product_Code> <Extended_Price>650.00</Extended_Price> <ID>5</ID> <Price>650.00</Price> <Quantity>1</Quantity> <Item_Notes></Item_Notes> <Images> <Ordered_Image>Print_5.jpg</Ordered_Image> <Original_Image>W:2361223612-025.jpg</Original_Image> <Image_Name>23612-025.jpg</Image_Name> </Images> <Tax>57.69</Tax> <Tax1 label="Sales Tax" rate="8.875">57.69</Tax1> <Tax2 label="" rate="0">0</Tax2> <ProductLineName>Portraits</ProductLineName> </Ordered_Item> <Ordered_Item> <ItemType>Print</ItemType> <Total_Discount>0</Total_Discount> <Description>24 x 30 in Heritage</Description> <Product_Name></Product_Name> <Product_Code>HER-2430</Product_Code> <Extended_Price>650.00</Extended_Price> <ID>6</ID> <Price>650.00</Price> <Quantity>1</Quantity> <Item_Notes></Item_Notes> <Images> <Ordered_Image>Print_6.jpg</Ordered_Image> <Original_Image>W:2361223612-031.jpg</Original_Image> <Image_Name>23612-031.jpg</Image_Name> </Images> <Tax>57.69</Tax> <Tax1 label="Sales Tax" rate="8.875">57.69</Tax1> <Tax2 label="" rate="0">0</Tax2> <ProductLineName>Portraits</ProductLineName> </Ordered_Item> </Ordered_Items> <Refunds> </Refunds> </Order> </Client> I only need the order item information. Im new to this I hope I posted this correctly. Link to comment Share on other sites More sharing options...
comment Posted October 16, 2013 Share Posted October 16, 2013 I only need the order item information. The attached style sheet will import 3 "fields" from each Ordered_Item: Product_Code, Price, and Quantity. To add another column to the import, you need to [a] add a <FIELD> element to the <METADATA> section and add another <COL> to the <ROW>. It should be pretty self-explanatory once you have a look at it. ImportOrderitems.xsl.zip Link to comment Share on other sites More sharing options...
Barry685 Posted October 16, 2013 Author Share Posted October 16, 2013 Thanks ALOT. Will study it and see if I can make it work, Link to comment Share on other sites More sharing options...
Barry685 Posted October 16, 2013 Author Share Posted October 16, 2013 I tried to add one more field and did get a parsing error when trying to do the import. The only other field that I need is the "Image_Name" field in the images section. Perhaps you can add that to the xsl file. Thanks Link to comment Share on other sites More sharing options...
comment Posted October 16, 2013 Share Posted October 16, 2013 I think I can guess what you'll say when you see this... ImportOrderitems.xsl.zip Note that we are assuming there's only one image per item. Link to comment Share on other sites More sharing options...
Barry685 Posted October 17, 2013 Author Share Posted October 17, 2013 This is great. I will test it with more data tomorrow. I think this is all I need to extract. What software did you use to accomplish this with?. I know nothing about xml. Link to comment Share on other sites More sharing options...
comment Posted October 17, 2013 Share Posted October 17, 2013 It's hand-written in a text editor. I have a template for this, so I only needed to modify a couple of things. Link to comment Share on other sites More sharing options...
Newbies Luze Posted December 19, 2013 Newbies Share Posted December 19, 2013 Hi comment, My xml is look quite difference from Barry685. Could you please help to explain where should i change to import this file. Regards, Luz <xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882' xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882' xmlns:rs='urn:schemas-microsoft-com:rowset' xmlns:z='#RowsetSchema'><s:Schema id='RowsetSchema'> <s:ElementType name='row' content='eltOnly' rs:updatable='true'> <s:AttributeType name='MemberId' rs:number='1' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='MemberId' rs:keycolumn='true' rs:autoincrement='true'> <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true'/> </s:AttributeType> <s:AttributeType name='Lastname' rs:number='2' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='Lastname'> <s:datatype dt:type='string' dt:maxLength='25'/> </s:AttributeType> <s:AttributeType name='Firstname' rs:number='3' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='Firstname'> <s:datatype dt:type='string' dt:maxLength='15'/> </s:AttributeType> <s:AttributeType name='Init' rs:number='4' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='Comment'> <s:datatype dt:type='string' dt:maxLength='1'/> </s:AttributeType> <s:AttributeType name='IcsAlias' rs:number='5' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='ICS'> <s:datatype dt:type='string' dt:maxLength='16'/> </s:AttributeType> <s:AttributeType name='RefNo' rs:number='6' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='RefNo'> <s:datatype dt:type='string' dt:maxLength='10'/> </s:AttributeType> <s:AttributeType name='InActive' rs:number='7' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='InActive'> <s:datatype dt:type='boolean' dt:maxLength='2' rs:fixedlength='true'/> </s:AttributeType> <s:AttributeType name='Female' rs:number='8' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='Female'> <s:datatype dt:type='boolean' dt:maxLength='2' rs:fixedlength='true'/> </s:AttributeType> <s:AttributeType name='PrintLabel' rs:number='9' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='PrintLabel'> <s:datatype dt:type='boolean' dt:maxLength='2' rs:fixedlength='true'/> </s:AttributeType> <s:AttributeType name='RegionId' rs:number='10' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='TypeRegionId'> <s:datatype dt:type='string' dt:maxLength='3'/> </s:AttributeType> <s:AttributeType name='DOB' rs:number='11' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='DOB'> <s:datatype dt:type='dateTime' rs:dbtype='variantdate' dt:maxLength='16' rs:fixedlength='true'/> </s:AttributeType> <s:AttributeType name='Address1' rs:number='12' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='Address1'> <s:datatype dt:type='string' dt:maxLength='40'/> </s:AttributeType> <s:AttributeType name='Address2' rs:number='13' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='Address2'> <s:datatype dt:type='string' dt:maxLength='40'/> </s:AttributeType> <s:AttributeType name='City' rs:number='14' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='City'> <s:datatype dt:type='string' dt:maxLength='20'/> </s:AttributeType> <s:AttributeType name='Province' rs:number='15' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='Province'> <s:datatype dt:type='string' dt:maxLength='24'/> </s:AttributeType> <s:AttributeType name='CountryId' rs:number='16' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='TypeCountryId'> <s:datatype dt:type='string' dt:maxLength='3'/> </s:AttributeType> <s:AttributeType name='PostCode' rs:number='17' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='PostCode'> <s:datatype dt:type='string' dt:maxLength='10'/> </s:AttributeType> <s:AttributeType name='PhoneHome' rs:number='18' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='PhoneHome'> <s:datatype dt:type='string' dt:maxLength='20'/> </s:AttributeType> <s:AttributeType name='PhoneAlt' rs:number='19' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='PhoneAlternate'> <s:datatype dt:type='string' dt:maxLength='20'/> </s:AttributeType> <s:AttributeType name='PhoneFax' rs:number='20' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='PhoneFax'> <s:datatype dt:type='string' dt:maxLength='20'/> </s:AttributeType> <s:AttributeType name='Email' rs:number='21' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='Email'> <s:datatype dt:type='string' dt:maxLength='36'/> </s:AttributeType> <s:AttributeType name='ClassId' rs:number='22' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='TypeScoreClassId'> <s:datatype dt:type='string' dt:maxLength='1'/> </s:AttributeType> <s:AttributeType name='DfltDivId' rs:number='23' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='DfltDivisionId'> <s:datatype dt:type='ui1' dt:maxLength='1' rs:precision='3' rs:fixedlength='true'/> </s:AttributeType> <s:AttributeType name='DfltCatId' rs:number='24' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='DfltNonTeamCategoryId'> <s:datatype dt:type='ui1' dt:maxLength='1' rs:precision='3' rs:fixedlength='true'/> </s:AttributeType> <s:AttributeType name='DfltTagId' rs:number='25' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='DfltTagId'> <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true'/> </s:AttributeType> <s:AttributeType name='QualId' rs:number='26' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='TypeQualificationId'> <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true'/> </s:AttributeType> <s:AttributeType name='Register' rs:number='27' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='Register'> <s:datatype dt:type='boolean' dt:maxLength='2' rs:fixedlength='true'/> </s:AttributeType> <s:AttributeType name='Note' rs:number='28' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true' rs:basetable='tblMember' rs:basecolumn='Note'> <s:datatype dt:type='string' dt:maxLength='536870910' rs:long='true'/> </s:AttributeType> <s:extends type='rs:rowbase'/> </s:ElementType></s:Schema><rs:data> <z:row MemberId='1' Lastname='Cccc' Firstname='Ch0000' RefNo='' InActive='False' Female='True' PrintLabel='False' RegionId='AUS' DOB='1953-07-30T00:00:00' PhoneHome='+61 88888 4167' PhoneAlt='+61 8888 368' Email='[email protected]' ClassId='U' DfltDivId='2' DfltCatId='1' QualId='0' Register='False'/> <z:row MemberId='2' Lastname='Cttttt' Firstname='Vvvv' RefNo='' InActive='False' Female='True' PrintLabel='False' RegionId='AUS' DOB='1986-10-17T00:00:00' PhoneHome='' PhoneAlt='+677889999999254' Email='[email protected]' ClassId='U' DfltDivId='4' DfltCatId='1' QualId='0' Register='False'/> <z:row MemberId='3' Lastname='Cajjjjj' Firstname='Puuuu' RefNo='' InActive='False' Female='False' PrintLabel='False' RegionId='AUS' DOB='1953-05-01T00:00:00' PhoneHome='+69999997' PhoneAlt='+6177888888 747' Email='[email protected]' ClassId='U' DfltDivId='2' DfltCatId='3' DfltTagId='0' QualId='0' Register='False'/> <z:row MemberId='4' Lastname='Cjjjjj' Firstname='Jjjjj' RefNo='' InActive='False' Female='True' PrintLabel='False' RegionId='AUS' DOB='1978-11-09T00:00:00' PhoneHome='' PhoneAlt='+61 77777777' Email='[email protected]' ClassId='U' DfltDivId='4' DfltCatId='1' QualId='0' Register='False'/></rs:data></xml> Link to comment Share on other sites More sharing options...
Newbies Luze Posted December 19, 2013 Newbies Share Posted December 19, 2013 My understand is that the attribute part are the field name but it looks quite difference from Barry685. Luze Link to comment Share on other sites More sharing options...
comment Posted December 19, 2013 Share Posted December 19, 2013 Well, the "X" in XML stands for "eXtensible", so there is a large variety of XML schemas in use; Filemaker itself has no less than three of its own. You need to tailor the XSLT to fit the incoming schema on the input side and Filemaker's FMPXMLRESULT grammar on the output side. See if this can get you started: it too imports 3 fields - see post #4 for how to import more. <?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882' xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882' xmlns:rs='urn:schemas-microsoft-com:rowset' xmlns:z='#RowsetSchema' exclude-result-prefixes="s dt rs z"> <xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/> <xsl:template match="/"> <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"> <ERRORCODE>0</ERRORCODE> <PRODUCT BUILD="" NAME="" VERSION=""/> <DATABASE DATEFORMAT="" LAYOUT="" NAME="" RECORDS="" TIMEFORMAT=""/> <METADATA> <FIELD NAME="MemberId" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/> <FIELD NAME="Lastname" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/> <FIELD NAME="Firstname" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/> </METADATA> <RESULTSET FOUND=""> <xsl:for-each select="xml/rs:data/z:row"> <ROW MODID="" RECORDID=""> <COL><DATA><xsl:value-of select="@MemberId"/></DATA></COL> <COL><DATA><xsl:value-of select="@Lastname"/></DATA></COL> <COL><DATA><xsl:value-of select="@Firstname"/></DATA></COL> </ROW> </xsl:for-each> </RESULTSET> </FMPXMLRESULT> </xsl:template> </xsl:stylesheet> --- P.S. I hope those aren't real people's names and addresses. If they are, please remove them. Link to comment Share on other sites More sharing options...
Newbies Luze Posted December 19, 2013 Newbies Share Posted December 19, 2013 Hi comment, Thanks for the sample. Sorry I overlooked the data, I've removed it. Thanks for remind me. I'll try it when I get home. By the way, can this xsl file use for export to XML in the same format ? Regards, Luze Link to comment Share on other sites More sharing options...
Newbies Luze Posted December 19, 2013 Newbies Share Posted December 19, 2013 Hi comment, The format from your sample is work beautifully, thank you very much again. Luze Link to comment Share on other sites More sharing options...
comment Posted December 23, 2013 Share Posted December 23, 2013 By the way, can this xsl file use for export to XML in the same format ? No, it needs to be completely different. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 3798 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now