Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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.

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Reading multiple XML 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

http://masterssummit.com

http://robwunderlich.com

View solution in original post

6 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Reading multiple XML nodes

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
Highlighted
MVP & Luminary
MVP & Luminary

Re: Reading multiple XML 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

http://masterssummit.com

http://robwunderlich.com

View solution in original post

Highlighted
Not applicable

Re: Reading multiple XML nodes

Thanks for trying.

Please note the status column is empty.

Highlighted
Not applicable

Re: Reading multiple XML nodes

Thank you so much.

This works.

Highlighted
Partner
Partner

Re: Reading multiple XML nodes

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;

Highlighted
Contributor III
Contributor III

Re: Reading multiple XML nodes

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!