How can I easily extract a value from a XML/JSON structure?

Jaymer

Some of you may know that Aware has a nifty way to process a JSON response from a REST service.
It can take a bit of work to figure out the correct schema to hold an intricate reply, but it CAN be done.
Below is a SIMPLE XML result, and I'd like to examine an alternate method to extract data.

Instead of Aware adding records to a physical table, I'm wondering about getting the result back into a single variable.
Then parsing that for what i want.
In this SIMPLE example, say I want the CITY of the LegalAddr.

Basically: CompanyInfo.LegalAddr.City = San Pablo3
If I only needed this for a specific immediate reason, seems a lot simpler to request the data and extract the City rather than run a process to read physical records to get the city. BOTH might be fast/immediate, but its night and day under the hood w.r.t. in-memory vs. physical IO.

Given that I would always know a path to the desired element, is it possible for a simple REGEX function to return my element?
What about an array? See the 2nd example... what if I wanted the UnitPrice of ID# 11. (the Pump, $15)? Can I get that with a REGEX?

In JS/Python (and others), there's all sorts of libraries to process this stuff? i wonder if calling a Java function would work better?
example: SessionVar.thePrice = FindXML(SessionVar.theString, 'CompanyInfo.LegalAddr.City')

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<IntuitResponse
	xmlns="http://schema.intuit.com/finance/v3" time="2021-08-19T07:35:02.350-07:00">
	<CompanyInfo domain="QBO" sparse="false">
		<Id>1</Id>
		<SyncToken>11</SyncToken>
		<MetaData>
			<CreateTime>2021-04-21T13:36:13-07:00</CreateTime>
			<LastUpdatedTime>2021-08-12T13:48:56-07:00</LastUpdatedTime>
		</MetaData>
		<CompanyName>Test Acct</CompanyName>
		<LegalName>Test Acct</LegalName>
		<CompanyAddr>
			<Id>1</Id>
			<Line1>123 Sierra Way</Line1>
			<City>San Pablo1</City>
			<Country>US</Country>
			<CountrySubDivisionCode>CA</CountrySubDivisionCode>
			<PostalCode>87999</PostalCode>
		</CompanyAddr>
		<CustomerCommunicationAddr>
			<Id>1</Id>
			<Line1>123 Sierra Way</Line1>
			<City>San Pablo2</City>
			<Country>US</Country>
			<CountrySubDivisionCode>CA</CountrySubDivisionCode>
			<PostalCode>87999</PostalCode>
		</CustomerCommunicationAddr>
		<LegalAddr>
			<Id>1</Id>
			<Line1>123 Sierra Way</Line1>
			<City>San Pablo3</City>
			<Country>US</Country>
			<CountrySubDivisionCode>CA</CountrySubDivisionCode>
			<PostalCode>87999</PostalCode>
		</LegalAddr>
		<PrimaryPhone></PrimaryPhone>
		<CompanyStartDate>2021-04-21</CompanyStartDate>
		<FiscalYearStartMonth>January</FiscalYearStartMonth>
		<Country>US</Country>
		<Email>
			<Address>noreply@quickbooks.com</Address>
		</Email>
		<WebAddr></WebAddr>
		<SupportedLanguages>en</SupportedLanguages>
		<NameValue>
			<Name>NeoEnabled</Name>
			<Value>true</Value>
		</NameValue>
		<NameValue>
			<Name>IsQbdtMigrated</Name>
			<Value>false</Value>
		</NameValue>
		<NameValue>
			<Name>CompanyType</Name>
			<Value>Other</Value>
		</NameValue>
		<NameValue>
			<Name>SubscriptionStatus</Name>
			<Value>SUBSCRIBED</Value>
		</NameValue>
		<NameValue>
			<Name>OfferingSku</Name>
			<Value>QuickBooks Online Plus</Value>
		</NameValue>
		<NameValue>
			<Name>PayrollFeature</Name>
			<Value>false</Value>
		</NameValue>
		<NameValue>
			<Name>AccountantFeature</Name>
			<Value>false</Value>
		</NameValue>
		<NameValue>
			<Name>QBOIndustryType</Name>
			<Value>Landscaping Services</Value>
		</NameValue>
		<NameValue>
			<Name>ItemCategoriesFeature</Name>
			<Value>true</Value>
		</NameValue>
		<NameValue>
			<Name>AssignedTime</Name>
			<Value>2021-08-12T14:01:56-07:00</Value>
		</NameValue>
	</CompanyInfo>
</IntuitResponse>

<IntuitResponse xmlns="http://schema.intuit.com/finance/v3" time="2021-08-19T08:00:00.476-07:00">
    <QueryResponse startPosition="1" maxResults="5">
        <Item sparse="true">
            <Id>
                25
            </Id>
            <Name>
                Beam, large, 20ft
            </Name>
            <UnitPrice>
                250
            </UnitPrice>
        </Item>
        <Item sparse="true">
            <Id>
                11
            </Id>
            <Name>
                Pump
            </Name>
            <UnitPrice>
                15
            </UnitPrice>
        </Item>
        <Item sparse="true">
            <Id>
                5
            </Id>
            <Name>
                Rock Fountain
            </Name>
            <UnitPrice>
                275
            </UnitPrice>
        </Item>
        <Item sparse="true">
            <Id>
                16
            </Id>
            <Name>
                Sprinkler Heads
            </Name>
            <UnitPrice>
                2
            </UnitPrice>
        </Item>
        <Item sparse="true">
            <Id>
                17
            </Id>
            <Name>
                Sprinkler Pipes
            </Name>
            <UnitPrice>
                4
            </UnitPrice>
        </Item>
    </QueryResponse>
</IntuitResponse>

BobK

Jaymer wrote
Given that I would always know a path to the desired element, is it possible for a simple REGEX function to return my element?

Depends upon how you define simple.

Using the examples you provided, the following will get the elements you want.

Given:
BO = ComplexRegEx
Your first XML example stored in ComplexRegEx.FirstXML
Your second XML example stored in ComplexRegEx.SecondXML

The following rules will extract the required values

Rule1

ComplexRegEx.City=REPLACE_PATTERN(REPLACE_PATTERN(REPLACE_PATTERN(ComplexRegEx.FirstXML, CR(), ''), '^.*\<LegalAddr\>.*\<City\>', ''), '\</City\>.*$', '')

Rule2

ComplexRegEx.UnitPrice=TRIM(REPLACE_PATTERN(REPLACE_PATTERN(REPLACE_PATTERN(ComplexRegEx.SecondXML, CR(), ''),'^.*Pump.*?\<UnitPrice\>',''),'\</UnitPrice\>.*$',''))

Results
ComplexRegEx.City = San Pablo3
ComplexRegEx.UnitPrice=15

Explanation:
Each rule executes the REPLACE_PATTERN 3 time.

The first REPLACE_PATTERN removes all new line symbols because RegEx works on a line by line bases. If the actual XML comes in 1 long line, this is not needed.

The second REPLACE_PATTERN removes everything before the required value

The third REPLACE_PATTERN removes everything after the required value. Leaving only the required value left.

Note: the FirstXML and SecondXML fields are not changed by these rules.

Jaymer

thx - ok, now I understand that strategy

1) It def does not come back from the service provider with CRs
2) Is it easier, harder, the same to do this with a JSON string?
3) Given :
a) we will have a consistent structure in the return string, AND
b) considering that a more complex example might find our desired data buried another 1 or 2 levels deeper, AND
c) I can choose the response to be XML or JSON,

I'd say this method wins BIG TIME over the newer aware approach of physically writing this data to physical storage and then using a process to re-read the data. Esp. with new SessionVars to pass/set parms to a RegEx searching process there's a lot of potential.

BobK

Jaymer wrote
2) Is it easier, harder, the same to do this with a JSON string?

My first thought was that the search would be easier in XML because there are XML tags before and AFTER the searched for value. But, as I typed my previous post I realized that after everything before the value has been removed, the first double quote (") in the remaining string is the double quote after the value. So you just need to delete the first double quote and everything after it. The only issue with that is that AwareIM will not allow double quotes in rules, so you have to search using the ASCII hex value of the double quote, which is: \x22.

Jaymer wrote
b) considering that a more complex example might find our desired data buried another 1 or 2 levels deeper,

I do not think the number of levels matters, the real issue would be if multiple values have the same labels. This issue would be present if the data was in JSON or XML format. It should still be doable, but the RegEx would be more complex. Look at rule1 in my previous post. LegalAddr was included in the RegEx to find the correct City. If there had been another City after the LegalAddr City, the above RegEx would have found that city instead of the correct City. A small change to the RegEx would be needed to find the correct City.

Jaymer

BobK wrote
I do not think the number of levels matters...

I only mentioned that for comparison... because if there are more levels, thats extra BOs that you have to create to store the hierarchy in the return string using Aware's method... gets to be a PITA.
So if we use this method to search a string, then even better, the # of levels doesn't matter a bit.

BLOMASKY

You forgot to say "Pretty Please Jaymer"

Jaymer

yes, bruce, you are teacher's pet.
you bring up the topic and its fixed the next day! Bravo!

aware_support

We have just released build 2938 which supports PARSE_JSON and PARSE_XML functions

Examples:
Object.Value = PARSE_JSON (Object.JSONAttribute, 'JSON_PATH')
where JSON_Path follows the format described here https://github.com/json-path/JsonPath#getting-started

Object.Value = PARSE_XML (Object.XMLAttribute, 'XPATH')
where XPath follows the format described here https://www.w3.org/TR/1999/REC-xpath-19991116