Search string for 2nd, 3rd, 4th... occurance

[broecher]

Hi, I have an xml block I want to read and pull information out of it. Her is a sample of the xml:



<?xml version="1.0" encoding="UTF-8"?>
<Elements>
<List>
<Items>
<Item>
<Id>{4764F5E6-15A1-48BF-808A-F673ED7CDCDA}</Id>
<Name>Date</Name>
<Static>True</Static>
<StaticDataType>7</StaticDataType>
<ImageId>803</ImageId>
</Item>
<Item>
<Id>{EB86279A-E032-43D2-B4A8-8B8B2892B10E}</Id>
<Name>Time</Name>
<Static>True</Static>
<StaticDataType>8</StaticDataType>
<ImageId>221</ImageId>
</Item>


I would like to pull the text listed as 'Name' from the xml. So in this sample that would be 'Date' and 'Time'. The problem is that there are a lot, maybe 50+ Names to pull from the block.

I started writing a query that would do this, but I realized that it cannot pull more than 24 or so 'names' and then it freezes access up due to each line depending on all the previous lines of sql. Here is what I started:

SELECT Settings.XmlData, InStr([XmlData],"<Name>")+6 AS Name1begin, Mid([XmlData],[Name1begin],InStr([Name1begin],[XmlData],"</Name>")-[Name1begin]) AS Name1,

InStr([Name1begin],[XmlData],"<Name>")+6 AS Name2begin, Mid([XmlData],[Name2begin],InStr([Name2begin],[XmlData],"</Name>")-[Name2begin]) AS Name2,

InStr([Name2begin],[XmlData],"<Name>")+6 AS Name3begin, Mid([XmlData],[Name3begin],InStr([Name3begin],[XmlData],"</Name>")-[Name3begin]) AS Name3
' ........on to Name30+
FROM Settings
WHERE (((Settings.Name)="elements"));


It would be nice to have some function that just returns the position of the nth instance of your keyword.
Like:
getInstance(1, [XmlData], "<Name>") = 189
getInstance(2, [XmlData], "<Name>") = 461
getInstance(3, [XmlData], "<Name>") = 733
Which would be the positions of the 1st, 2nd, and 3rd "<Name>". From there I could use the mid function to finish it up.

Any ideas on how to make this function, or how to do this some other way? Thanks

[ajetrumpet]

you can adapt this function by using the substring as "<Name>" in your example and then performing a mid() function on it from the start of the occurance: http://www.ajenterprisesonline.com/_...fname=EInstr()

[broecher]

I tried adding the argument

ocur AS INTEGER

but I wasn't sure what to do next. I changed

End If
oCount = ocur

I thought this would stop the loop as soon as it reaches the occurrence I enter in the argument. then I changed the ending to be

EInstr = CurPos

Anyway this code as I changed it freezes my access. Any more tips for me?

Option Compare Database

Function EInstr(ocur As Integer, strBase As String, _
                strSearch As String)

On Error GoTo Cleanup

'******************************************************************************
'                                                                             *
'Author: Adam Evanovich                                                       *
'Date: 9/17/2010                                                              *
'Purpose: To count the number of occurances of one string inside of another.  *
'                                                                             *
'Arguments:                                                                   *
'strBase > String in which to search for substrings                           *
'strSearch > Substring for which to count occurances                          *
'                                                                             *
'******************************************************************************

Dim oCount As Long
Dim CurPos As Long
Dim lReached As Boolean
Dim strSearchPos As Long

lReached = False
CurPos = 1

    Do Until lReached = True

        strSearchPos = InStr(CurPos, strBase, strSearch)

            If strSearchPos = 0 Then
                lReached = True
            Else
                oCount = oCount + 1
            End If
                oCount = ocur
                

    Loop

EInstr = CurPos

Cleanup:
    oCount = 0
    CurPos = 0
    strSearchPos = 0

End Function

[broecher]

just found one error, but it still freezes when i run it. I realized CurPos is the character count I was looking for. I made the correction in my last post

[ajetrumpet]

so how is this data structured anyway? what do the records look like? you mean you have XML code that has been pulled from an actual file in ONE of the fields as a memo type? that's what it looks like. and how many records are there in this table of yours?

running this function on an even medium sized table will freeze up the program. it's too complex.

But then again, most of the time Access "freezes" (as people call it), it hasn't really crashed, it's simply taking a LONG time to process the request. For instance, I've looped a table for millions of records before and stopped it at 2 million. the screen goes white of course, but it returns to normal when the processing is finally done.

You might have to break this up into sections sir. e.g. - maybe get the positions of the occurances first? then put them in a table and query out those positions in combination with the MID() function to get the content inside the NAME tags??

[broecher]

Hey sorry I didn't say thanks for the link to that code. It DID work in its original form just as it was supposed to for me. I am testing it on a a tiny database with only 9 records. The XML memo from each of those records is around 450 lines which is about 10,000 characters.

Since the code worked fine in its original form, I think I just broke it. I am just trying to stop it while counting the occurrences of the string when it reaches the occurrence I want, and to tell me the character position. Should be less work than it was doing to start with right?

I really no nothing about vba so that is my problem.

[ajetrumpet]

you don't really need to know the language to work it. but it helps.

did you try what I said in the prev. post? break it up. running it in one fell swoop is not a good idea. and it will take eons to process....

[broecher]

Yeah, that's all I tried to make your code do was give me one position. no mids or anything yet. I am sure I made some stupid mistake.

My idea is to have a query like this:

Occurance, yourFunctionResult{the char position}, TextIWant
1, 155, Date
2, 180, Time
3, 230, Longitude
.....

The Mid Function would be in the TextIWant field

[ajetrumpet]

Occurance, yourFunctionResult{the char position}, TextIWant
1, 155, Date
2, 180, Time
3, 230, Longitude
.....

The Mid Function would be in the TextIWant field

you can't do that sir. queries only run horizontally, and on data already in the source. they cannot create new records dynamically like that. if that's what you want, create a table with 1 field: make it a long type. use a recordset in my function and add new records for as many occurrances as you find. something like this:[code]

Option Compare Database

Function EInstr()

dim rs as dao.recordset
set rs=currentdb.openrecordset("newtable")

On Error GoTo Cleanup

'******************************************************************************
'                                                                             *
'Author: Adam Evanovich                                                       *
'Date: 9/17/2010                                                              *
'Purpose: To count the number of occurances of one string inside of another.  *
'                                                                             *
'Arguments:                                                                   *
'strBase > String in which to search for substrings                           *
'strSearch > Substring for which to count occurances                          *
'                                                                             *
'******************************************************************************

Dim oCount As Long
Dim CurPos As Long
Dim lReached As Boolean
Dim strSearchPos As Long

lReached = False
CurPos = 1

    Do Until lReached = True

        strSearchPos = InStr(CurPos, strBase, strSearch)

            If strSearchPos = 0 Then
                lReached = True
            else
                rs.addnew
                rs!NameTagValue = mid(strBase, strSearchPos + 6, _
                                      instr(strSearchPos + 6, strBase, "</Name>"))
                rs.update
            end if

    Loop

Cleanup:
    oCount = 0
    CurPos = 0
    strSearchPos = 0

End Function

where strBase = the entire XML code and strSearch = "<Name>".

make sense?

[broecher]

I realize that I would have had to do several other things to get a query that looks like that. Break it up into steps right? These details are not that important but I was going to have a table that contained numbers 1-200 or some number that greatly exceeds the maximum number of Names I want to pull from the XML. Then I would query it, and I would use it to make this function work

expr1: IEnstr(4,[XmlData],"<Name>")

would become:

expr1: IEnstr([numberlistfield],[XmlData],"<Name>")

I am not worried about all that right now, just can't figure out the code in my second post. Do you see anything blatantly wrong? I listed the changes I made before posting the code.

[ajetrumpet]

just can't figure out the code in my second post. Do you see anything blatantly wrong? I listed the changes I made before posting the code.

what code? I don't see it in this thread. you mean the one where you attempted to modify my function?

if so, you don't need to worry about that. 3 posts ago I gave you the exact example to use. why can't you just do it that way?

or does the code I provided not make sense?