Load XML from each record in a field

[#1]

I have a database which holds annual leave records. Each leave record is actually a leave period (e.g. a pay fortnight), and the details of the leave are stored as XML in one particular field.

I have established that if stored in a file by itself, 1 record of the XML can be read back into QV via the XML/Table wizard.

What I need to do, is store each xml record into its own CSV file.

I have tried various versions of do while, and for each loops, but have not managed to make it happen.

Any ideas?

My sample code below:

For Each ListValue in FieldValueList('XML_Data')

     For Each a in FieldValueCount('XML_Data')

     XMLTest$(a):

     Load '$(ListValue)' as XMLData_Line

     Resident Staging;

     Store XMLTest$(a) into [$(pQVDExtract)XMLTest$(a).csv] (txt);

Next a

Next ListValue

[#2]

I have managed to use the following code to generate a file for each field value:

Set a = 1;

For Each ListValue in FieldValueList('XML_Data')

XML_Store$(a):

Load

'$(ListValue)' as XML_String

AutoGenerate 1;

Store XML_Store$(a) into [$(pQVDExtract)XML_Store$(a).csv] (txt);

Drop Table XML_Store$(a);

Let a=a+1;

Next ListValue;

My challenge is that I know that there are 119 lines in the original table, and only 100 files are being generated. Is there an in built limitation on file numbers that this function can generate?

[marcus_sommer]

FieldValueList returned only the distinct field-values of this field and not the records from the origin table. If you need all records (with their redundant values) you will need to loop through the table per something like:

for i = 1 to noofrows('YourTable')

     let ListValue = peek('XML_DATA', $(i), 'YourTable');

     ....

If this isn't the reason for your uncomplete loop I would add a trace-statement like:

trace $(a): '$(ListValue)';

to see within the load-progress window or the log-file what really happens.

- Marcus