10 Replies Latest reply on Dec 3, 2009 8:09 AM by comment_1

    XML - How to pick up the entire 'advert' text and dump into the relevant Filemaker record

    goodhope

      Title

      XML - How to pick up the entire 'advert' text and dump into the relevant Filemaker record

      Post

      Hello,

      Sorry to flood the board with XML related questions, but I do have another issue.

      As people will know I've been busy reading an XML file into Filemaker -
      THANKS ALL FOR YOUR HELP!. The XML file is a collection of adverts which make up a newspaper. I've been collecting named elements and passing their textual content through into separate fields in Filemaker - working well!

      I now find out that a requirement is also to pick up the entire advert content (an advert starts with and ends with tags - <NOTICE> and </Notice>) and dump that into another field on each record - one advert equals one record. I know how to pick up separate elements and use its related data, but don't know how at the same point to gather all the advert text together and then dump into a named field in filemaker.

      Each record will have data separated into its correct fields, as is currently the case (for instance 'Publish date' text goes into the 'PublishDate' field), then another field will hold the complete advert text. Please see my XSLT and sample XML file below. If anyone could help with the syntax and location of commands within my XSLT to achieve this that would be great.

      Cheers Nick 

       

      XSLT:

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

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

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

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

      <FIELD NAME="CourtDistrict" 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="TradingAs" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>

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

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

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

      <FIELD NAME="TypeOfLiquidation" 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=""/>

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

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

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

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

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

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

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

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

      <FIELD NAME="DateSigned" 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="@Type"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@Reference"/></DATA></COL>

      <COL><DATA><xsl:value-of select="Metadata/PublishDate"/></DATA></COL>

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

      <COL><DATA><xsl:value-of select="Court/CourtDistrict"/></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/CompanyOtherNames/TradingAs"/></DATA></COL>

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

      <COL><DATA><xsl:for-each select="P/Text"><xsl:value-of select="." /><xsl:text>&#10;&#13;</xsl:text></xsl:for-each></DATA></COL>

      <COL><DATA><xsl:for-each select="Company/NatureOfBusiness"><xsl:value-of select="." /><xsl:text>&#10;&#13;</xsl:text></xsl:for-each></DATA></COL>

      <COL><DATA><xsl:value-of select="Company/TypeOfLiquidation"/></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>

      <COL><DATA><xsl:for-each select="Administration/Administrator/PersonName | P/Text/PersonName"><xsl:value-of select="." /><xsl:text>&#10;&#13;</xsl:text></xsl:for-each></DATA></COL>

      <COL><DATA><xsl:for-each select="Administration/Administrator/OfficeHolderNumber | Administration/OfficeHolderNumbers/OfficeHolderNumber"><xsl:value-of select="." /><xsl:text>&#10;&#13;</xsl:text></xsl:for-each></DATA></COL>

      <COL><DATA><xsl:value-of select="Administration/Administrator/FirmName | P/Text/FirmName"/></DATA></COL>

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

      <COL><DATA><xsl:value-of select="Administration/Administrator/Date | P/Text/Date"/></DATA></COL>

      <COL><DATA><xsl:value-of select="Administration/Administrator/Time | P/Text/Time"/></DATA></COL>

      <COL><DATA><xsl:value-of select="Administration/Administrator/Legislation | P/Text/Legislation"/></DATA></COL>

      <COL><DATA><xsl:value-of select="Administration/Administrator/LegislationSection | P/Text/LegislationSection"/></DATA></COL>

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

      </ROW>

      </xsl:for-each>

      </RESULTSET>

      </FMPXMLRESULT>

      </xsl:template>

      </xsl:stylesheet>

       

       

      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-23</PublishDate>

      <SupplierNoticeID>1588-070293</SupplierNoticeID>

      </Metadata>

      <Court>

      <CourtName>High Court</CourtName>

      <CourtNumber Number="170266" Year="2008"/>

      </Court>

      <Company>

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

      <CompanyNumber>55555666</CompanyNumber>

      <NatureOfBusiness>Supply</NatureOfBusiness>

      <NatureOfBusiness>TexMex Restaurant</NatureOfBusiness>

      <CompanyRegisteredOffice>135 London</CompanyRegisteredOffice>

      </Company>

      <Administration>

      <DateOfAppointment Date="2008-08-10">10 August 2008</DateOfAppointment>

      <Administrator>

      <PersonName Class="Administrator"><Forename>Jim</Forename> <Surname>Jones</Surname></PersonName>  (IP Nos <OfficeHolderNumber>7720</OfficeHolderNumber>), of <FirmName Class="Administrator">S Limited</FirmName>, <AddressLineGroup Class="Administrator">34 London Road, London</AddressLineGroup>.

      </Administrator>

      </Administration></Notice>

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

      <Metadata>

      <PublishDate>2009-08-23</PublishDate>

      <SupplierNoticeID>588-070293</SupplierNoticeID>

      </Metadata>

      <Court>

      <CourtName>High Court</CourtName>

      <CourtNumber Number="7499" Year="2008"/>

      </Court>

      <Company>

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

      <CompanyNumber>99995666</CompanyNumber>

      <NatureOfBusiness>Plastics</NatureOfBusiness>

      <CompanyRegisteredOffice>132 London Way, London</CompanyRegisteredOffice>

      </Company>

      <Administration>

      <DateOfAppointment Date="2009-08-22">22 June 2009</DateOfAppointment>

      <Administrator>

      <PersonName Class="Administrator"><Forename>Mark</Forename> <Surname>Phillips</Surname></PersonName> (IP Nos <OfficeHolderNumber>8820</OfficeHolderNumber>), of <FirmName Class="Administrator">APS Limited</FirmName>, <AddressLineGroup Class="Administrator">31 Ambrose Lane, Oxford</AddressLineGroup>.

      </Administrator></Administration></Notice></Section></Category></Body></Gazette>

       

       




        • 1. Re: XML - How to pick up the entire 'advert' text and dump into the relevant Filemaker record
          comment_1
            

          I am not sure why you need to do this, when the same information is being imported into separate fields. You could simply concatenate the fields within Filemaker.

           

          Anyway, you could add a field to your stylesheet and specify its contents as:

           

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

           

          This will get the text contents of the current node (i.e. <Notice>) and all its descendants - but not the information contained in attributes.

          • 2. Re: XML - How to pick up the entire 'advert' text and dump into the relevant Filemaker record
            goodhope
              

            Hello,

             

            Yes, I take you're point. However, I'm not actually using all the data inside Filemaker. So I thought the easiest way if I had to re-constitute the data would be joining all of these 'entire' field records together. It's probably also a good idea also from a point of view of checking I've got all the data in the right places - initially anyway!

             

            Thanks very much for your help. You seem to be very good with XSLT - did you actually attend a course or is this self learnt?

             

            Cheers Nick 

            • 3. Re: XML - How to pick up the entire 'advert' text and dump into the relevant Filemaker record
              goodhope
                

              Just looking into you're suggestion:

               

               

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

               

              This will get the text contents of the current node (i.e. <Notice>) and all its descendants - but not the information contained in attributes.

               

               

              I guess I add this line as the first entry in my <ROW> block? Do I also need to put something into the METADATA block as a field name? Then how does filemaker know what field to map to on import as I've not specified a name in the XSLT?

               

              Thanks 

              • 4. Re: XML - How to pick up the entire 'advert' text and dump into the relevant Filemaker record
                goodhope
                  

                Sorry, I should also have mentioned at the point of 'dumping' all advert data into one field in Filemaker that I'd really like to retain the XML tags - so the entire notice is inserted in its entirety (data and tagging).

                 

                Cheers Nick 

                • 5. Re: XML - How to pick up the entire 'advert' text and dump into the relevant Filemaker record
                  comment_1
                    

                  goodhope wrote:
                  I thought the easiest way if I had to re-constitute the data would be joining all of these 'entire' field records together.

                  Not really: the XML source keeps the data separated into tagged elements, similar to "records" and "fields". Lumping it all together in a single text field is not a good preparation for extracting it later.

                   

                   


                  goodhope wrote:
                  did you actually attend a course or is this self learnt?

                  Entirely self-learned - and there is still a LOT I don't know... Fortunately, you only need to to know a small part of XSLT in order to work with Filemaker - and when you encounter a specific problem, you can usually look it up pretty quickly in a book or on the web.

                  • 6. Re: XML - How to pick up the entire 'advert' text and dump into the relevant Filemaker record
                    goodhope
                      
                    Hello There,
                     
                    It seems I have a long way to go! - I'm only really a Filemaker beginner and probably less in terms of XSLT. I've been using Filemaker for about 3-4 months now, employed to do so for 2. I have been working with structured data before, but not to the degree that I really had to understand XSLT - now I do, or certainly a little anyway! I really appreciate you're assistance. 

                    I'm looking into you're suggestion:
                     
                     
                    <COL><DATA><xsl:value-of select="."/></DATA></COL>
                     
                    This will get the text contents of the current node (i.e. <Notice>) and all its descendants - but not the information contained in attributes.
                     
                     
                    I guess I add this line as the first entry in my <ROW> block? Do I also need to put something into the METADATA block as a field name? Then how does filemaker know what field to map to on import as I've not specified a name in the XSLT? It does seem I need to do this - its a customer requirement now.

                    Sorry, I should also have mentioned at the point of 'dumping' all advert data into one field in Filemaker that I'd really like to retain the XML tags - so the entire notice is inserted in its entirety (data and tagging).
                     

                    • 7. Re: XML - How to pick up the entire 'advert' text and dump into the relevant Filemaker record
                      comment_1
                        

                      Every Filemaker field in your stylesheet has two elements:

                       

                      1. <FIELD> - the field's definition in the <METADATA> section;

                      2. <COL> - the field's contents in the <RESULTSET> section.

                       

                      These two are matched by their order in the stylesheet, e.g.. the third <FIELD> is "fed" data specified in the third <COL> element.

                       

                       

                       


                      goodhope wrote:
                      I'd really like to retain the XML tags - so the entire notice is inserted in its entirety (data and tagging).

                       

                      Again, I am not sure this is a good idea: you will get a copy of the <Notice> element in a text field as text - there's not much that you can do with this later (at least not easily).

                       

                       

                      Anyway, I believe this should produce the desired result:

                      <COL><DATA>
                      <xsl:text disable-output-escaping="yes">&lt;![CDATA[</xsl:text>
                      <xsl:copy-of select="."/>
                      <xsl:text disable-output-escaping="yes">]]&gt;</xsl:text>
                      </DATA></COL>

                       



                      • 8. Re: XML - How to pick up the entire 'advert' text and dump into the relevant Filemaker record
                        goodhope
                          

                        Thanks - that's it exactly.

                         

                        Regards Nick 

                        • 9. Re: XML - How to pick up the entire 'advert' text and dump into the relevant Filemaker record
                          goodhope
                            

                          Hello,

                           

                          I now find that I do indeed desire the entire textual content of my Advert but now NOT including text inside the <Metadata> . . . </Metadata> section. At the moment thanks to you're help I get the entire textual content of an advert (which was what I'd asked for) - I now need to cut out the red data in the sample XML file below.

                           

                          Please see sample notice:

                           

                          <Notice Type="2508" Reference="8888439">

                          <Metadata>

                          <PublishDate>2008-08-23</PublishDate>

                          <SortKey/>

                          <Language>English</Language>

                          <NoticeClass>Standard</NoticeClass>

                          </Metadata>

                          <Person>

                          <PersonName>Marcia Higgs</PersonName>

                          <PersonStatus>Employed</PersonStatus>

                          </Person>

                          <P><Text>Notice is hereby given that blah blah.</Text></P>

                          <Administration>

                          <Administrator><PersonName Class="Administrator"><Forename>Mike</Forename> <MiddleNames>Lambert</MiddleNames> <Surname>Jones</Surname></PersonName> Trustee</Administrator>

                          <DateSigned>Dated: 12 August 2008</DateSigned>

                          </Administration>

                          </Notice> 

                           

                           

                          Line in my XSLT at the moment (allows import of entire Textual and Tagging content):

                           

                          <COL><DATA><xsl:text disable-output-escaping="yes">&lt;![CDATA[</xsl:text><xsl:copy-of select="."/><xsl:text disable-output-escaping="yes">]]&gt;</xsl:text></DATA></COL>

                           

                          Thanks for you're assistance.

                           

                          Regards Nick 

                           

                           

                          • 10. Re: XML - How to pick up the entire 'advert' text and dump into the relevant Filemaker record
                            comment_1
                              

                            I suppose it's possible, but I am afraid I'd have to work on it for longer than what's reasonable within a forum exchange.

                             

                            This is not really a Filemaker question anyway - perhaps you should try asking it on a XSL forum.