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

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

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

Post by 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')

Code: Select all

<?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>[email protected]</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>

Code: Select all

<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>
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
BobK
Posts: 544
Joined: Thu Jan 31, 2008 2:14 pm
Location: Cincinnati, Ohio, USA

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

Post by BobK »

Jaymer wrote: Thu Aug 19, 2021 3:04 pm 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

Code: Select all

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

Code: Select all

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.
Bob
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

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

Post by 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.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
BobK
Posts: 544
Joined: Thu Jan 31, 2008 2:14 pm
Location: Cincinnati, Ohio, USA

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

Post by BobK »

Jaymer wrote: Fri Aug 20, 2021 3:38 pm 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: Fri Aug 20, 2021 3:38 pm 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.
Bob
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

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

Post by Jaymer »

BobK wrote: Fri Aug 20, 2021 5:03 pmI 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.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
BLOMASKY
Posts: 1470
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

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

Post by BLOMASKY »

You forgot to say "Pretty Please Jaymer"
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Gold Star for Bruce!

Post by Jaymer »

yes, bruce, you are teacher's pet.
you bring up the topic and its fixed the next day! Bravo!
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
aware_support
Posts: 7523
Joined: Sun Apr 24, 2005 12:36 am
Contact:

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

Post by 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
Aware IM Support Team
Post Reply