Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Reading multiple XML nodes

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.

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

6 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

Thanks for trying.

Please note the status column is empty.

Not applicable
Author

Thank you so much.

This works.

hectorgarcia
Partner - Creator III
Partner - Creator III

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;

AxNoxy
Contributor III
Contributor III

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!