By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 1,847 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,694 IT Pros & Developers. It's quick & easy.

Extract XML tagged text from Access Memo field

P: 1
I have an Access 2003 table with an XML field that is tagged as follows:

<Calendar CompactMode="1"><CustomProperties><CustomProperty Name="startdate" Value="3/26/2007" VariantType="8"/><CustomProperty Name="recurrencestarttime" Value="10:00:00 AM" VariantType="8"/><CustomProperty Name="membername" Value="John Smith" VariantType="8"/><CustomProperty Name="starttime" Value="10:00:00 AM" VariantType="8"/><CustomProperty Name="comment" Value="" VariantType="8"/><CustomProperty Name="casemanagerid" Value="250" VariantType="8"/><CustomProperty Name="districtid" Value="" VariantType="8"/><CustomProperty Name="riderequestid" Value="288" VariantType="8"/><CustomProperty Name="endtime" Value="12:00:00 PM" VariantType="8"/><CustomProperty Name="archived" Value="Open" VariantType="8"/><CustomProperty Name="recurrenceendtime" Value="10:30:00" VariantType="7"/></CustomProperties></Calendar>

I need to extract the text from each of the CustomProperty Name fields. For example, I need to extract the actual MemberName value of John Smith from <CustomProperty Name="membername" Value="John Smith" VariantType="8"/>.

I'm using Access 2003 with Windows XP. Many thanks in advance to anyone who can point me in the right direction.
Mar 27 '07 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 1,356
This would need to be done in VBA using a Function the results can be shown in a query, form, or Report.

Tested on the sample provided, to use just enter GetMember(YOURFIELDNAMEWITHXML) as a field name in a query

Expand|Select|Wrap|Line Numbers
  1. Public Function GetMember(strXML As String)
  2. Dim inInt As Integer
  3. Dim strMember As String, strSrch As String
  4. strSrch = "CustomProperty Name=""membername"""
  5. Debug.Print strSrch
  6. 'Instr is used to find the starting position of a string Such as the C in the strSrch variable
  7. inInt = InStr(strXML, strSrch)
  8. If inInt > 0 Then
  9.     'Mid is a function that will pick things out of the middle of a string, Len(Item) is used to determine length
  10.     strMember = Mid(strXML, ((Len(strSrch) + inInt) + 8), InStr(((Len(strSrch) + inInt) + 8), strXML, """") - ((Len(strSrch) + inInt) + 8))
  11. Else
  12.     strMember = "No Name Found!"
  13. End If
  14. GetMember = strMember
  15. End Function

Hope this helps if you need more info let me know.
Mar 30 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.