2 Replies Latest reply on Apr 22, 2014 6:12 PM by beverly

    XML/XSD file mapping

    seancsn

      Hi,

       

      I am in the process of setting up a database for NHS clinicians and am wanting to include a dropdown for medicines. The NHS provide the attached files every 4 weeks which provides a drug list and associated information. I have no idea how to put the files together and import into filemaker. Would anyone be able to help? Ive attached the user spec and data model.

      Thanks in advance

       

      Sean

        • 1. Re: XML/XSD file mapping
          nickorr

          Sean,

           

          The best way to handle this is to write xslt to transform the data into the FMP XML format so it can be imported directly from the files.  I had a quick look at the files and there are lots of datasets in here.  For example the amp file has data that you could import into four tables.  The lookup file has many more.  Each one requires it's own XSLT in order to map the fields into your FileMaker table structure.  Certainly possible but there's a fair amount of time invovled here setting up all the mappings.

           

          I'd suggest it would be worthwhile writing your own xslt to do this, it's not as complex as it seems and then you can maintain it yourself.  I've got a primer on XML and XSLT in three parts, you can start here : http://www.goya.com.au/blog/understanding-and-using-database-design-report-part-1  This was written a while back, and is based around the DDR's version of XML, but the concepts apply anywhere.

           

          Alternatively if you'd like some help to get this done, let me know.

           

          Cheers,

          Nick

          • 2. Re: XML/XSD file mapping
            beverly

            Import is relatively simple compared to export.

            To help you understand the import schema (patterns), I usually start with a simple Export (XML) from the table which I will use for Import. There! Your XSLT basis is started. The rest is getting the correct XPATH to the element you want in a field. And if you can discover what repeats, these will probably be related.

             

            Nicholas is right about the one XSLT per table. Unless you have a one-one relationship, you cannot import into related fields. I find it easier to just make sure a unique value is imported when there is related data. Most imports I've done have something unique that can be used to tie the tables together. You can still have your auto-enter and look up fields, but they pull from existing data or constants & variables.

             

            I have a widget (fmdb) that makes mapping a little easier, but knowing that XPATH helps!

            If you look at source XML in many browsers, they "pretty format" with a default stylesheet. You may be able to collapse or expand chunks for better views of what repeats. Then the path is really each parent element that got you there (much like a folder structure):

            Root/parent/child/element2match

            This is the XPATH when the XML has

            Root

                Parent

                     Child

                         ......

             

             

            (With the angle brackets, of course)

             

            HTH

            -- sent from my iPhone4 --

            Beverly Voth

            --