Thread: Retrieving String from Access Column

Several months ago I posted a thread about searching and retrieving a string. This thread was solved... thx for all the help. Refer to the following link => http://www.excelforum.com/access-pro...h-pattern.html

In this new thread I'm trying to achieve two (2) tasks that are very similar.

I have a column in MS Access 2003 named 'Application_XML'. Each cell per record contains about 700 lines of details. See below for sample of cell:

HTML Code: 

...
      <field name=""EVENT_CODE"">
        <value />
      </field>
      <field name=""LANGUAGE_SPOKEN"">
        <value>EN</value>
      </field>
      <field name=""LANGUAGE_WRITTEN"">
        <value>EN</value>
      </field>
...
  <Notes>
John Doe
123 Somewhere Pl 
City, State 12345

Phone 222-22-2222
Lives with grandmother 
Doc
poa= envelope 
ID=birth certificate
  </Notes>

1. How do I retrieve the value for 'LANGUAGE_SPOKEN'? which in the sample is 'EN'
2. How do I retrieve what the user has entered in Notes? which begins with the tag '<Notes>' and ends with the tag '</Notes>'

Eventually, I will have the Language_Spoken value and any Notes in its own column in an Access query.

Please help !

Wow, you are storing entire XML files as individual records in an Access database? Do you love pain? Any way you could import the XML files into tables using the Import feature in Access instead of doing it the way you propose above?

If that isn't possible, you are going to need a query like this:
SELECT Left(Right([Application_XML],Instr([Application_XML],"LANGUAGE_SPOKEN"+6),Instr([Application_XML],"LANGUAGE_WRITTEN")-10) AS LANGUAGE_SPOKEN FROM tableName

You will really have to play around with this to get it to work. You need to change the 6 and 10 to lop off the correct number of surrounding characters. I'm not even sure if it will work at all because it looks like there might be tabs and carriage returns in your data.

The Notes field will be the same idea, but should be a bit easier because you can use <Notes> and </Notes> as identifiers for the Instr part of the query.