I think this will work. Position returns 0 if the specified instance cannot be found so you can check the value of that result and return "" if that's the case.
Let ( [
XML_Length = Length ( XML ) ;
Attribute_Length = Length ( Attribute ) ;
Get_Instance = If ( IsEmpty ( Instance ) ; 1 ; Instance ) ;
Att_Pos = Position ( XML ; "</" & attribute & ">" ; 1 ; Get_Instance )
IsEmpty ( XML ) or IsEmpty ( attribute ) or Att_Pos = 0 ; "" ;
Middle ( XML ; Position ( XML ; "<" & attribute & ">" ; 1 ; Get_Instance ) + attribute_length + 2 ;
xml_length - ( xml_length - Att_Pos ) - ( Att_Pos + Attribute_Length + 2 ) )
I copied that straight into the function, and tried it, and it returns all fields empty.
This looks a bit curious in your original function:
Position ( XML ; "</" & attribute & ">" ; 1 ; Get_Instance )
Is that / correct or a typo?
I completely missed it the first time around....
It might help to post a sample of your XML data.
I found the problem. Its not the custom function, it is the XML data set. What is happening is that if the field is empty on the site, the XML doesnt return the attribute in that data set, and the function is extracting the instance it is told to, it just happens to be from what would be a different account.
Have you tried doing an import of this XML data into a table in your database? That strikes me as an approach that might be much easier to deal with.
Let me see if I Have this straight:
You want all the data between the lines starting with:
(See where I'm headed with this in the CF?)
the <ViewAllCustomers diffgr:id="ViewAllCustomers1" msdata:rowOrder="0"> is the start of the first record, and </ViewAllCustomers is the end of a record. then the next record will be <ViewAllCustomers diffgr:id="ViewAllCustomers2" msdata:rowOrder="1"> and end with </ViewAllCustomers so on and so on....
No, I havent tried importing, I have a plugin "WinFmNexWeb" that access the web service of the website and enters the XML response into the field "result" then I simply change the EventNumber field, to view the next data set from the XML. this is where I was going to have the script loop through and set variables and goto another layout and create an individual record from the parsed data, till I ran into my problem. I discected a sample file I found and addapted it for my purposes.
Most of what I use it for is to actually post data to the site, but I am trying to make this work, so I can update my solution with the website generated data for the accounts, IE, customer #'s and other ID#, generated by the site.
It might be an interesting experiment to use Export FIeld Contents to export this data to a file and then do an xml import into a temp table in your database. I haven't messed around with XML imports, but it sounds worth a look.
I don't have time to write the CF at this time, but what I'm thinking here is to set up the calculation to look for
First, then find the next < as the start of the data to be parsed, with
as the flag marking the end of the text to be parsed.
I figured out an easy solution. I used an auto replace calculation. To add the elements if they arent in the XML, and this will add them in every data set in the XML if there is no data coming from the site.
["<tel />"; "<tel></tel>"] ;
["<fax />"; "<fax></fax>"] ;
["<mob />"; "<mob></mob>"] ;
["<email />"; "<email></email>"] ;
["<password />"; "<password></password>"] ;
["<internal_account />"; "<internal_account></internal_account>"] ;
["</password><points_active>"; "</password><internal_account></internal_account><points_active>"] ;
["</active></ViewAllCustomers>"; "</active><external_id></external_id></ViewAllCustomers>"] ;
["<external_id />"; "<external_id></external_id>"]