8 Replies Latest reply on Dec 9, 2009 9:37 PM by didier98004

    How to create new records on XML import

    goodhope

      Title

      How to create new records on XML import

      Post

      Hello,

       

      I've previously posted here about importing XML. This is the first time I've had to look into this, so I cut down my XML file to make life easier. What I have really is an XML file containing many Adverts, each beginning with the [Notice] tag. I'd really like to know how on import into Filemaker to create a new record when a [Notice] tag is encountered (one Advert to equal one record). Please see below an example of how my XML file and XSLT looks:

       

      I guess I need to make an amendment to the XSLT, but I'm not sure what command syntax to use or where to place it.

       

      Help will be greatefully received.

       

      Regards Nick

       

       

       XML FILE:

      [?xml version="1.0" encoding="UTF-8"?]
      [Paper]
      [Body][Category Type="2"][Section Type="Administration"][Notice Type="10" Reference="345678"]
      [Metadata]
      [PublishDate]2009-08-08[/PublishDate]
      [SupplierNoticeID]8B51588-070293[/SupplierNoticeID]
      [NoticeClass]Standard[/NoticeClass[
      [/Metadata]
      [Court]
      [CourtName]High Court of Justice[/CourtName]
      [CourtNumber Number="172345 and 17433" Year="2008"/]
      [/Court]
      [Company]
      [CompanyName Class="Company"]AZ CONSULTANCY[/CompanyName]
      [CompanyNumber]12345678[/CompanyNumber]
      [NatureOfBusiness]Software Supply[/NatureOfBusiness]
      [CompanyRegisteredOffice]Address Line Material[/CompanyRegisteredOffice]
      [/Company]
      [Administration]
      [DateOfAppointment Date="2009-08-11"]10 August 2009[/DateOfAppointment]

      [/Administration][/Notice][/Section][/Category][/Body][/Paper] 

       

       

      XSLT FILE:

      <!--  StartFragment  -->

      [?xml version="1.0"encoding="UTF-8"?]

      [xsl:stylesheetversion="1.0"xmlns:xsl="http://www.w3.org/1999/XSL/Transform"]

      [xsl:outputmethod="xml" version="1.0" encoding="utf-8"indent="yes"/]

      [xsl:templatematch="/"]

      [FMPXMLRESULTxmlns="http://www.filemaker.com/fmpxmlresult"]

      [ERRORCODE]0[/ERRORCODE]

      [PRODUCT BUILD=""NAME="" VERSION=""/]

      [DATABASEDATEFORMAT="" LAYOUT="" NAME=""RECORDS="" TIMEFORMAT=""/]

       

      [METADATA]

      [FIELDNAME="CourtName" TYPE="TEXT" EMPTYOK="YES"MAXREPEAT=""/]

      [FIELDNAME="Number" TYPE="TEXT" EMPTYOK="YES"MAXREPEAT=""/]

      [FIELD NAME="Year"TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/]

      [FIELDNAME="CompanyName" TYPE="TEXT" EMPTYOK="YES"MAXREPEAT=""/]

      [FIELDNAME="CompanyNumber" TYPE="NUMBER" EMPTYOK="YES"MAXREPEAT=""/]

      [FIELD NAME="NatureOfBusiness"TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/]

      [FIELDNAME="CompanyRegisteredOffice" TYPE="TEXT"EMPTYOK="YES" MAXREPEAT=""/]

      [FIELDNAME="DateOfAppointment" TYPE="TEXT"EMPTYOK="YES" MAXREPEAT=""/]

      [FIELDNAME="Administrator" TYPE="TEXT" EMPTYOK="YES"MAXREPEAT=""/]

      [/METADATA]

       

      [RESULTSET FOUND=""]

      [ROW MODID=""RECORDID=""]

      [COL][DATA][xsl:value-ofselect="Gazette/Body/Category/Section/Notice/Court/CourtName"/][/DATA][/COL]

      [COL][DATA][xsl:value-of select="Gazette/Body/Category/Section/Notice/Court/CourtNumber/@Number"/][/DATA][/COL]

      [COL][DATA][xsl:value-ofselect="Gazette/Body/Category/Section/Notice/Court/CourtNumber/@Year"/][/DATA][/COL]

      [COL][DATA][xsl:value-ofselect="Gazette/Body/Category/Section/Notice/Company/CompanyName"/][/DATA][/COL]

      [COL][DATA][xsl:value-ofselect="Gazette/Body/Category/Section/Notice/Company/CompanyNumber"/][/DATA][/COL]

      [COL][DATA][xsl:value-ofselect="Gazette/Body/Category/Section/Notice/Company/NatureOfBusiness"/][/DATA][/COL]

      [COL][DATA][xsl:value-ofselect="Gazette/Body/Category/Section/Notice/Company/CompanyRegisteredOffice"/][/DATA][/COL]

      [COL][DATA][xsl:value-ofselect="Gazette/Body/Category/Section/Notice/Administration/DateOfAppointment"/][/DATA][/COL]

      [COL][DATA][xsl:value-ofselect="Gazette/Body/Category/Section/Notice/Administration/Administrator"/][/DATA][/COL]

      [/ROW]

       

      [/RESULTSET]

      [/FMPXMLRESULT]

      [/xsl:template]

      [/xsl:stylesheet]

      <!--  EndFragment  -->

       










        • 1. Re: How to create new records on XML import
          FentonJones
            

          The element you're missing is a way to tell the XSL where to start extracting data. There are 2 ways to do that, but the easiest (to understand, for me also) is for-each. You'd put it above your <ROW> line, so you'd get a new row/record for each. I'm using the below. If however, there can be multiple <Metadata> tags within a <Notice> then you'd move Metadata down one level into the "value-of" lines.

           

           <xsl:for-each select="Paper/Body/Category/Section/Notice/Metadata"> 

           

          We can only assume there is only one, as we also have to assume there is one court and one company. Otherwise you have one-to-many, which has the same problems as trying to do one-to-many as flat within FileMaker (or any other relational database). Just because XML looks flat, because it's one document does not mean it is flat, far from it. It can be as relational as a database; that is its strength.

           

          Also, please just post your XML directly, with its proper tags. Though there may be some problems sometimes with the forum parser, it is better than whatever you're using. I had to spend 15 min fixing your xml before it could be used. Not only replacing the "[]", but separating attributes that got mashed together.

           

          P.S. There was no <Administrator> in the posted XML, but maybe sometimes there is, so we'll leave it in.

           

           

          <?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" 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="CourtName" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>

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

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

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

          <FIELD NAME="CompanyNumber" TYPE="NUMBER" EMPTYOK="YES" MAXREPEAT=""/>

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

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

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

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

          </METADATA>

           

          <RESULTSET FOUND="">

          <xsl:for-each select="Paper/Body/Category/Section/Notice/Metadata">

          <ROW MODID="" RECORDID="">

          <COL><DATA><xsl:value-of select="Court/CourtName"/></DATA></COL>

          <COL><DATA><xsl:value-of select="Court/CourtNumber/@Number"/></DATA></COL>

          <COL><DATA><xsl:value-of select="Court/CourtNumber/@Year"/></DATA></COL>

          <COL><DATA><xsl:value-of select="Company/CompanyName"/></DATA></COL>

          <COL><DATA><xsl:value-of select="Company/CompanyNumber"/></DATA></COL>

          <COL><DATA><xsl:value-of select="Company/NatureOfBusiness"/></DATA></COL>

          <COL><DATA><xsl:value-of select="Company/CompanyRegisteredOffice"/></DATA></COL>

          <COL><DATA><xsl:value-of select="Administration/DateOfAppointment"/></DATA></COL>

          <COL><DATA><xsl:value-of select="Administration/Administrator"/></DATA></COL>

          </ROW>

          </xsl:for-each>

          </RESULTSET>

          </FMPXMLRESULT>

          </xsl:template>

          </xsl:stylesheet> 

          • 2. Re: How to create new records on XML import
            goodhope
              

            Hello Fenton,

             

            Thanks for the information. I've done as you suggested below - I afraid I don't seem to get what I was after. Now on import into Filemaker I do indeed get two records created (because my XML file now contains two notices/adverts) - but each record is showing empty fields (no advert text present). If I don't put in the 'for-each' then I get one record created and I manage to see data from the first advert. The 'for-each' is obviously managing to create the correct number of records, but they all (both) seem to be empty of actual text in the fields - please see xml file and current xslt below.

             

            Thanks again for your assistance. Sorry about the time it took - I've pasted in directly this time with the correct open/close tags present.

             

            XML FILE

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

            <Gazette>

            <Body><Category Type="24"><Section Type="Administration"><Notice Type="2410" Reference="900771">

            <Metadata>

            <PublishDate>2009-08-24</PublishDate>

            <SupplierNoticeID>RB51588-070293</SupplierNoticeID>

            <NoticeClass>Standard</NoticeClass>

            </Metadata>

            <Court>

            <CourtName>High Court of Justice</CourtName>

            <CourtNumber Number="17022 and 17413" Year="2009"/>

            </Court>

            <Company>

            <CompanyName Class="Company">AA CONSULTANCY LIMITED</CompanyName>

            <CompanyNumber>05425666</CompanyNumber>

            <NatureOfBusiness>Other Software and Consultancy Supply</NatureOfBusiness>

            <NatureOfBusiness>TexMex Restaurant</NatureOfBusiness>

            <CompanyRegisteredOffice>132 Henwick Road, St Johns, Worcester, WR2 5PB</CompanyRegisteredOffice>

            </Company>

            <Administration>

            <DateOfAppointment Date="2009-08-11">11 August 2009</DateOfAppointment>

            <Administrator>

            <PersonName Class="Administrator"><Forename>Mark</Forename> <Surname>Phillips</Surname></PersonName> and <PersonName Class="Administrator"><Forename>Julie</Forename> <Surname>Swan</Surname></PersonName> (IP Nos <OfficeHolderNumber>9320</OfficeHolderNumber> and <OfficeHolderNumber>9168</OfficeHolderNumber>), both of <FirmName Class="Administrator">APS Insolvency Limited</FirmName>, <AddressLineGroup Class="Administrator">31 Ambrose Lane, Harpenden, Herts, AL5 4DG</AddressLineGroup>.

             

            </Administrator>

            </Administration></Notice>

            <Notice Type="2410" Reference="77771">

            <Metadata>

            <PublishDate>2009-08-24</PublishDate>

            <SupplierNoticeID>RB51588-070293</SupplierNoticeID>

            <NoticeClass>Standard</NoticeClass>

            </Metadata>

            <Court>

            <CourtName>High Court of Justice</CourtName>

            <CourtNumber Number="7413" Year="2009"/>

            </Court>

            <Company>

            <CompanyName Class="Company">BB CONSULTANCY LIMITED</CompanyName>

            <CompanyNumber>05425666</CompanyNumber>

            <NatureOfBusiness>Other Software and Consultancy Supply</NatureOfBusiness>

            <CompanyRegisteredOffice>132 Henwick Road, St Johns, Worcester, WR2 5PB</CompanyRegisteredOffice>

            </Company>

            <Administration>

            <DateOfAppointment Date="2009-08-11">11 August 2009</DateOfAppointment>

            <Administrator>

            <PersonName Class="Administrator"><Forename>Mark</Forename> <Surname>Phillips</Surname></PersonName> and <PersonName Class="Administrator"><Forename>Julie</Forename> <Surname>Swan</Surname></PersonName> (IP Nos <OfficeHolderNumber>9320</OfficeHolderNumber> and <OfficeHolderNumber>9168</OfficeHolderNumber>), both of <FirmName Class="Administrator">APS Insolvency Limited</FirmName>, <AddressLineGroup Class="Administrator">31 Ambrose Lane, Harpenden, Herts, AL5 4DG</AddressLineGroup>.

             

            </Administrator>

            </Administration></Notice></Section>

            </Category>

            </Body>

             

            </Gazette>

             

             

            XSLT FILE:

            <?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" 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="CourtName" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>

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

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

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

            <FIELD NAME="CompanyNumber" TYPE="NUMBER" EMPTYOK="YES" MAXREPEAT=""/>

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

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

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

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

            </METADATA>

             

            <RESULTSET FOUND="">

             

            <xsl:for-each select="Gazette/Body/Category/Section/Notice/Metadata">

            <ROW MODID="" RECORDID="">

            <COL><DATA><xsl:value-of select="Gazette/Body/Category/Section/Notice/Court/CourtName"/></DATA></COL>

            <COL><DATA><xsl:value-of select="Gazette/Body/Category/Section/Notice/Court/CourtNumber/@Number"/></DATA></COL>

            <COL><DATA><xsl:value-of select="Gazette/Body/Category/Section/Notice/Court/CourtNumber/@Year"/></DATA></COL>

            <COL><DATA><xsl:value-of select="Gazette/Body/Category/Section/Notice/Company/CompanyName"/></DATA></COL>

            <COL><DATA><xsl:value-of select="Gazette/Body/Category/Section/Notice/Company/CompanyNumber"/></DATA></COL>

            <COL><DATA><xsl:value-of select="Gazette/Body/Category/Section/Notice/Company/NatureOfBusiness"/></DATA></COL>

            <COL><DATA><xsl:value-of select="Gazette/Body/Category/Section/Notice/Company/CompanyRegisteredOffice"/></DATA></COL>

            <COL><DATA><xsl:value-of select="Gazette/Body/Category/Section/Notice/Administration/DateOfAppointment"/></DATA></COL>

            <COL><DATA><xsl:value-of select="Gazette/Body/Category/Section/Notice/Administration/Administrator"/></DATA></COL>

             

            </ROW>

            </xsl:for-each>

             

            </RESULTSET>

            </FMPXMLRESULT>

             

            </xsl:template>

             

            </xsl:stylesheet> 

            • 3. Re: How to create new records on XML import
              comment_1
                

              You have two mistakes.

               

               

              First, this line is incorrect:

               

              <xsl:for-each select="Gazette/Body/Category/Section/Notice/Metadata">

               

              You want to create a record for each <Notice> element - NOT for each <Metadata> element inside <Notice>. This may seem unimportant when there is only one <Metadata> element inside each <Notice> - but it is important, as will become apparent immediately.

               

               

               

              Second, when you start the for-each loop, you are already in the context of <Notice> (well, actually you are in the context of <Metadata>, but you should be in the context of <Notice>). Now your line:

               

              <xsl:value-of select="Gazette/Body/Category/Section/Notice/Court/CourtName"/>

               

              is looking for the element <Gazette> inside the current  element - and of course, it won't find anything.

               

               

               

              The correct way would be:

               

               ...

              <xsl:for-each select="Gazette/Body/Category/Section/Notice">
              <ROW MODID="" RECORDID="">
              <COL><DATA><xsl:value-of select="Court/CourtName"/></DATA></COL>
               ...

               

              • 4. Re: How to create new records on XML import
                goodhope
                  

                Hello Fenton,

                 

                Just had success! - I've managed to successfully create more than one record!

                 

                The problem I was having was all to do with the paths that I was using. In the 'for-each' where you specify which tag to create a new advert on (around <ROW> block), I needed to specify the actual full path (Paper/Body/Category/Notice). Inside the ROW block where you specify what data fields you want to use specify the path from the point of Notice - so for 'CourtName' key '<COL><DATA><xsl:value-of select="Court/CourtName"/></DATA></COL>'.

                 

                Thanks ever so much again for your help - I just needed to look at your sample XSLT a little better!

                 

                Cheers Nick 

                • 5. Re: How to create new records on XML import
                  FentonJones
                    

                  Following the structure, as comment said, I added one XSL function, to the Administrator element, which has a mixture of elements and plain text inside it.

                  normalize-space(), which "...removes all extra spaces and converts line feed, carriage return, and table characters into single spaces."

                   

                  <?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" 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="CourtName" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>

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

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

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

                  <FIELD NAME="CompanyNumber" TYPE="NUMBER" EMPTYOK="YES" MAXREPEAT=""/>

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

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

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

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

                  </METADATA>

                  <RESULTSET FOUND="">

                  <xsl:for-each select="Gazette/Body/Category/Section/Notice">

                  <ROW MODID="" RECORDID="">

                  <COL><DATA><xsl:value-of select="Court/CourtName"/></DATA></COL>

                  <COL><DATA><xsl:value-of select="Court/CourtNumber/@Number"/></DATA></COL>

                  <COL><DATA><xsl:value-of select="Court/CourtNumber/@Year"/></DATA></COL>

                  <COL><DATA><xsl:value-of select="Company/CompanyName"/></DATA></COL>

                  <COL><DATA><xsl:value-of select="Company/CompanyNumber"/></DATA></COL>

                  <COL><DATA><xsl:value-of select="Company/NatureOfBusiness"/></DATA></COL>

                  <COL><DATA><xsl:value-of select="Company/CompanyRegisteredOffice"/></DATA></COL>

                  <COL><DATA><xsl:value-of select="Administration/DateOfAppointment"/></DATA></COL>

                  <COL><DATA><xsl:value-of select="normalize-space(Administration/Administrator)"/></DATA></COL>

                  </ROW>

                  </xsl:for-each>

                  </RESULTSET>

                  </FMPXMLRESULT>

                  </xsl:template>

                  </xsl:stylesheet> 

                  • 6. Re: How to create new records on XML import
                    didier98004
                      

                    Hi..

                     

                    Thanks for this thread, as it has helped me too on understanding better the XSLT

                     

                    I have an extra question about the XSLT format structure

                     

                    The Submitted XML file had a section formatted like this:

                     

                    <Metadata>

                    <PublishDate>2009-08-24</PublishDate>

                    <SupplierNoticeID>RB51588-070293</SupplierNoticeID>

                    <NoticeClass>Standard</NoticeClass>

                    </Metadata>

                     

                    In my case, though, the formatting for the same data in my XML would be like:

                     

                    <Metadata>

                    <Info name="PublishDate" date="2009-08-24">

                                  <alias name="Published Date"/> 

                    </Info> 

                    <Info name="SupplierNoticeID" value="RB51588-070293"

                                  <alias name="Supplier Notice"/> 

                    </Info>

                    <Info name="NoticeClass" value="Standard"

                                  <alias name="Notice Class"/> 

                    </Info>

                    </Metadata>

                     

                    If I wanted to import the date from SupplierNoticeID and put it in another field in the database,

                    what should the syntax of the XSLT be in the ROW section?

                     

                     

                     

                    • 7. Re: How to create new records on XML import
                      comment_1
                        

                      It's hard to say without seeing the wider context. Assuming you are creating a record for each <Notice>, and that <Metadata> is a child of <Notice>, you could fetch the date by:

                      <COL><DATA><xsl:value-of select="Metadata/Info[@name='PublishDate']/@date"/></DATA></COL>

                       

                       

                       ADD:


                      Didier Monin wrote:
                      If I wanted to import the date from SupplierNoticeID

                      There is no date in the SupplierNoticeID info - the only date I see is in the <Info> element named PublishDate. If you meant "data", then I guess it would be:

                       

                      <COL><DATA><xsl:value-of select="Metadata/Info[@name='SupplierNoticeID']/@value"/></DATA></COL>


                      • 8. Re: How to create new records on XML import
                        didier98004
                          

                        My bad, I meant the value indeed, not the date. Thanks for the help!