Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Source XML
http://maps.googleapis.com/maps/api/geocode/xml?address=516,%20,%20,%20United%20States&sensor=false
With the following load statement, I am able to read the Status XML node as well as Result XML node.
The source XML contains multiple Result nodes. But it captures only the very first Result node.
LOAD
[status] AS GeoDataStatus,
[result/formatted_address] as FormattedAddress,
[result/geometry/location/lat] AS Latitude
FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=516,%20,%20,%20United%20States&sensor=false] (XmlSimple, Table is [GeocodeResponse]);
With below load statement I am able to read all Result nodes, but unable to read the Status node.
LOAD
[status] AS GeoDataStatus1,
[formatted_address] as FormattedAddress1,
[geometry/location/lat] AS Latitude1
FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=516,%20,%20,%20United%20States&se] (XmlSimple, Table is [GeocodeResponse/result]);
Please help me to read both Status and all Result nodes.
As far as QV is concerned, Status (one row) and Result (many rows) are two different tables that must be loaded with different LOAD statements and linked. The XML table wizard generates a link key for you to link the tables.
Of course, you don't want to issue the http request twice, because the contents are not guaranteed to be the same. The trick is to load once into a field and then parse the XML from the field.
Raw:
LOAD
concat(rawXml, chr(13) & chr(10),RecId) as rawXml
;
LOAD [@1:n] as rawXml
,recno() as RecId
FROM
[http://maps.googleapis.com/maps/api/geocode/xml?address=516,%20,%20,%20United%20States&se]
(fix, utf8)
;
result:
LOAD type,
formatted_address as FormattedAddress1,
[geometry/location/lat] as [Latitude1],
%Key_GeocodeResponse_62C7A0A69859D655 // Key to parent table: GeocodeResponse
FROM_FIELD (Raw, rawXml) (XmlSimple, Table is [GeocodeResponse/result])
;
GeocodeResponse:
LOAD status,
%Key_GeocodeResponse_62C7A0A69859D655 // Key for this table: GeocodeResponse
FROM_FIELD (Raw, rawXml) (XmlSimple, Table is [GeocodeResponse])
;
DROP TABLE Raw;
Gysbert's updated example attached.
-Rob
I can't reproduce your problem. I pasted your code for the Result nodes and it loads 20 records from the xml returned by the url you posted. See attached qvw.
As far as QV is concerned, Status (one row) and Result (many rows) are two different tables that must be loaded with different LOAD statements and linked. The XML table wizard generates a link key for you to link the tables.
Of course, you don't want to issue the http request twice, because the contents are not guaranteed to be the same. The trick is to load once into a field and then parse the XML from the field.
Raw:
LOAD
concat(rawXml, chr(13) & chr(10),RecId) as rawXml
;
LOAD [@1:n] as rawXml
,recno() as RecId
FROM
[http://maps.googleapis.com/maps/api/geocode/xml?address=516,%20,%20,%20United%20States&se]
(fix, utf8)
;
result:
LOAD type,
formatted_address as FormattedAddress1,
[geometry/location/lat] as [Latitude1],
%Key_GeocodeResponse_62C7A0A69859D655 // Key to parent table: GeocodeResponse
FROM_FIELD (Raw, rawXml) (XmlSimple, Table is [GeocodeResponse/result])
;
GeocodeResponse:
LOAD status,
%Key_GeocodeResponse_62C7A0A69859D655 // Key for this table: GeocodeResponse
FROM_FIELD (Raw, rawXml) (XmlSimple, Table is [GeocodeResponse])
;
DROP TABLE Raw;
Gysbert's updated example attached.
-Rob
Thanks for trying.
Please note the status column is empty.
Thank you so much.
This works.
It worked , i also tried with resident tables with fields with info like xml format (i just fixed with the data needed to comolete de format) and then used your logic , thanks rob
This is a sample about what i'm talking :
lasttrn:
load
useraccountkey,
timestamp(max(F1)) as TS
Resident data
group by useraccountkey;
left join
load
useraccountkey,
trxId,
F1 as TS,
'<?xml version="1.0" encoding="UTF-8"?>'&chr(13)&'<a>'&
textbetween(RQRS,'<transactionList>','</transactionList>')
&'</a>'
as RQRS
Resident data;
drop table data;
let t=NoOfRows('lasttrn');
for n=0 to $(t)-1
let vrawXml=peek('RQRS',$(n),'lasttrn');
let vtrxId=peek('trxId',$(n),'lasttrn');
let vuseraccountkey=peek('useraccountkey',$(n),'lasttrn');
let vTS=peek('TS',$(n),'lasttrn');
Raw:
LOAD
concat(rawXml, chr(13) & chr(10),RecId) as rawXml
;
LOAD RQRS as rawXml
,
recno()
//trxId
as RecId
resident lasttrn
where trxId=$(vtrxId);
;
result:
LOAD
'$(vtrxId)' as trxId,
'$(vuseraccountkey)' as vuseraccountkey,
'$(vTS)' as vTS,
id,
date,
description,
type,
amount,
referenceId,
memo,
store
FROM_FIELD (Raw, rawXml) (XmlSimple, Table is [a/transaction])
;
drop table Raw;
next n;
How can I make this works if I have multiple loads of xml? I mean, if the fieldname of the key changes (key_randomnumber)on each new XML load. Thanks in Advance!