Solved: XML within a column - need to parse - Qlik Community

Anonymous · ‎2018-08-10

Hi
I am trying to find some resources that will assist in parsing some XML that I have in a QVD. Can anyone help?

Attached is the XML that is contained within 1 cell, and I am trying to query it within QlikSense

Have tried loading the XML into a separate XML file;

RatingData:

Load

*

FROM [Policy.QVD] (qvd);

Store RatingData INTO RatingData.XML ;

But as you can see from below, the QlikSense load does not pick up the XML and create the tables;

LOAD

String%Table,

%Key_QvdFieldHeader_59D66ED49CFF179D

FROM [lib://Qlik Files/Extract\Peak\TWGInsurance_ForSFTP\Policy.QVD]

(XmlSimple, table is [QvdTableHeader/Fields/QvdFieldHeader/Tags/String]);

LOAD

"FieldName",

BitOffset,

BitWidth,

Bias,

NoOfSymbols,

Offset,

"Length",

"NumberFormat/Type",

"NumberFormat/nDec",

"NumberFormat/UseThou",

"NumberFormat/Dec",

"NumberFormat/Thou",

%Key_QvdTableHeader_B94FCCAC68ED3E20,

%Key_QvdFieldHeader_59D66ED49CFF179D

FROM RatingData.XML

(XmlSimple, table is [QvdTableHeader/Fields/QvdFieldHeader]);

LOAD

QvBuildNo,

CreatorDoc,

CreateUtcTime,

SourceFileSize,

"TableName",

RecordByteSize,

NoOfRecords,

Offset,

"Length",

"Lineage/LineageInfo/Discriminator",

"Lineage/LineageInfo/Statement",

%Key_QvdTableHeader_B94FCCAC68ED3E20

FROM RatingData.XML

(XmlSimple, table is QvdTableHeader);

Would really welcome some assistance please?

petter · ‎2018-08-14

I think

LOAD

.....

.......... table is [RatingData]);

needs to be changed to

LOAD

[CPI RA],

AOI,

AV,

CV,

V,

R,

C,

I,

Id

From_Field('DataTable','RD') (xmlSimple, table is [RatingData/CP]);

View solution in original post

petter · ‎2018-08-11

By making use of the a load statements FROM_FIELD instead of FROM, RESIDENT etc you can read data from an existing resident field as if the data in the field came from a file. Each and every single value would be treated like a single file from the file system. This also means that you can have the same format specifications of the "file" and specify that it is in XML format like with (XMLSimple, table is [vechicles/cars]).

It doesn't seem to be very fast - but if you understand that each and every single value represents a separate "file" a 1000 rows with a single column/field of XML would be interpreted by using FROM_FIELD as 1000 separate XML-files. They would of course be auto concatenated so one could believe that they might be like separate rows in a file - but they are not.

petter · ‎2018-08-11

Here is a simple example - I have used INLINE just to make it easier to read, but you might have the source file coming from a file by using FROM or of course also from a database by having a following SQL statement.

DATA: LOAD * INLINE `

# , XML , CSV

1, "<note>

<title>Strong and Brave</title>

<body>

This article is about a young woman, a little puppy and their journey

</body>

</note>

", "A;B;C;D

1;2;3;4

5;6;7;8"

` (txt,msq);

NOTES:

LOAD

title,

body

FROM_FIELD (DATA,XML) (xmlSimple, table is [note]);

FROM_CSV:

LOAD

*

FROM_FIELD(DATA,CSV) (txt, delimiter is ';', embedded labels);

So in this example we have a mixed format file with the top level content in regular CSV with commas as separators and two sub-formats of XML and CSV with semicolon as delimiter.

Anonymous · ‎2018-08-13

Hi Petter,

Thank you for your response. I am, however, having some issues.....

This is the loads that I am using;

Policy:

LOAD

ContractId,

RatingData

FROM Policy.QVD;

Premiums:

Load

PExIPT,

CDExIPT,

CP

From_Field (Policy,RatingData) (xmlSimple,table is [Policies]);

Contract ID is the unique identifier within the QVD, and RatingData contains the XML that was previously attached, but I am getting 0 returns.

If we pull the values within the RatingData field we have;

So you can see that it is in XML form.

If I write it directly on the SQL Server that the QVD is created from;

SELECT

P.ContractId

,P.RatingData.value('(RatingData/@PExIPT)[1]', 'MONEY')

,P.RatingData.value('(RatingData/@PIncIPT)[1]', 'MONEY') - P.RatingData.value('(RatingData/@PExIPT)[1]', 'MONEY')

,P.RatingData.value('(RatingData/CP/CPI[@I="true"]/@V)[1]', 'MONEY')

,P.AdminChargeValue

,P.RatingData.value('(RatingData/PA/PAItems/@V)[1]','MONEY')

These return the values.

I am just not getting the values from the load.

Anonymous · ‎2018-08-14

So, I have something that works;

XMLOutput:

Load

PIncIPT

,PExIPT

,PExcCExcIpt

From_Field('DataTable','RD') (xmlSimple, table is [RatingData])

Which is great for pulling data from;

Thank you

However, how do you get to the data in;

petter · ‎2018-08-14

I think

LOAD

.....

.......... table is [RatingData]);

needs to be changed to

LOAD

[CPI RA],

AOI,

AV,

CV,

V,

R,

C,

I,

Id

From_Field('DataTable','RD') (xmlSimple, table is [RatingData/CP]);

Anonymous · ‎2018-08-15

Hi Petter,

Thank you for your help

In the end I did;

XMLOutput:

Load

RId as RatingJoin,

PIncIPT as [Premium Inc IPT],

PExIPT as [Premium Ex IPT],

PIncIPT - PExIPT as IPT

From_Field('Policy','RD') (xmlSimple, table is [RatingData])

CPOutput:

Load

peek(RD,1,RId) as RatingJoin,

I as [True/False],

V as [Comm],

Id as ID

From_Field ('Policy','RD') (xmlSimple, table is [RatingData/CP/CPI])

where I = 'true'

to get the information that I needed.

petter · ‎2018-08-15

You're welcome - happy to help