topic Re: XML within a column - need to parse in App Development

XML within a column - need to parse

Anonymous — Fri, 10 Aug 2018 15:30:18 GMT

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?

Re: XML within a column - need to parse

petter — Sat, 11 Aug 2018 06:40:40 GMT

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.

Re: XML within a column - need to parse

petter — Sat, 11 Aug 2018 07:42:43 GMT

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.

Re: XML within a column - need to parse

Anonymous — Mon, 13 Aug 2018 08:17:13 GMT

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,

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.

Re: XML within a column - need to parse

Anonymous — Tue, 14 Aug 2018 11:02:30 GMT

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;

Re: XML within a column - need to parse

petter — Tue, 14 Aug 2018 17:46:10 GMT

I think

LOAD

.....

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

needs to be changed to

LOAD

[CPI RA],

AOI,

AV,

CV,

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

Re: XML within a column - need to parse

Anonymous — Wed, 15 Aug 2018 07:22:57 GMT

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.

Re: XML within a column - need to parse

petter — Wed, 15 Aug 2018 07:37:06 GMT

You're welcome - happy to help