GetXmlContent ( theXml ; theNodeName ; theInstanceNumber )

Extract data from XML snippet which may contain attributes and/or erroneous partial matches

Average rating: 4.7 (26 votes) Log in to vote

JT - Show more from this author

Sample input:

GetXmlContent ( theXml ; "test" ; 2 )

Where theXml =

123
Norm
456
789

Sample output:

456

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

Case (
	// IF EMPTY PARAMETER(S)
	IsEmpty ( theXml )
	or IsEmpty ( theNodeName ) ;
		"";	// RETURN

	Let ( [
		RARETEXT = "XYZZY";	// REPLACE WITH RARER TEXT IF NOT SUFFICIENT
		~lengthRareText = Length ( RARETEXT );

		TAG_OPEN = Char ( 60 );		// <
		TAG_CLOSE = Char ( 62 );	// >
		SPACE = Char ( 32 );		// " "

		~instanceNumber = If ( IsEmpty ( theInstanceNumber ) ; 1 ; theInstanceNumber );

		~nodeNameTagBegin = TAG_OPEN & theNodeName;				// e.g., "<DATA"
		~nodeNameTagWithAttributeBegin = ~nodeNameTagBegin & SPACE;		// e.g., "<DATA "
		~nodeNameTagsWithoutAttribute = ~nodeNameTagBegin & TAG_CLOSE;		// e.g., "<DATA>"

		~xmlTransformed =
			Substitute (
				theXml ;
				[ ~nodeNameTagsWithoutAttribute ; RARETEXT & TAG_CLOSE ] ;
				  [ ~nodeNameTagWithAttributeBegin ; RARETEXT ]
			);

		~positionNodeTest = Position ( ~xmlTransformed ; RARETEXT ; 1 ; ~instanceNumber );
		~nodeExists = True = (~positionNodeTest ≠ 0)
	];
		Case (
			not ~nodeExists ;
				"" ;	// RETURN 			// REPLACE WITH ERROR MESSAGE, IF ONE DESIRED

			 Let ( [
				SLASH = Char ( 47 );	// /

				~positionCharacterFollowingRareText = ~positionNodeTest + ~lengthRareText;
				~nodeContainsAttribute = True =
					Let ( ~characterFollowingRareText = Middle ( ~xmlTransformed ; ~positionCharacterFollowingRareText ; 1 );
						(~characterFollowingRareText ≠ TAG_CLOSE)
					);

				~positionNodeContentBegin = 
					If ( ~nodeContainsAttribute ;
						Position ( ~xmlTransformed ; TAG_CLOSE ; ~positionCharacterFollowingRareText ; 1 ) + 1 ;
					// Else
						~positionCharacterFollowingRareText + 1
					);

				~nodeNameTagEnd = Substitute( ~nodeNameTagsWithoutAttribute ; TAG_OPEN ; TAG_OPEN & SLASH );
				~positionNodeContentEnd = Position ( ~xmlTransformed ; ~nodeNameTagEnd ; ~positionNodeContentBegin ; 1 )
			];
				// IF INCOMPLETE NODE ( HAS NO ENDING TAG )
				If ( ~positionNodeContentEnd = 0 ;
					"ERROR: INVALID NODE" ;	// RETURN
				// Else
					Middle ( ~xmlTransformed ; ~positionNodeContentBegin ; ~positionNodeContentEnd - ~positionNodeContentBegin )
				)
			)
		)
	)
)

This function was inspired by Andy Knasinski's ExtractData ( http://www.briandunning.com/cf/1 ) and expanded to account for erroneous partial-match error returns and multiple matching instances which may or may not contain attributes.

Comments

		comment, VR Feb 17, 2015
It is a common misconception that XML can be easily and relaible paresed using text function. Consider the folowing example: <xml> <test>one</test> <test xmlns="urn:X-ample:1">two</test> <test>the real two</test> </xml> What is the value of the second occurrence of element named "test"? The above CF tells us it is "two", but the correct answer is "the real two" - compare: http://www.utilities-online.info/xpath/?save=10882c1b-0ee1-4468-bb20-6a49ffee330e-xpath#.VOL3IYURJFQ

		john renfrew, attitude Feb 25, 2015
This: ~positionNodeTest = Position ( ~xmlTransformed ; RARETEXT ; 1 ; theInstanceNumber ); should be: ~positionNodeTest = Position ( ~xmlTransformed ; RARETEXT ; 1 ; ~instanceNumber );

		JT Feb 25, 2015
Fixed, thanks John. I noticed that earlier but hadn't a chance to fix it yet. comment, good point about the namespacing. But for my purposes at least, "two" would be the correct result. Caveat Functioner, I guess.

		Bruce Robertson Apr 13, 2015
Does not compile as written. There is no Null function. Case ( // IF EMPTY PARAMETER(S) IsEmpty ( theXml ) or IsEmpty ( theNodeName ) ; Null; // RETURN

		JT Apr 13, 2015
Fixed. Thanks, Bruce!

		Robert D Trammel, Lucky Penny LLC Nov 2, 2023
8 years later and still working great. Thanks for this.

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

GetXmlContent ( theXml ; theNodeName ; theInstanceNumber )

Comments

Support this website.

Support this website.