7 Replies Latest reply on Aug 30, 2012 8:17 AM by MarcMcCall

    Extracting a portion of an XML String with "MiddleValues" ?

    MorkAfur

      Title

      Extracting a portion of an XML String with "MiddleValues" ?

      Post

      I have some XML where I want to extract just what's between the "<return>" and "</return>" elements.

      However, when I run the code below, I get nothing.

      MiddleValues ( $WSResult ; Position($WSResult; "<return>"; 1; 1) ; Position($WSResult; "</return>"; 1; 1) )

      The two position functions shown give 150 and 177 respectively in the debugger.

      If this function won't work for some reason (I don't see why based on the fmp12_functions_ref.pdf), how do you extract a substring from XML?

      Thanks in advance.

      -m

        • 1. Re: Extracting a portion of an XML String with "MiddleValues" ?
          philmodjunk

          Position returns character positions within the referenced text. MiddleValues works with values separated by return characters.

          Try using the Middle function instead of MiddleValues, like this:

          Middle ( $WSResult ; Position($WSResult; "<return>"; 1; 1) ; Position($WSResult; "</return>"; 1; 1) - Position($WSResult; "<return>"; 1; 1) + 9 )

          You may have to "tweak" this by using a different number instead of 9--it's easy to be off by one in the number of characters extracted by this function.

          • 2. Re: Extracting a portion of an XML String with "MiddleValues" ?
            MorkAfur

            Cool, thanks a bunch. I totally missed the "middle" function in the docs. That's exactly what I needed, though I'm not sure it's the best way to parse XML, but it works for now -- since I know the exact substring to extract.

            I see how you computed the number of characters to extract the text with too.

            I've used so many substring functions. Some what postion 1 and postion 2 (which is what I was assuming in my posting above) and others, like FMP I know now, want the start position and the number of characters to go from there.

            Thanks again....always appreciate your excellent replies.

            - m

            • 3. Re: Extracting a portion of an XML String with "MiddleValues" ?
              philmodjunk

              The functions are documented in Filemaker Help. A check or two there might save you the trouble of posting here. Wink

              And XML can be imported into a table, with a "grammar" to help parse the data so you might not need to use this calculation based approach to extract the data that you need.

              • 4. Re: Extracting a portion of an XML String with "MiddleValues" ?
                MorkAfur

                HA!

                I was looking at the documentation and missed the function!

                Need more coffee.... :)

                 

                Thanks,

                • 5. Re: Extracting a portion of an XML String with "MiddleValues" ?
                  MarcMcCall

                  There is also a custom function that is good for this too.

                  ExtractData( XML ; Attribute ; Instance )

                   

                  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 ) )
                     )
                  )
                  • 6. Re: Extracting a portion of an XML String with "MiddleValues" ?
                    MorkAfur

                    That's cool. Did you come up with that Marc?

                    Some documentation would be good since it's long and a bit cryptic, but I get the idea.

                    Thanks very much.

                    -m

                    • 7. Re: Extracting a portion of an XML String with "MiddleValues" ?
                      MarcMcCall

                      I got the CF from Briandunning.com

                      http://www.briandunning.com/cf/1

                       

                      I will note, I found a few problems I easily fixed depending on what your XML data looks like.  In certain cases the for me the XML doesnt always send me all of the nodes, because there is no data for that node.  It either gives me <Fax/> when no fax number is present or no Fax tag at all.  In cases like this, the function will give you the next instance it locates, which may move your data out of wack.  I did some extencive testing with my solution and worked with the Web Service developer so I could compensate for all scenarios the XML may show me.  here is a sample of how I fixed this in my solution.

                      Substitute(ViewAllCustomersUsers_result;

                      ["<tel />"; "<tel></tel>"] ;
                      ["<fax />"; "<fax></fax>"] ;
                      ["<mob />"; "<mob></mob>"] ;
                      ["<email />"; "<email></email>"] ;
                      ["<password />"; "<password></password>"] ;
                      ["</max_spend_date><administrator>";"</max_spend_date><max_spend_date_start>Null</max_spend_date_start><administrator>"];
                      ["</date_last_order><external_id /></ViewAllCustomersUsers>";"</date_last_order><external_id>Null</external_id></ViewAllCustomersUsers>"];
                      ["</date_last_order></ViewAllCustomersUsers>";"</date_last_order><external_id>Null</external_id></ViewAllCustomersUsers>"];
                      ["</date_last_login><external_id>";"</date_last_login><date_last_order>Null</date_last_order><external_id>"];
                      ["</date_last_login><external_id />";"</date_last_login><date_last_order>Null</date_last_order><external_id>Null</external_id>"];
                      ["</date_last_login></ViewAllCustomersUsers>";"</date_last_login><date_last_order>Null</date_last_order><external_id>Null</external_id></ViewAllCustomersUsers>"];
                      ["</date_registered><external_id>";"</date_registered><date_last_login>Null</date_last_login><date_last_order>Null</date_last_order><external_id>"];
                      ["</date_registered><external_id/>";"</date_registered><date_last_login>Null</date_last_login><date_last_order>Null</date_last_order><external_id>"];
                      ["</date_registered></ViewAllCustomersUsers>";"</date_registered><date_last_login>Null</date_last_login><date_last_order>Null</date_last_order><external_id>Null</external_id></ViewAllCustomersUsers>"];
                      ["</date_registered><external_id />";"</date_registered><date_last_login>Null</date_last_login><date_last_order>Null</date_last_order><external_id>Null</external_id></ViewAllCustomersUsers>"]
                      )
                       
                      I set this to an auto relpace calculation so when the XML is retrieved by the plugin and script, the XML is inserted into the field, the above calculation looks for the missing tags, and places the
                      missing tags in the correct locations so my data doesnt get out of wack.  You'll notice I added the word "Null", I simply added that for myself, to make it an easer searchable item.