topic Re: Xml Parsing in QlikView

Xml Parsing

marco_puccetti — Fri, 02 Oct 2015 12:47:08 GMT

Hello, i need to access, some XML text fields and get some values from it, is it possible?

If so, how can i do this?

Thanks

Marco

Re: Xml Parsing

Fri, 02 Oct 2015 12:54:03 GMT

yes

xml data can be retireved from either a file or web page.

In scripting on the Load, you can select table files, if the xml file is saved or you can select web page. If you slect table files, once you point to the file in the wizard, make sure the file type is XML

Re: Xml Parsing

Peter_Cammaert — Fri, 02 Oct 2015 12:55:47 GMT

Where do you want to get these XML fields from? A regular XML (data) file? Or an online XML source?

The QlikView script can read data files in XML format, after which you can pick up the values you want to keep.

Re: Xml Parsing

pamaxeed — Fri, 02 Oct 2015 12:57:46 GMT

Ciao Marco,

you can just use the wizard, load table file and choose xml type like below, you should get the parsed xml file in the preview window:

Cheers,

Patric

Re: Xml Parsing

marco_puccetti — Fri, 02 Oct 2015 13:08:23 GMT

I need to load dinamically Some Xml values from a table in which is not present only xml but also standard text and numerical values.

How can i do this?

Thanks

Marco

Re: Xml Parsing

Peter_Cammaert — Fri, 02 Oct 2015 13:11:01 GMT

Can you post an example of the table you want to load in this thread? XML-formatted files can vary wildly in syntax and layout, and a solution for one format may not always work for another.

Best,

Peter

Re: Xml Parsing

marco_puccetti — Fri, 02 Oct 2015 13:19:45 GMT

This is the table with the xml field.

Thanks

Marco

Re: Xml Parsing

Peter_Cammaert — Fri, 02 Oct 2015 13:32:12 GMT

Ah, I see...

What you could do is create a table import-column export QVW document that simply saves the column with XML-formatted data to an external file (for example a .csv with a single column and a column title that consists of the XML header lines). Next you only need to read this new file using the technique suggested before...

Best,

Peter

Re: Xml Parsing

marco_puccetti — Fri, 02 Oct 2015 13:37:11 GMT

I need to know if exists some function that allow me to get specific field inside the xml (XSL functions or other).

Thanks

Marco

Re: Xml Parsing

martynlloyd — Fri, 02 Oct 2015 13:57:48 GMT

Hi, you could use Replace() to strip off the XML tags:

Replace(

Replace(YourField,'VRCSM',''),

'<CDISPTROPR', '')

etc.

AS ValueInXML;

Or even better:

// 1 Build map of tags

XMLTag_Map:

MAPPING
LOAD DISTINCT
'<' & TextBetween('<' & SubField(XMLTEXT, '<', IterNo()),'<','>') & '>' as XMLTag,
'' as Substitute
Resident YourTable
While NOT IsNull(SubField(XMLTEXT, '<', IterNo()));

// 2 Use map to replace tags

...
,Trim(RTrim(Replace(
Replace( MapSubstring('XMLTag_Map', XMLTEXT),'&','&')
,'"','"'))) as XMLTEXT_Cleansed

Post the data and I'll make it for you.

kind regards, Marty.

Re: Xml Parsing

Fri, 02 Oct 2015 15:33:16 GMT

I was thinking of the same concept as Peter prior to seeing the file (have been in a meeting the last few hours). You can create a qvw that reads this file loads jsut the column with the xml and saves the file with just the one column.

You can then re-read the new file containing the single column and specify it contains xml - you could do this in teh same qvw or create another one so you are breaking out your tansformation process in the different qvws

Re: Xml Parsing

Fri, 02 Oct 2015 16:05:51 GMT

when you parse the file and specify exml, it should allow you to reference the specific fields within the laod statement

if you have or can crate a file with just the xml, it would be fairly simple to show

Re: Xml Parsing

marco_puccetti — Thu, 08 Oct 2015 09:57:21 GMT

So is only possible to use string manipulation to get the data.

Thanks

Marco

Re: Xml Parsing

martynlloyd — Fri, 09 Oct 2015 12:08:59 GMT

Yes Marco, there is no "field" type of XML, only a "file" type.

Take your pick from the solutions above.

Regards,

Marty.

Re: Xml Parsing

petter — Fri, 09 Oct 2015 12:12:11 GMT

No it is not only possible to use string manipulation but in some cases it is easier.