topic Re: How to parse a subtag in a xml using Qlikview? in QlikView

How to parse a subtag in a xml using Qlikview?

Wed, 25 Nov 2020 16:16:04 GMT

I have a xml under which there is another tag. For example

<p>The details of this book can be obtained from

<url href="https://xyz.org"> Founders Association </url>. The book has captured the <url href="https://xyz1.org"> XYZ Publisher</url> attention of the audience </p>.

I tried to load the <p> tag using

Load p

from abc.xml

The result it shows is as follows:

The details of this book can be obtained from The book has captured the attention of the audience

But my desired result should be:

The details of this book can be obtained from Founders Association.The book has captured the XYZ Publisher attention of the audience.

Can someone help me out to achieve this?

Re: How to parse a subtag in a xml using Qlikview?

adamdavi3s — Mon, 20 Feb 2017 10:23:11 GMT

Can you share the xml?

The structure will matter in trying to figure this out I think

Re: How to parse a subtag in a xml using Qlikview?

Mon, 20 Feb 2017 11:42:55 GMT

Hi Adam,

It is not possible to share the xml but the structure I have given you is the similar to that of the original xml. I am not bothered about the data in other tags.My only concern is to fetch all the text inside the <p> tag. The only difference is that some of the <p> tag doesn't contain an <url> tag whereas in some cases it contains one and in some two <url> tags. I cannot fetch the text in between the <url> tag. The data fetched through my process is as shown above.

Regards,

Arghya Ray

Re: How to parse a subtag in a xml using Qlikview?

jonathandienst — Mon, 20 Feb 2017 11:50:12 GMT

You haven't really given us very much to work on. A few more examples, as well as information on how the XML is delivered (are they fragments from a database, from a text file, from a web page); and your script that is not working. Without these we are only able to guess at a solution (and Sherlock Holmes has not logged in recently).

Re: How to parse a subtag in a xml using Qlikview?

adamdavi3s — Mon, 20 Feb 2017 11:53:50 GMT

gah I missed a trick there with my username

Re: How to parse a subtag in a xml using Qlikview?

adamdavi3s — Mon, 20 Feb 2017 11:55:59 GMT

Hi Arghya,

Hmm I wonder if it might be easier to parse it as text you see, but without the xml its really hard to know if that will work.

Can you just provide the XML with two or three lines (plus any header etc) with the data made suitable for sharing?

Re: How to parse a subtag in a xml using Qlikview?

adamdavi3s — Mon, 20 Feb 2017 12:06:35 GMT

text parsing is dirty but it gets what you want

load:
LOAD @1
FROM

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

test:
load
replace(replace(replace(replace(replace(replace(replace(@1,TextBetween(@1,'<url','>'),''),'<url>',''),textbetween(replace(replace(@1,TextBetween(@1,'<url','>'),''),'<url>',''),'<url','>'),''),'<url>',''),'<p>',''),'</p>',''),'</url>','') as cleaned1
resident load;

Re: How to parse a subtag in a xml using Qlikview?

Mon, 20 Feb 2017 14:15:09 GMT

Hi Adam,

Please find the format of the xml below:

<item>

<title>Acknowledgements</title>

<p>There was a time<url href="xyz.org">xyz.org</url> Founders Association <url href="abc.org">abc.org</url>during ages</p>

</section>

<title>Acknowledgements1</title>

</section>

<title>Acknowledgements2</title>

<p>The glory<url href="badumtiss.org">badumtiss.org</url>leads to<url href="badumtiss1.org">badumtiss1.org</url>success for all</p>

</section>

<title>Acknowledgements3</title>

<p>Chase your dreams<url href="tukur.org">tukur.org</url>And one day it will lead you to victory</p>

</section>

<title>Acknowledgements4</title>

<p>The path to enlightment is to know your inner self</p>

</section>

</item>

Thanks and regards,

Arghya

Re: How to parse a subtag in a xml using Qlikview?

adamdavi3s — Mon, 20 Feb 2017 14:24:34 GMT

This one seems to work straight up I am afraid, Qlik just pulls it straight in using the XML import for <p>

Assuming this is what you want of course

Re: How to parse a subtag in a xml using Qlikview?

Tue, 21 Feb 2017 11:57:40 GMT

Hi Adam,

The data set you have provided is not correct.

My desired result should be:

Chase your dreams tukur.org And one day it will lead you to victory

Sa Re Ga bla.bla Pa Dha bla1.org Ni Sa

The glory badumtiss.org leads to badumtiss1.org success for all

The path to enlightment is to know your inner self

There was a time bla.bla Founders Association bla1.org during ages

Re: How to parse a subtag in a xml using Qlikview?

adamdavi3s — Tue, 21 Feb 2017 12:05:15 GMT

Ok that is what I wasn't clear on, I'll see what I can come up with

Re: How to parse a subtag in a xml using Qlikview?

Tue, 21 Feb 2017 13:08:18 GMT

Thanks Adam. Your help will be highly appreciated.

Hope to get the solution from you.

Re: How to parse a subtag in a xml using Qlikview?

adamdavi3s — Tue, 21 Feb 2017 13:55:23 GMT

This is a very specific parser but hopefully you can take it from here to finalise any tweaks.

load:

LOAD @1
FROM

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

processing:
load cleaned1,WildMatch(cleaned1,'*<*') as match;
load
replace(replace(replace(replace(replace(replace(replace(@1,TextBetween(@1,'<url','>'),''),'<url>',' '),textbetween(replace(replace(@1,TextBetween(@1,'<url','>'),''),'<url>',''),'<url','>'),''),'<url>',''),'<p>',''),'</p>',''),'</url>',' ') as cleaned1
resident load;

final:
load cleaned1
resident processing
where match=0;
drop tables load, processing;

I also found this thread which may or may not help, I didn't have time to play

Generic XML Import

Re: How to parse a subtag in a xml using Qlikview?

sasiparupudi1 — Tue, 21 Feb 2017 13:55:51 GMT

Try the following script please

Load

PField,

Replace(PField1,TextBetween(PField1, '>',' ')&'>','') as PField1;

Load

PField,

Replace(PField1,TextBetween(PField, '>',' ')&'>','') as PField1 ;

Load

PField,

Replace(PField, '"','') as PField1 ;

Load

Replace(Replace(PField,'</url>',' '),'<url href=',' ') as PField;

Load

If(Index(Lower(@1),'<p>')>0,TextBetween(@1,'<p>','</p>')) as PField

FROM

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

Re: How to parse a subtag in a xml using Qlikview?

Thu, 23 Feb 2017 11:47:40 GMT

Hi Adam,

Thank You so much for your help. But still I need your help to solve another problem related to this matter. Please find here a sample XML. It contains tags like <doi>,<p> etc. as follows:

<item>

<p>This is an open access article under features of the

<url href="http://allopen.org/licenses/ab-bc-cd/4.2/">Creative Features Attribution-NonCommercial-NoDerivs</url> License, which permits use and distribution in any medium, made realistic.</p></legalStatement>

<lstype>creativefeaturesab-bc-cd</lstype>

<lsurl>http://allopen.org/licenses/by-nc-nd/4.0/</lsurl>

<fundingAgency>Medica Group, Inc.</fundingAgency>

</fundingInfo>

<title type="main">Acknowledgments</title>

<p>The men would like to thank XYZ, Peter Shetty, and Heather Connell CCRP for their assistance and their efforts in coordinating the study. Epiflix VLU Management Group included: David Warner, DPM, USA, FL.</p>

<p>

<i>Source of Funding</i>: This study was sponsored and funded by Medica Group, Inc., Sancez, JD.</p>

<p>

<i>Conflicts of Interest</i>: Hero has provided consultative services to Medica and has been a source of fire to us that has provided consultative services to Medica. All other contributors have no committments to disclose.</p>

</section></item>

<item>

<p>This is an open access article under features of the

<lstype>Lease</lstype>

<lsurl>http://pqr.org/licenses/pq-qr-st/4.9/</lsurl>

<title type="main">Acknowledgments</title>

<p xml:id="ess257-para-0064">All raw image data are available on the portals of the organisation

<url href="http://klo.dfr.scf.gov">klo.dfr.scf.gov</url>. In addition, the experts are researching about the matter that are provided through

<url href="http://rew.dse.xzc.gov">rew.dse.xzc.gov</url>. This work was carried out at the society of fine arts GFDR.</p>

</section></item>

Now my objective is to fetch all the text present inside the <p> tag for each doi because I have to join this field with another table which has doi as its primary key.

Therefore my result should be:

1) for doi ABC/1234, the result is: The men would like to thank XYZ, Peter Shetty, and Heather Connell CCRP for their assistance and their efforts in coordinating the study. Epiflix VLU Management Group included: David Warner, DPM, USA, FL. Source of Funding. : This study was sponsored and funded by Medica Group, Inc., Sancez, JD. Conflicts of Interest : Hero has provided consultative services to Medica and has been a source of fire to us that has provided consultative services to Medica. All other contributors have no committments to disclose.

2) for doi PQR/45678, the result is: All raw image data are available on the portals of the organisation klo.dfr.scf.gov . In addition, the experts are researching about the matter that are provided through rew.dse.xzc.gov . This work was carried out at the society of fine arts GFDR.

NOTE: <p> tag only inside the <section> tag is required.

Can you please help me achieve this.

Thanks and regards,

Arghya