INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Isolating data from a field with XML data

Isolating data from a field with XML data

(OP)
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..." />

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...">
<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

RE: Isolating data from a field with XML data

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/10/Serialization..."><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>';

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close