Isolating data from a field with XML data 1

[JetRamsey]

I am attempting to pull data from a SQL database and I see a field that contains XML data. In the application, many fields are tied to this one field in the SQL database. If no data is filled out in let's say 6 fields, this XML type field within the SQL DB just shows:

<ArrayOfKeyValueOfstringstring xmlns:i="

http://www.w3.org/2001/XMLSchema-instance"

xmlns="

http://schemas.microsoft.com/2003/10/Serialization/Arrays"

/>

However, if one of more fields are filled out in the application, a KEY and VALUE tag will appear and display data. In the following example, two possible fields are listed within the array.

<ArrayOfKeyValueOfstringstring xmlns:i="

http://www.w3.org/2001/XMLSchema-instance"

xmlns="

http://schemas.microsoft.com/2003/10/Serialization/Arrays">

<KeyValueOfstringstring>
<Key>EventSummary</Key>
<Value>"The event will be held in 3 days"</Value>
</KeyValueOfstringstring>
<KeyValueOfstringstring>
<Key>disposition</Key>
<Value>{"code":"00","description":"SOON"}</Value>
</KeyValueOfstringstring>
</ArrayOfKeyValueOfstringstring>

In the example above, I need to isolate each field as it's own. Is there an easy way to isolate a display the Event Summary Key value of "The event will be held in 3 days" and then the Disposition Key value. Also, the description value, not the code value of the Disposition key is what I need to display.

TIA

 

[lbass]

This is coded specifically to your example, but maybe it will get you started:

Create a formula:

stringvar x := '<ArrayOfKeyValueOfstringstring xmlns:i="

http://www.w3.org/2001/XMLSchema-instance"

xmlns="

http://schemas.microsoft.com/2003/1...ringstring><Key>EventSummary</Key><Value>"The

event will be held in 3 days"</Value></KeyValueOfstringstring><KeyValueOfstringstring><Key>disposition</Key><Value>{"code":"00","description":"SOON"}</Value></KeyValueOfstringstring></ArrayOfKeyValueOfstringstring>';

numbervar pos1 := instr(x,'<Value>')+8;
numbervar pos2 := instr(x,'</Value>')-1;
numbervar pos3 := instr(x,"description")+14;
numbervar pos4 := instr(x,'}</Value>')-1;
mid(x,pos1,pos2-pos1)+" "+
mid(x,pos3,pos4-pos3)

-LB