Split XML Field

[hanchilicious]

Hi,

Xtal XI

How do you turn this one field (contains no carriage returns):

<?xml version="1.0" encoding="UTF-8"?><qobject><type>QObjectVTIEpisode</type>
<created>2008-05-15T14:06:19</created>
<properties><episodeid>112806</episodeid>
<programmeid>1105133</programmeid><contentref>
</contentref><episodetitle></episodetitle...

into this:

created: 2008-05-12T14:06:19
episodeid: 112806
programmeid: 1150133...

where the section of the string to capture will always start with <created>, where this start point is not fixed by number of characters, and where other xml identifiers like <episodeid> for example, are not fixed by position, or may be entirely different fields altogether.

 

[Madawc]

You could get rid of a lot of it using a series of Replace statements, e,g,

Replace({your.field, "<created>", "")

One per possible string, unfortunately, though you can embed them and also reference one formula field from another.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[hanchilicious]

I really don't think writing a massive nested replace formula for strings which may or may not be there is the most elegant way to achieve this.

I'm really looking to drive the bulk of the function from '<', '<>', or '</'.

Cheers

 

[lbass]

I don't know what you rule is regarding what to include, so I assumed in the following that you only wanted segments containing numerals:

stringvar k := {yourXMLfield};
stringvar array z := split(k,"</");
numbervar i;
numbervar j := ubound(z);
stringvar p := "";

for i := 1 to j do(
if isnumeric(right(replace(right(z,len(z)-instrrev(z,"<")),">",":"),1)) then
p := p + replace(right(z,len(z)-instrrev(z,"<")),">",": ")+chr(13)
);
p

When formatted to "can grow", this returns:

created: 2008-05-12T14:06:19
episodeid: 112806
programmeid: 1150133

-LB

 

[hanchilicious]

Thanks, but the values can be any datatype as the data and title tags suggest.

Plus, if there is no data, the output needs to reflect this to the user.

created: 2008-05-12T14:06:19
episodeid: 112806
programmeid: 1150133
contentref: e.g. AB (for the sake of argument)
episodetitle:
programmetitle: ...etc

for instance

 

[lbass]

Then try:

stringvar k := mid({yourXMLfield},instr({yourXMLfield},"Created");
stringvar array z := split(k,"</");
numbervar i;
numbervar j := ubound(z);
stringvar p := "";

for i := 1 to j do(
p := p + replace(right(z,len(z)-instrrev(z,"<")),">",": ")+chr(13)
);
p

This will get you close.

-LB

 

[hanchilicious]

Strangest thing; that formula takes some tags out of their original order, and suffixes them to the end of the record, duplicating the original last tag. Also string values negate a carriage return prior to the next line.

I'm running with:

whileprintingrecords;
stringvar qw := replace({myfield},'><','><br></br>'+chr(13)+'<:');
stringvar zx := replace(qw,':/','/');
stringvar xy := replace(zx,'<:','');

xy;

and switching the text interpretation to html.