Parsing Non-Repeating XML (such as QuickBooks custom fields)

The Scribe Connector for Quickbooks exposes custom fields created in quickbooks but the data is put into a single field called " CustomFields" and the data is stored as XML with multiple fields being put into this single field. For example, if QuickBooks was customized to have 3 custom fields on the Customers entity: Information Officer, Expiration Date, Color the " CustomFields" field when accessed with Scribe Online would look like:

Information OfficerJohn DoeExpiration Date08/30/2013ColorBlue

The question then is, how do I access the individual fields from this XML data? One way to parse the data is to use Scribe Online's Regular Expression parsing to pull out the data. Use the formula below and customize for your implementation. You'll need to change the information of the red data to match your needs. In the example below, the data was coming from the " Customers.CustomFields" entity and will look for the custom field called " Color" .

REPLACEREGEX (REPLACEREGEX (MATCHREGEX (MATCHREGEX (Customers.CustomFields, " Color(.*?)" ), " (.*?)" ), " ^" , " " ), " \z" , " " )

If you run the above formula on the data set above, it will return the value " Blue"

Posted by Eric W. on Aug 21 2013 7:56PM { U2806 , F46 , T3241 , C10261 }

Login