11 Replies Latest reply on Mar 31, 2010 12:07 PM by FentonJones

    XML import format help!

    steelbut

      Title

      XML import format help!

      Post

      I am trying to import xml data from an engineering program.  Can I get help on what it will take to parse xml below so they will import?  It is about 500+ lines usually if that makes a difference?  Below I pasted 2 different formats of the smae xml where I tried to make it parse but it would not.

       

      Which one of the below examples are better or should I have the xml generator changed to another format?

       

      Also when the engineering program makes the xml it sometimes makes more them one value for the same line (discription).

       

      <?xml version="1.0" encoding="UTF-8" ?>
      <Hibbs_Fire>
      <ID>F101</ID>
      <Descript>customer name</Descript>
      <Value1>Customer Name</Value1>
      </Hibbs_Fire>
      <Hibbs_Fire>
      <ID>F102</ID>
      <Descript>customer address</Descript>
      <Value1>Customer Address</Value1>
      <Value2>Address Continued</Value2>
      </Hibbs_Fire>
      <Hibbs_Fire>
      <ID>F103</ID>
      <Descript>job id</Descript>
      <Value1>Hibbs_Fire</Value1>
      </Hibbs_Fire>
      <Hibbs_Fire>
      <ID>F104</ID>
      <Descript>project</Descript>
      <Value1>Hibbs_Fire</Value1>
      </Hibbs_Fire>
      <Hibbs_Fire>
      <ID>F105</ID>
      <Descript>job site</Descript>
      <Value1>Project Street</Value1>
      <Value2>72029</Value2>
      </Hibbs_Fire>
      <Hibbs_Fire>
      <ID>F201</ID>
      <Descript>building width</Descript>
      <Value1>30.5</Value1>
      </Hibbs_Fire>
      <Hibbs_Fire>
      <ID>F202</ID>
      <Descript>building length</Descript>
      <Value1>40</Value1>
      </Hibbs_Fire>
      <Hibbs_Fire>
      <ID>F203</ID>
      <Descript>back side eave height</Descript>
      <Value1>16</Value1>
      </Hibbs_Fire>

       

       

      the second version of the same xml (in part)

       

      <?xml version="1.0" encoding="UTF-8" ?>
      <Hibbs_Fire>
      <F1011 desc="customer name">Customer Name</F1011>
      <F1021 desc="customer address">Customer Address</F1021>
      <F1022 desc="customer address">Address Continued</F1022>
      <F1031 desc="job id">Hibbs_Fire</F1031>
      <F1041 desc="project">Hibbs_Fire</F1041>
      <F1051 desc="job site">Project Street</F1051>
      <F1052 desc="job site">72029</F1052>
      <F2011 desc="building width">30.5</F2011>
      <F2021 desc="building length">40</F2021>
      <F2031 desc="back side eave height">16</F2031>
      <F2041 desc="front side eave height">16</F2041>
      <F2051 desc="back side roof slope">1.0000:12</F2051>
      <F2061 desc="front side roof slope">1.0000:12</F2061>
      <F2071 desc="sidewall bays"> 2 at 20</F2071>
      <F2081 desc="left endwall bays"> 2 at 15.25</F2081>
      <F2091 desc="right endwall bays"> 2 at 15.25</F2091>
      <F2101 desc="enter yes if sidewall eave heights are different"></F2101>
      <F2111 desc="peak offset">15.25</F2111>
      <F2901 desc="Hot Rolled Structural Code">AISC89</F2901>
      <F2911 desc="Cold Form Structural Code">AISI89</F2911>
      <F3001 desc="building design lifetime in years"></F3001>
      <F3011 desc="dead load">2.000</F3011>
      <F3021 desc="live load">20.00</F3021>
      <F3031 desc="snow load">10</F3031>
      <F3041 desc="enter Yes if load reduction is used">No</F3041>
      <F3051 desc="collateral load">0.5</F3051>
      <F3061 desc="wind code">IBC 06</F3061>
      <F3071 desc="wind speed/pressure">90</F3071>
      <F3081 desc="closure">Closed</F3081>
      <F3091 desc="wind exposure">B</F3091>
      <F3101 desc="wind importance"> 1.00</F3101>
      <F3111 desc="seismic zone">B</F3111>
      <F3121 desc="seismic V">0</F3121>
      <F3131 desc="seismic coef Aa"> 0.530</F3131>
      <F3141 desc="seismic Zv"> 0.00</F3141>
      <F3151 desc="seismic importance"> 1.00</F3151>
      <F3161 desc="wind10">0</F3161>
      <F3171 desc="wind30 load">0</F3171>
      <F3181 desc="left endwall live load">20</F3181>
      <F3191 desc="right endwall live load">20</F3191>
      <F3201 desc="rigid frame live load">20</F3201>
      <F3221 desc="seismic Sa(2.0">1</F3221>
      <F3231 desc="Temperature change">0 deg F</F3231>
      <F3501 desc="Deflect Limit: EW_Col">180</F3501>
      <F3511 desc="Deflect Limit: EW_Raf_Live">180</F3511>
      <F3521 desc="Deflect Limit: EW_Raf_Wind">180</F3521>
      <F3531 desc="Deflect Limit: Wall_Girt">90</F3531>
      <F3541 desc="Deflect Limit: Purl_Live">180</F3541>
      <F3551 desc="Deflect Limit: Purl_WInd">120</F3551>
      <F3561 desc="Deflect Limit: Wall_Panel">90</F3561>
      <F3571 desc="Deflect Limit: Roof_Panel_Live">180</F3571>
      <F3581 desc="Deflect Limit: Roof_Panel_Wind">120</F3581>
      <F3591 desc="Deflect Limit: RF_Horizontal">60</F3591>
      <F3601 desc="Deflect Limit: RF_Vertical">180</F3601>
      <F3611 desc="Deflect Limit: Wind_Bent">60</F3611>
      <F3621 desc="Deflect Limit: RF_Crane">0</F3621>
      <F3631 desc="Deflect Limit: RF_Seis">50</F3631>
      <F3641 desc="Deflect Limit: Wind_Bent_Seis">50</F3641>
      <F3911 desc="lew girt depth"> 8.00</F3911>
      <F3921 desc="fsw girt depth"> 8.00</F3921>
      <F3931 desc="rew girt depth"> 8.00</F3931>
      <F3941 desc="bsw girt depth"> 8.00</F3941>
      <F3951 desc="purlin depth (surface 1"> 8.00</F3951>
      <F4011 desc="left endwall frame type">Bearing</F4011>
      <F4021 desc="left endwall framing"> 15.25 15.25</F4021>
      <F4031 desc="left endwall girt type">Flush</F4031>
      <F4041 desc="left endwall girt locations"> 7.500 11.42</F4041>
      <F4051 desc="right endwall frame type">Bearing</F4051>
      <F4061 desc="right endwall framing"> 15.25 15.25</F4061>
      <F4071 desc="right endwall girt type">Flush</F4071>
      <F4081 desc="right endwall girt locations"> 7.500 11.42</F4081>
      <F4091 desc="interior framing"> 1 rigid frame</F4091>
      <F4101 desc="front sidewall girt type">Flush</F4101>
      <F4111 desc="front sidewall girt locations"> 7.500 11.42</F4111>
      <F4121 desc="back sidewall girt type">Flush</F4121>
      <F4131 desc="back sidewall girt locations"> 7.500 11.42</F4131>
      <F4141 desc="roof framing">Bypass purlins at 4.657 ft spacing.</F4141>
      <F4151 desc="roof wind framing">Cable Diagonal Bracing</F4151>
      <F4161 desc="left endwall wind framing">Cable Diagonal Bracing to Frame</F4161>
      <F4171 desc="front sidewall wind framing">Cable Diagonal Bracing</F4171>
      <F4181 desc="right endwall wind framing">Cable Diagonal Bracing</F4181>
      <F4191 desc="back sidewall wind framing">Cable Diagonal Bracing</F4191>
      <F4201 desc="rigid frame 1 - type">RF-</F4201>
      <F4211 desc="rigid frame 2 - type"></F4211>
      <F4221 desc="rigid frame 3 - type"></F4221>
      <F4231 desc="rigid frame 4 - type"></F4231>
      <F4241 desc="rigid frame 5 - type"></F4241>
      <F4251 desc="rigid frame 6 - type"></F4251>

       

      Thanks!!  Larry

































































        • 1. Re: XML import format help!
          grinch
            

            Unless your engineering program can export in FMPXMLRESULT Grammar, which is unlikely but hey every once in a while one gets lucky, you are going to need is a XSLT.  A XSLT translates your XML into a format FileMaker can read.  They are not hard to write.  There is a very good article located at:

           

          http://www.goya.com.au/node/105

           

          This is a three part article that gives you a lot of insight into writing a XSLT for FileMaker.  It starts kind of dry but by the end of part 2 it really gets to the point.  For more info on XSLT in general you can check out W3 Schools tutorial

           

          Looking at your XML, I would use the first example.  It will be easier to work with.  Working with attributes is slightly more involved.  It looks to me that your data would all go into 1 record.  The Descript tag will be the field name, and the Value tag will be the data for the field.  Read the article and let us know if you have any questions.

           

          Chris

           

          • 2. Re: XML import format help!
            dogger27

            Hi there - That's a very helpful post, thank you! I have many XML files that I will need to import into filemaker. They are all formatted like this. I've included one "<clip>" but there were about 10 in the actual file. Any pointers on creating an XSLT? I would very much appreciate your advice! Kind Regards.

            Paul

             

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

            <?xml-stylesheet type="text/xsl" href="Standard.xsl"?>

            <ClipfinderData>

                <Metadata>

                    <StandardFields>

                        <Fieldname>ClipFilePath</Fieldname>

                        <Fieldname>ClipName</Fieldname>

                        <Fieldname>ClipRenderName</Fieldname>

                        <Fieldname>ClipCustomName</Fieldname>

                        <Fieldname>ClipDateTime</Fieldname>

                        <Fieldname>ClipDuration</Fieldname>

                        <Fieldname>ClipWidth</Fieldname>

                        <Fieldname>ClipHeight</Fieldname>

                        <Fieldname>ClipFrameRate</Fieldname>

                        <Fieldname>ClipNumFrames</Fieldname>

                        <Fieldname>ClipISO</Fieldname>

                        <Fieldname>ClipShutter</Fieldname>

                        <Fieldname>ClipFirmwareVersion</Fieldname>

                        <Fieldname>ClipAudioChannels</Fieldname>

                        <Fieldname>ClipTC</Fieldname>

                        <Fieldname>ClipTCOut</Fieldname>

                        <Fieldname>ClipEC</Fieldname>

                        <Fieldname>ClipECOut</Fieldname>

                        <Fieldname>ClipInFrame</Fieldname>

                        <Fieldname>ClipOutFrame</Fieldname>

                        <Fieldname>ClipComment</Fieldname>

                        <Fieldname>ClipGoodTake</Fieldname>

                        <Fieldname>ClipPosterFrame</Fieldname>

                    </StandardFields>

                    <CustomFields>

                        <Fieldname>Scene</Fieldname>

                        <Fieldname>Take</Fieldname>

                    </CustomFields>

             

                <Clip>

                    <ClipFilePath>/Volumes/REDRAID 1/SanctuarySeason2/Episode301/Day1/lunch/RED/B008_0315CP/B008_0315CP.RDM/B008_C006_0315DH.RDC</ClipFilePath>

                    <ClipName>B008_C006_0315DH</ClipName>

                    <ClipRenderName>B008_C006_0315DH</ClipRenderName>

                    <ClipDateTime>15/03/10 7:20 PM</ClipDateTime>

                    <ClipDuration>00:01:30:21</ClipDuration>

                    <ClipWidth>4096</ClipWidth>

                    <ClipHeight>2304</ClipHeight>

                    <ClipFrameRate>23.97602</ClipFrameRate>

                    <ClipNumFrames>2179</ClipNumFrames>

                    <ClipISO>640</ClipISO>

                    <ClipShutter>1/48</ClipShutter>

                    <ClipFirmwareVersion>30.4.1#30</ClipFirmwareVersion>

                    <ClipAudioChannels>A1A2</ClipAudioChannels>

                    <ClipTC>12:18:34:00</ClipTC>

                    <ClipTCOut>12:20:04:18</ClipTCOut>

                    <ClipEC>01:07:56:22</ClipEC>

                    <ClipECOut>01:09:27:16</ClipECOut>

                    <ClipInFrame>0</ClipInFrame>

                    <ClipOutFrame>2179</ClipOutFrame>

                    <CustomField name="Scene">61a</CustomField>

                    <CustomField name="Take">2</CustomField>

                    <Thumbnail>

                        <ImageRef>thumbnails/B008_C006_0315DH-000000.jpg</ImageRef>

                        <TC>12:18:34:00</TC>

                        <EC>01:07:56:22</EC>

                        <Frame>0</Frame>

                    </Thumbnail>

                </Clip>

            </ClipfinderData>

            • 3. Re: XML import format help!
              FentonJones

               

              <?xml version="1.0" encoding="utf-8"?>
              <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
              <xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>
              <xsl:template match="/">
              <ERRORCODE>0</ERRORCODE>
              <PRODUCT BUILD="" NAME="" VERSION=""/>
              <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="" RECORDS="" TIMEFORMAT="h:mm:ss a"/>
              <METADATA>
              <!-- Use their Metadata to build our METADATA :-] All Text; otherwise manually construct -->
              <xsl:for-each select="ClipfinderData/Metadata/*/Fieldname">
              <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="{.}" TYPE="TEXT"/>
              </xsl:for-each>
              <!-- Add Thumbnail fields, as they ain't in their Metadata -->
              <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Thumb_ImageRef" TYPE="TEXT"/>
              </METADATA>
              <RESULTSET FOUND="{count(/ClipfinderData/Clip)}">
              <xsl:for-each select="/ClipfinderData/Clip">
              <!-- FileMaker record -->
              <ROW>
              <xsl:attribute name="MODID">0</xsl:attribute>
              <xsl:attribute name="RECORDID"><xsl:value-of select="position()"/></xsl:attribute>
              <!-- FileMaker field -->
              <COL>
              <DATA>
              <xsl:value-of select="ClipFilePath"/>
              </DATA>
              </COL>
              <!-- Next field -->
              <COL>
              <DATA>
              <xsl:value-of select="ClipName"/>
              </DATA>
              </COL>
              <!-- Skip down to Thumbnail, which has children  -->
              <COL>
              <DATA>
              <xsl:value-of select="Thumbnail/ImageRef"/>
              </DATA>
              </COL>
              <!-- Etc. for every field -->
              </ROW>
              </xsl:for-each>
              </RESULTSET>
              </FMPXMLRESULT>
              </xsl:template>
              </xsl:stylesheet>
              • 4. Re: XML import format help!
                dogger27

                Fenton - Thank you so much for taking the time to make that.. It works great and it's so super cool of you to help me.

                I have few questions, but only if you have time:

                 

                what does that "position()" function do?

                why don't I need the "@" sign (as a tutorial had said) before my field names when I am looping to output?

                 

                Lastly, what is the best way to automatically display the thumbnail (container field?) , that we have the file path for?

                • 5. Re: XML import format help!
                  FentonJones

                  1. The position() function does pretty much what you expect. It returns the number of that node position where it is processing (something like that). If you put it at a different level, it will return for that level. In this case I just stuck it in to return the "record number" within the record set of the xml. It is optional; you could just use the short form for the ROW:

                  <ROW MODID="" RECORDID="">

                  But sometimes it helps to see the record number, if you're troubleshooting the result in an XML transformation editor.

                   

                  The position() function can be useful in other ways also, like setting the position into a variable, then outputting its value, or using to select data.

                   

                  2.The "@" sign is used to select an "attribute", which is a name-value pair within an element. 

                  <element_name something="donuts">My Donut Shop</element_name>

                  In the above case,

                  element_name/@something would produce "donuts"

                  element_name would produce "My Donut Shop"

                   

                  Attributes are not always there. It's just a way to add another level to an element, without having to create a new sub element. There should be some consistent logic followed when doing so.

                   

                  3. If you have a path to an image, likely a URL path, then there's a couple ways to show it. Use Text Functions to create a calculation field which has it as a FileMaker syntax path (platform-specific, you can add both), with a result of Container; which will show the image IF you have access. Or just use a Web Viewer with the URL path.

                   

                  If you want to download the image locally, then there's more involved, different tools for different platforms.

                  • 6. Re: XML import format help!
                    dogger27

                    Very Cool! That's a great thing to know about the position() function, and also proper usage for the @ character.

                    I was able to view the images in the container field,  as a relative path was included in the XML file. I used text functions to create the absolute path:

                       "imagemac:/Projects/Sanctuary/RED/" & MiddleWords(ProjectsR3D;6;3) & "/" & ImageRef

                    It took some time to discover I needed to say "imagemac:", luckily that was in another post

                    I'm so glad to have this working, thanks again for your time :-)

                    Paul Wiens

                    • 7. Re: XML import format help!
                      dogger27

                      I have one more question.. if this is my XML file below, how do I get the "Scene" and "Take" fields into a Filemaker column? 

                       

                      <Metadata>

                              <StandardFields>

                                  <Fieldname>ClipName</Fieldname>

                              </StandardFields>

                              <CustomFields>

                                  <Fieldname>Scene</Fieldname>

                                  <Fieldname>Take</Fieldname>

                              </CustomFields>

                      </Metadata>

                      <Clip>

                              <ClipName>A001_C002_03159G</ClipName>

                              <CustomField name="Scene">30</CustomField>

                              <CustomField name="Take">1</CustomField>

                      </Clip>

                       

                      Thank you. Paul

                      • 8. Re: XML import format help!
                        FentonJones

                        I didn't notice those. They require a little more structure to target the fields. You can do that with a test inside the node path, which works well in XSL. In this case: value of the CustomField where the 'name' attribute is "Scene" (then "Take" for the next one)

                         

                         

                        <!-- Custom Fields -->
                        <COL>
                             <DATA>
                                  <xsl:value-of select="CustomField[@name='Scene']"/>
                             </DATA>
                        </COL>
                        <COL>
                             <DATA>
                                  <xsl:value-of select="CustomField[@name='Take']"/>
                             </DATA>
                        </COL>

                         

                        Looks like I'd already got them in the FileMaker <METADATA>, by using an "/*/" wildcard in the node path I was using to get those "fieldname" elements from the XML; so it matched either 'StandardFields/Fieldname' or 'CustomFields/Fieldname'. Or you could just write them out; but I was lazy.

                         

                        • 9. Re: XML import format help!
                          dogger27

                          Thanks Fenton, that works really great!

                          The only problem now is, that when I declare the input fields at the top of the XSL file:

                           

                          <xsl:for-each select="ClipfinderData/Metadata/CustomFields/Fieldname">

                             <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="{.}" TYPE="TEXT"/>

                           </xsl:for-each>

                           

                          sometimes there will be 2 custom fields in the XML file, and sometimes there will be 3.

                           

                          So when I define the columns like so:

                           

                          <COL>

                          <DATA>

                          <xsl:value-of select="CustomField[@name='Scene']"/>

                          </DATA>

                          </COL>

                          <COL>

                          <DATA>

                          <xsl:value-of select="CustomField[@name='Take']"/>

                          </DATA>

                          </COL>

                          <COL>

                          <DATA>

                          <xsl:value-of select="CustomField[@name='Lens MM']"/>

                          </DATA>

                          </COL>

                           

                          It doesn't import right if "Lens MM" is not in the XML file. 

                          Thanks,

                          Paul

                           

                          • 10. Re: XML import format help!
                            FentonJones

                            While it would be possible to do some testing within the XSL, to allow it to be dynamic, I would say, since there's only 3, to just hard-code them. That means hard-coding the <METADATA> instead of using the xsl:for-each. I used that at first because it would be tedious to write out the Metadata (though they are much the same, other than the name). 

                             

                            But you don't have to write them out. You can just copy them from the resulting xml, if you are using an XML transformation editor (and you should be; it is a bit nuts to try and write XSL and just test with FileMaker, as you cannot see the result unless it's 100% correct, which it seldom is at first).

                             

                            If the xml element is not there, it does not matter; you must still create a <COL><DATA> anyway. An XML Import is not magic; you must have a field there, for every possible field you want, whether it has data or not in that particular XML file, else the Import Order is off.

                             

                              <METADATA>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipFilePath" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipName" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipRenderName" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipCustomName" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipDateTime" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipDuration" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipWidth" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipHeight" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipFrameRate" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipNumFrames" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipISO" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipShutter" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipFirmwareVersion" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipAudioChannels" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipTC" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipTCOut" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipEC" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipECOut" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipInFrame" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipOutFrame" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipComment" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipGoodTake" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ClipPosterFrame" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Scene" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Take" TYPE="TEXT"/>

                                <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Lens MM" TYPE="TEXT"/>

                              </METADATA>

                            • 11. Re: XML import format help!
                              FentonJones

                              An advantage of hard-coding the Metadata is that you could specify NUMBER instead of TEXT for some things also; not that FileMaker really cares much for the Import; but it would if you used the XSL to create a new FileMaker file from the XML.