topic Reading multiple XML nodes in QlikView

Reading multiple XML nodes

Thu, 05 Feb 2015 06:55:34 GMT

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.

Re: Reading multiple XML nodes

Gysbert_Wassenaar — Wed, 11 Feb 2015 18:18:45 GMT

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.

Re: Reading multiple XML nodes

rwunderlich — Wed, 11 Feb 2015 20:19:09 GMT

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

http://masterssummit.com

http://robwunderlich.com

Re: Reading multiple XML nodes

Thu, 12 Feb 2015 05:54:54 GMT

Thanks for trying.

Please note the status column is empty.

Re: Reading multiple XML nodes

Thu, 12 Feb 2015 05:55:30 GMT

Thank you so much.

This works.

Re: Reading multiple XML nodes

hectorgarcia — Thu, 02 Jul 2015 14:53:14 GMT

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;

Re: Reading multiple XML nodes

AxNoxy — Thu, 18 Apr 2019 21:03:46 GMT

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!