Brian Dunning's FileMaker Custom Functions

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   Andy Knasinski
NRG Software
http://www.nrgsoft.com/

Share on Facebook Share on Twitter

  Sample input:
ExtractData ( "
123
456
789
" ; "InvNum" ; 2 )
  Sample output:
456

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

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

 

Comments

gus ohouo   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   Charlie Bailey, Northampton MA
Jan 10, 2011
Andy,

Nice function - very simple - gets the job done!
 
Daniel A. Shockley   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   Anannyaap, India
Apr 14, 2014
Really helpful.
 
Jim   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   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   Ricardo, Brazil
Feb 26, 2017
Nice function, now, Im a noob... just wonder where I have to insert it... Can anyone help?
 
Andy K   Andy K, Milwaukee, WI
Mar 30, 2017
Ricardo - You can go to File->Manage->Custom Functions... in FMP Advanced to add custom functions
 
Tigrou83   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   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   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   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   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: