Import XML from MySQL with generic name tags

[#1]

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

<?xml version="1.0"?>

<resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <row>

    <field name="id">22</field>

    <field name="client_id">1</field>

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

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

    <field name="zipcode"></field>

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

    <field name="scope_id">28</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.

[marcus_sommer]

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

- Marcus

[#3]

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.

[jonathandienst]

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.

[jonathandienst]

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.

[jonathandienst]

Also see here Extract from XML. Help

[#7]

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?