topic Re: Import XML from MySQL with generic name tags in QlikView

Import XML from MySQL with generic name tags

Thu, 07 Apr 2016 04:17:31 GMT

How can I import an XML dump from MySQL in the following format:

<?xml version="1.0"?>

<row>

<field name="name">ExampleName</field>

<field name="address">Some sample street 5</field>

<field name="city">Sampletown</field>

</row>

</resultset>

Note that the column tags are all named 'field' with an attribute 'name' containing the actual name.

I found some sample script to import this into Qlik, but it relied on iterating numerically over all columns and I cannot rely on the order of the fields, so that probably wont work.

Re: Import XML from MySQL with generic name tags

marcus_sommer — Thu, 07 Apr 2016 07:18:28 GMT

Maybe this is helpful: Re: Loading attribute from a specific section in XML file

- Marcus

Re: Import XML from MySQL with generic name tags

Thu, 07 Apr 2016 07:28:24 GMT

Thanks! Read it, but doesn't really seem to be relevant to my question. The structure in that question is much more exotic but also less generic with differently named tags.

Re: Import XML from MySQL with generic name tags

jonathandienst — Thu, 07 Apr 2016 08:18:44 GMT

I assume that each 'record' is contained inside a <row></row> element in <resultset>. If that is the case, you should be able to iterate over the <row> elements and load a record at a time. That should work, but may not scale very well for a file with a large number of <row> elements.

I have some other ideas but would need some tests (involving from-field and generic loads) for a load method that will scale better.

Re: Import XML from MySQL with generic name tags

jonathandienst — Thu, 07 Apr 2016 09:19:09 GMT

I gave up trying to use the XMLSimple load protocol to get this file (has anyone ever used XmlSax?). This is the script I used:

T1:
LOAD *,
  If(row like '<row>', Alt(Peek(recID), 0)+1, Alt(Peek(recID), 0)) as recID,
  If(row like '<field name=*', TextBetween(row, '"','"')) as fieldname,
  If(row like '<field name=*', TextBetween(row, '>','<')) as fieldvalue
;
LOAD [@1:n] as row, RecNo() as ID
FROM [GenericXML.xml]
(fix, codepage is 1252);

T2:
NoConcatenate
LOAD * Resident T1
Where Len(fieldname) > 0


DROP Table T1;


T3:
Generic LOAD recID, fieldname, fieldvalue
Resident T2
;


Let tableName = 'T3.' & FieldValue('fieldname', 1);


Result:
LOAD recID
Resident T2;


For i = 1 To FieldValueCount('fieldname')
  Let tableName = 'T3.' & FieldValue('fieldname', i);

  Join(Result)
  LOAD Distinct * 
  Resident [$(tableName)];
  DROP Table [$(tableName)];
Next 


DROP Table T2;

This produces a table 'Result' that will contain the data correctly structured.

Re: Import XML from MySQL with generic name tags

jonathandienst — Thu, 07 Apr 2016 09:28:24 GMT

Also see here Extract from XML. Help

Re: Import XML from MySQL with generic name tags

Thu, 14 Apr 2016 08:14:01 GMT

Any idea how this performs on a large dataset of several MB and 100k+ rows? I haven't been able to try it yet, but it seems that multiple joins on the same dataset and a loop will take its toll on the performance.

As opposed to the original question, if I were to supply a direct hardcoded mapping from the XML fields to the database columns (field#1 = id, field#2 = name, etc) would it be easier to build a better performing load script?