Search an XML

[MrPresident2k]

Hi,

I have an XML string which is this format

 <Parts>

 <Part>
  <index>1</index> 
  <PART_NBR>AS15</PART_NBR> 
  <SECTION>A</SECTION> 
  <MAX_PITCH_DIA>1.4</MAX_PITCH_DIA> 
  <MIN_PITCH_DIA>0</MIN_PITCH_DIA> 
  <ALLOW_FHP_BELT>1</ALLOW_FHP_BELT> 
  <COMPOUND_GROOVE>0</COMPOUND_GROOVE> 
  <TURNS_OF_ADJUSTMENT_100>0</TURNS_OF_ADJUSTMENT_100> 
  <SUB_PARTS_PER_TURN>0</SUB_PARTS_PER_TURN> 
  <INIT_TURNS_OPEN_MAX_PD>0</INIT_TURNS_OPEN_MAX_PD> 
  </Part>

 <Part>
  <index>2</index> 
  <PART_NBR>AS15</PART_NBR> 
  <SECTION>B</SECTION> 
  <MAX_PITCH_DIA>1.6</MAX_PITCH_DIA> 
  <MIN_PITCH_DIA>0</MIN_PITCH_DIA> 
  <ALLOW_FHP_BELT>1</ALLOW_FHP_BELT> 
  <COMPOUND_GROOVE>0</COMPOUND_GROOVE> 
<TURNS_OF_ADJUSTMENT_100>0</TURNS_OF_ADJUSTMENT_100> 
  <SUB_PARTS_PER_TURN>0</SUB_PARTS_PER_TURN> 
  <INIT_TURNS_OPEN_MAX_PD>0</INIT_TURNS_OPEN_MAX_PD> 
  </Part>

 <Part>
  <index>3</index> 
  <PART_NBR>AK17</PART_NBR> 
  <SECTION>A</SECTION> 
  <MAX_PITCH_DIA>1.7</MAX_PITCH_DIA> 
  <MIN_PITCH_DIA>0</MIN_PITCH_DIA> 
  <ALLOW_FHP_BELT>1</ALLOW_FHP_BELT> 
  <COMPOUND_GROOVE>0</COMPOUND_GROOVE> 
<TURNS_OF_ADJUSTMENT_100>0</TURNS_OF_ADJUSTMENT_100> 
  <SUB_PARTS_PER_TURN>0</SUB_PARTS_PER_TURN> 
  <INIT_TURNS_OPEN_MAX_PD>0</INIT_TURNS_OPEN_MAX_PD> 
  </Part>

 <Part>
  <index>4</index> 
  <PART_NBR>AS20</PART_NBR> 
  <SECTION>A</SECTION> 
  <MAX_PITCH_DIA>1.9</MAX_PITCH_DIA> 
  <MIN_PITCH_DIA>0</MIN_PITCH_DIA> 
  <ALLOW_FHP_BELT>1</ALLOW_FHP_BELT> 
  <COMPOUND_GROOVE>0</COMPOUND_GROOVE> 
<TURNS_OF_ADJUSTMENT_100>0</TURNS_OF_ADJUSTMENT_100> 
  <SUB_PARTS_PER_TURN>0</SUB_PARTS_PER_TURN> 
  <INIT_TURNS_OPEN_MAX_PD>0</INIT_TURNS_OPEN_MAX_PD> 
  </Part>

 <Part>
  <index>4</index> 
  <PART_NBR>AS20</PART_NBR> 
  <SECTION>B</SECTION> 
  <MAX_PITCH_DIA>1.9</MAX_PITCH_DIA> 
  <MIN_PITCH_DIA>0</MIN_PITCH_DIA> 
  <ALLOW_FHP_BELT>1</ALLOW_FHP_BELT> 
  <COMPOUND_GROOVE>0</COMPOUND_GROOVE> 
<TURNS_OF_ADJUSTMENT_100>0</TURNS_OF_ADJUSTMENT_100> 
  <SUB_PARTS_PER_TURN>0</SUB_PARTS_PER_TURN> 
  <INIT_TURNS_OPEN_MAX_PD>0</INIT_TURNS_OPEN_MAX_PD> 
  </Part>
</Parts>

I have 4000 records from a database in this format. The Part_Nbr (say "AS15") is the primary key which I have. I have these values in an array. Now I have to get the records for each primary key from this XML. I tried doing a search, but it is taking a long time.

For i = 0 To XMLDoc.getElementsByTagName("PART_NBR").length - 1
    If XMLDoc.childNodes(0).childNodes(i).childNodes(1).Text = "AS20" Then
      MsgBox "Found"
    End If
Next i

This is taking a long time, since there are 4000 records. Is there any method available, so that I can query the XML string and get the result?

Thanks,
Pres.

[MartinLiss]

The following will return all the PART_NBRs

Dim oxmlNodeList As IXMLDOMNodeList
    Dim intIndex As Integer
 
    Set oxmlNodeList = XMLdoc.documentElement _
                                     .selectNodes("//PART_NBR")
 
    For intIndex = 0 to oxmlNodeList.Length - 1
       ' Do what you want to with the returned nodes
    End If

And this will return the "AS20" nodes

Dim oxmlNodeList As IXMLDOMNodeList
    Dim intIndex As Integer
 
    Set oxmlNodeList = XMLdoc.documentElement _
                                     .selectNodes("//[PART_NBR = AS20]")
 
    For intIndex = 0 to oxmlNodeList.Length - 1
       ' Do what you want to with the returned nodes
    End If

I haven't tested either one, so if there are problems you can't figure out let me know.

[Lethal]

Or..

Private Sub Command1_Click()
    MsgBox GetRecordXml("AS15").length
End Sub

Private Function GetRecordXml(ByVal strKey As String) As IXMLDOMNodeList
    Dim strXPath As String
    Dim xmlRecords As IXMLDOMNodeList
    Dim xmlRecordSet As New DOMDocument
    
    xmlRecordSet.async = False
    xmlRecordSet.Load App.Path & "\test.xml"
    
    strXPath = "Part[PART_NBR='" & strKey & "']"
    Set xmlRecords = xmlRecordSet.documentElement.selectNodes(strXPath)
    Set GetRecordXml = xmlRecords
End Function

[MrPresident2k]

Thanks Guys,

It worked well and it is working fast.

Thanks,
Pres