Newest functions Upload a function Leave a tip My account

There are 2,570 functions in this library.

ExtractData ( XML ; Attribute ; Instance )

Extract specific data out of XML formatted text without using FileMaker's built-in XML functionality.

  Average rating: 3.8 (101 votes) Log in to vote

Andy Knasinski NRG Software http://www.nrgsoft.com/

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

Let ( [ XML_Length = Length ( XML ) ; Attribute_Length = Length ( Attribute ) ; Get_Instance = If ( IsEmpty ( Instance ) ; 1 ; Instance ) ]; Case( IsEmpty ( XML ) or IsEmpty ( attribute ) or PatternCount ( xml ; "<" & attribute & ">" ) = 0 ; "" ; Middle ( XML ; Position ( XML ; "<" & attribute & ">" ; 1 ; Get_Instance ) + attribute_length + 2 ; xml_length - ( xml_length - Position ( XML ; "</" & attribute & ">" ; 1 ; Get_Instance ) ) - ( Position ( XML ; "<" & attribute & ">" ; 1 ; Get_Instance ) + Attribute_Length + 2 ) ) ) ) Click to copy

Use this function to search for and return the specified instance of a field from any valid XML source.

 

Comments

		gus ohouo, Atlanta, GA Jan 8, 2009
Very clever I was looking for a way to import XML data created from a web application(ASP.net 2.0 technology) to a FileMaker Pro 8.0 database table, but your code up there caught my atttention for its logic. God Bless

		Charlie Bailey, Northampton MA Jan 10, 2011
Andy, Nice function - very simple - gets the job done!

		Daniel A. Shockley, New York, NY May 10, 2011
Here's a modified version that should handle attributes within the tags (where "tags" are what Andy called "attributes"). In other words, if your XML looks like this: <xml> <InvNum id="first" >123</InvNum> <InvNum id="second" >456</InvNum> <InvNum id="third" >789</InvNum> </xml> This should still get the value "456" if called as ExtracData( XML; "InvNum"; 2 ) Note that it still won't handle cases where attributes include ">" or where data looks like XML tags. No CDATA handling. // ExtractData ( XML ; Attribute ; Instance ) // version 1.1, Daniel A. Shockley // Based on code by Andy Knasinski, NRG Software, http://www.nrgsoft.com/ Let ( [ XML_Length = Length ( XML ) ; Attribute_Length = Length ( Attribute ) ; Get_Instance = If ( IsEmpty ( Instance ) ; 1 ; Instance ) ; posOpenTagStart = Position ( XML ; "<" & Attribute; 1; Get_Instance ) ; posNextOpen = Position ( XML ; "<"; posOpenTagStart + 1; 1 ) ; posOpenTagFinish = Position ( XML ; ">"; posOpenTagStart; 1 ) ; posCloseTagStart = Position( XML; "</" & Attribute & ">"; posOpenTagFinish; 1 ) ]; Case( IsEmpty ( XML ) or IsEmpty ( Attribute ) or ( posOpenTagStart = 0 ) // Attribute not in XML at all or ( posNextOpen>0 and posOpenTagFinish> posNextOpen ) // Attribute seems to start, but another tag starts before opening tag brackets close ; "" ; Middle ( XML ; posOpenTagFinish + 1 ; posCloseTagStart - posOpenTagFinish - 1 ) ) )

		Anannyaap, India Apr 14, 2014
Really helpful.

		Jim, Newyork Apr 6, 2016
Dosent work! Try: <SalesRankings> <SalesRank> <ProductCategoryId>music_display_on_website</ProductCategoryId> <Rank>1981</Rank> </SalesRank> <SalesRank> <ProductCategoryId>1033930</ProductCategoryId> <Rank>50</Rank> </SalesRank> <SalesRank> <ProductCategoryId>1033714</ProductCategoryId> <Rank>90</Rank> </SalesRank> <SalesRank> <ProductCategoryId>1033792</ProductCategoryId> <Rank>172</Rank> </SalesRank> </SalesRankings> with key SalesRank. It will match on SalesRankings!!!

		Jim, Newyork Apr 6, 2016
Try this one: Let ([ XML_Length = Length ( XML ); Attribute_Length = Length ( Attribute ); PosStartTag = If ( Position ( XML ; "<" & Attribute & " " ; 1 ; 1 ) > 0; Position ( XML ; "<" & Attribute & " "; 1; Instance ); Position ( XML ; "<" & Attribute & ">"; 1; Instance ) ); TagName = If ( Position ( XML ; "<" & Attribute & " " ; 1 ; 1 ) > 0; Middle ( XML ; PosStartTag ; Position ( XML ; ">" ; PosStartTag ; 1 ) +1 - PosStartTag ); "<" & Attribute & ">"); PosEndTag = Position ( XML ; "</" & Attribute & ">"; PosStartTag; 1 ); TheResult = Case( IsEmpty ( XML ) or IsEmpty ( Attribute ) or (PosStartTag = 0 ); // Attribute not in XML at all ""; Middle (XML; PosStartTag + Length ( TagName ); PosEndTag - (PosStartTag + Length ( TagName ))) ) ]; TheResult )

		Ricardo, Brazil Feb 26, 2017
Nice function, now, Im a noob... just wonder where I have to insert it... Can anyone help?

		Andy K, Milwaukee, WI Mar 30, 2017
Ricardo - You can go to File->Manage->Custom Functions... in FMP Advanced to add custom functions

		Tigrou83, FRANCE May 15, 2017
THANKS. A lot. Works wonderfully and made my FM life a lot simpler ! Instantly working. Great, great job and saved me hours. And thanks to this absolute best FM site.

		MB, Italy Apr 28, 2018
Nice work. If you need to deal with hierarchy, you can give a try to : http://xmlfm.bellinonet.com

		Nicolas Viellard Jul 23, 2019
Hi I had some problems with the redundant attributes ... Multiple identical attributes in the same XML file and function can not work! I modified the custom function to adapt to these XML files (strange but real). The modified function uses 2 new function attributes: indentation length and NewLine character (Char (10) or Char (13) or another character in FileMaker depending on the XML file). It can surely be improved but it seems to work : ExtractDataXML ( XML; Attribute; Instance; NL_Character ) = Let ( [ XML = XML ; Attribute = Attribute ; Instance = Instance ; NL_Character = NL_Character ; Attribute_Length = Length ( Attribute ) ; Get_Instance = If ( IsEmpty ( Instance ) ; 1 ; Instance ) ; Attribute_Position = Position ( XML ; "<" & Attribute & ">" ; 1 ; Get_Instance ) ; NL_Count = PatternCount ( Left ( XML ; Attribute_Position ) ; NL_Character ) ; PreviousNL_Position = Position ( XML ; NL_Character ; 1 ; NL_Count ) ; NextNL_Position = Position ( XML ; NL_Character ; 1 ; NL_Count + 1 ) ; Attribute_Line = Middle ( XML ; PreviousNL_Position + 1 ; NextNL_Position - PreviousNL_Position - 1 ) ; Indentation = Left ( Attribute_Line ; Length ( Attribute_Line ) - Attribute_Length - 2 ) ; StartLine_Position = Position ( XML ; Indentation & "<" & Attribute & ">" & NL_Character ; 1 ; 1 ) ; EndLine_Position = Position ( XML ; NL_Character & Indentation & "" ; 1 ; 1 ) ; Extraction = Middle ( XML ; StartLine_Position ; EndLine_Position + Length ( Indentation ) + Attribute_Length + 2 + 1 + 1 - StartLine_Position ) ] ; Extraction )

		Nicolas Viellard Jul 26, 2019
EDIT : Sorry, I made a mistake in the function. Here is the revised and simplified function (works on API NCBI "Gene-commentary"-awful XML, so it is robust!): Reminder: you must know the NewLine character and the attributes to extract must be alone on the line with indentation. ExtractDataXML ( XML; Attribute; Instance; NL_Character ) = Let ( [ Attribute_Length = Length ( Attribute ) ; Get_Instance = If ( IsEmpty ( Instance ) ; 1 ; Instance ) ; Attribute_Position = Position ( XML ; "<" & Attribute & ">" ; 1 ; Get_Instance ) ; NL_Count = PatternCount ( Left ( XML ; Attribute_Position ) ; NL_Character ) ; PreviousNL_Position = Position ( XML ; NL_Character ; 1 ; NL_Count ) ; XML_shortened = Right ( XML ; Length ( XML ) - PreviousNL_Position ) ; Attribute_Line = Left ( XML_shortened ; Position ( XML_shortened ; NL_Character ; 1 ; 1 ) ) ; Indentation = Left ( Attribute_Line ; Length ( Attribute_Line ) - Attribute_Length - 2 - 1 ) ; EndLine_Position = Position ( XML_shortened ; NL_Character & Indentation & "" ; 1 ; 1 ) ; Extraction = Middle ( XML_shortened ; 1 ; EndLine_Position + Length ( Indentation ) + Attribute_Length + 2 + 1 ) ] ; Extraction )

		Nicolas Viellard Jul 31, 2019
EDIT : Sorry, I made a mistake in the function. Here is the revised and simplified function (works on API NCBI "Gene-commentary"-awful XML, so it is robust!): Reminder: you must know the NewLine character and the attributes to extract must be alone on the line with indentation. ExtractDataXML ( XML; Attribute; Instance; NL_Character ) = Let ( [ Attribute_Length = Length ( Attribute ) ; Get_Instance = If ( IsEmpty ( Instance ) ; 1 ; Instance ) ; Attribute_Position = Position ( XML ; "<" & Attribute & ">" ; 1 ; Get_Instance ) ; NL_Count = PatternCount ( Left ( XML ; Attribute_Position ) ; NL_Character ) ; PreviousNL_Position = Position ( XML ; NL_Character ; 1 ; NL_Count ) ; XML_shortened = Right ( XML ; Length ( XML ) - PreviousNL_Position ) ; Attribute_Line = Left ( XML_shortened ; Position ( XML_shortened ; NL_Character ; 1 ; 1 ) ) ; Indentation = Left ( Attribute_Line ; Length ( Attribute_Line ) - Attribute_Length - 2 - 1 ) ; EndLine_Position = Position ( XML_shortened ; NL_Character & Indentation & "" ; 1 ; 1 ) ; Extraction = Middle ( XML_shortened ; 1 ; EndLine_Position + Length ( Indentation ) + Attribute_Length + 2 + 1 ) ] ; Extraction )

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 20 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: