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
 

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.

Under construction. Email me your wish list for improvements.