parse xml data

[Jacob438]

I have some data stored in a database that looks like this:

<xml><instanceID>49926</instanceID><name>Jay Bartels</name><address>555 Madison Avenue</address><phone>650 555 1212</phone><comments>Please only call in an emergency</comments></xml>

Is there a way i could easily get the data to look like this in a report:

Name: Jay Bartels
Address: 555 Madison Avenue
Phone: 650 555 1212
Comments: Please only call in an emergency

I need help on this ASAP and I'd like to be able to create a formula field that looks at this info from the database and outputs it like i specified above, thanks a million!

Jacob

 

[Jacob438]

now I've got a problem when I try to access that field from the database in the formula editor, i get a message stating &quot;blob field or memo field cannot be used in a formula&quot; what do I do now?

I cannot change the database but I need to parse this field and get the data out of it into a report, hmm...

Thanks,

Jacob

 

[synapsevampire]

You should be able to create a SQL Expression and CAST it as a varchar. If you have CR 8.5 or below, you're limited to 254 character fields, and may have to create several SQL Expressions to get at the data.

BTW, storing data in XML is cute, but not very efficient to Select on.

-k

 

[Jacob438]

Oh yeah, I realize it's not very practical, trust me this is definately not my doing, haha, if I had my choice, I wouldn't really deal with crystal reports much at all...

Anyways, yeah I had to truncate the database field to 254 in order to do this, and after that, parsing is cake, so i guess i was just stuck on the max field limit thing, thanks!

Jacob