Child Nodes...

Posted on 2006-05-03
Last Modified: 2008-02-01
I am pretty new to the vbscript and XML thing and I am trying to use selectSingleNode to get the child nodes information and then extract the Attributes from them. But I get an object required error when I use the selectSingleNode... I checked the DTD and this seems to be because of the ELEMENTS not listed in the <!ELEMENT MLB_SCHEDULE (STORYNUMBER, SELECTORCODE, KEYWORD, VERSION, DATE, WORDCOUNT, DATETIME, LEAGUE*)> line at the top of the DTD File... IF I add it the code runs but I get no results. So Obviously I need to figure out a better way to do this... Can someone tell me how to get the ELEMENTS "GAME", "HOME" and "VISIT" in the values listed in my code??? r9, r10, r11. I can post the DTD files here if needed.

Here is my code....
Set objFolder = objFSO.GetFolder("C:\SportsTicker\xmlfiles")
For Each file in objFolder.Files
    Set objTextStream = objFSO.OpenTextFile(objFolder & "\" & file.Name)
 Do While Not objTextStream.AtEndOfStream
    if j > 0 then
                  strval=strval & & ","
               msgbox "The file name is "& &" "
                  objXMLDOM.async = False
If Not objXMLDOM.Load("C:\SportsTicker\xmlfiles\" & Then  
                                    exit do
            End If
   Set objNodes = objXMLDOM.selectNodes("/MLB_SCHEDULE")
               For Each objBookNode In objNodes
                                          r1 = objBookNode.selectSingleNode("STORYNUMBER").nodeTypedValue
                                          'msgbox "r1 = "& r1 &" "
                                          r2 = objBookNode.selectSingleNode("SELECTORCODE").nodeTypedValue
                                          'msgbox "r2 = "& r2 &" "
                                          r3 = objBookNode.selectSingleNode("KEYWORD").nodeTypedValue
                                          'msgbox "r3 = "& r3 &" "
                                          r4 = objBookNode.selectSingleNode("VERSION").nodeTypedValue
                                          'msgbox "r4 = "& r4 &" "
                                          r5 = objBookNode.selectSingleNode("DATE").nodeTypedValue
                                          'msgbox "r5 = "& r5 &" "
                                          r6 = objBookNode.selectSingleNode("WORDCOUNT").nodeTypedValue
                                          'msgbox "r6 = "& r6 &" "
                                          r7 = objBookNode.selectSingleNode("DATETIME").nodeTypedValue
                                          'msgbox "r7 = "& r7 &" "
                                          r8 = objBookNode.selectSingleNode("LEAGUE").nodeTypedValue
                                          'msgbox "r8 = "& r8 &" "
                  CHILD NODE--->      r9 = objBookNode.selectSingleNode("GAME").nodeTypedValue
                  CHILD NODE--->      r10 = objBookNode.selectSingleNode("HOME").nodeTypedValue
                  CHILD NODE--->      r11 = objBookNode.selectSingleNode("VISIT").nodeTypedValue
                                          KEYS = R3
                                          objADORS.Open "SELECT * FROM MLB_Schedule_Log WHERE KEYWORD = '" & KEYS & "'", objADOCnn ', adOpenKeyset',
                         IF objADORS.eof THEN
                              objADOCnn.Execute "INSERT INTO MLB_Schedule_Log (STORYNUMBER, SELECTORCODE, KEYWORD, VERSION, DATE, WORDCOUNT, DATETIME, LEAGUE ) VALUES ('" & r1 & "','" & r2 & "','" & r3 & "','" & r4 & "','" & r5 & "','" & r6 & "','" & r7 & "','" & r8 & "')"
                              objADOCnn.Execute "UPDATE MLB_Schedule_Log SET STORYNUMBER='"& r1 &"',SELECTORCODE='"& r2 &"',VERSION='"& r4 &"',DATE='"& r5 &"',WORDCOUNT='"& r6 &"',DATETIME='"& r7 &",LEAGUE='"& r8 &"'WHERE KEYWORD = '" & KEYS & "'"
                              objADOCnn.Execute "DELETE FROM MLB_Schedule WHERE KEYWORD = '" & KEYS & "'"

                              END IF
             'set fx= fx.
 set xmldoc=CreateObject("Microsoft.xmldom")
 if (xmldoc.parseError <> 0) then
  call treewalk1(xmldoc.documentElement)  
  end if
            end if
'''''''''''''''''''''''reading the attributes value from the node elements            
sub treewalk1(node)
select case node.nodeType
case 1
if node.attributes.length > 0 then  
      for i=0 to (node.attributes.length-1)  
objADOCnn.Execute "INSERT INTO MLB_Schedule (schedule_id,interleague,double_header,game_time,h_team_code,h_team_name,h_starter_first_name,h_starter_last_name,h_st_player_id,h_record,h_era,v_team_code,v_team_name,v_starter_first_name,v_starter_last_name,v_st_player_id,v_record,v_era) VALUES ('" & cstr(node.attributes.item(0).text) & "','" & cstr(node.attributes.item(1).text) & "','" & cstr(node.attributes.item(2).text) & "','" & cstr(node.attributes.item(3).text) & "','" & cstr(node.attributes.item(4).text) & "','" & cstr(node.attributes.item(5).text) & "','" & cstr(node.attributes.item(6).text) & "','" & cstr(node.attributes.item(7).text) & "','" & cstr(node.attributes.item(8).text) & "','" & cstr(node.attributes.item(9).text) & "','" & cstr(node.attributes.item(10).text) & "','" & cstr(node.attributes.item(11).text) & "','" & cstr(node.attributes.item(12).text) & "','" & cstr(node.attributes.item(13).text) & "','" & cstr(node.attributes.item(14).text) & "','" & cstr(node.attributes.item(15).text) & "','" & cstr(node.attributes.item(16).text) & "','" & cstr(node.attributes.item(17).text) & "','" & cstr(node.attributes.item(18).text) & "','" & KEYS & "','" & R9 & "')"                                                
exit for
end if      
for c=0 to (node.ChildNodes.length-1)   '*** walk the kids      
  call treewalk1(node.childNodes(c))      
  end select
  end sub

      If objADOCnn.State = 1 Then
              Set objADOCnn = Nothing
Set objADORS = Nothing
              'msgbox "Successfully Executed"
         End If
Question by:jsuttor
    LVL 52

    Expert Comment

    by:Carl Tawn
    Does your XML document have any namespaces defined ?
    LVL 3

    Author Comment

    NO namespace in xml doc.... Here is the xml doc its pretty short.

    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <!DOCTYPE MLB_SCHEDULE SYSTEM "xmldtds/Major League Baseball/MLB_SCHEDULE.dtd">
    <MLB_SCHEDULE selectorcode="SKBBA" keyword="BC-BBA-SCHD-MLBSCHED-R" league="AL" category="SCHD" schedule_date="20060502">
    <DATETIME>st 05-02-06 06:27 et</DATETIME>
    <LEAGUE league_id="AL" name="AMERICAN LEAGUE">
    <GAME schedule_id="20060502AL---CLEVELAND-0" interleague="N" double_header="N" game_time="1205PM">
    <HOME team_code="CLEVELAND" team_name="CLEVELAND" starter_first_name="C.C." starter_last_name="Sabathia" st_player_id="1862491" record="0-0" era="11.57" />
    <VISIT team_code="CHWHITESOX" team_name="CHI WHITE SOX" starter_first_name="Mark" starter_last_name="Buehrle" st_player_id="1848098" record="3-1" era="2.57" />
    <GAME schedule_id="20060502AL---LAANGELS--0" interleague="N" double_header="N" game_time="0335PM">
    <HOME team_code="LAANGELS" team_name="LA ANGELS" starter_first_name="John" starter_last_name="Lackey" st_player_id="10908" record="3-1" era="3.09" />
    <VISIT team_code="OAKLAND" team_name="OAKLAND" starter_first_name="Brad" starter_last_name="Halsey" st_player_id="2126303" record="0-0" era="1.42" />
    <GAME schedule_id="20060502AL---DETROIT---0" interleague="N" double_header="N" game_time="0705PM">
    <HOME team_code="DETROIT" team_name="DETROIT" starter_first_name="Mike" starter_last_name="Maroth" st_player_id="10346" record="3-1" era="1.85" />
    <VISIT team_code="KANSASCITY" team_name="KANSAS CITY" starter_first_name="Mark" starter_last_name="Redman" st_player_id="1847520" record="0-1" era="7.82" />
    <GAME schedule_id="20060502AL---BOSTON----0" interleague="N" double_header="N" game_time="0705PM">
    <HOME team_code="BOSTON" team_name="BOSTON" starter_first_name="Josh" starter_last_name="Beckett" st_player_id="1873450" record="3-1" era="4.50" />
    <VISIT team_code="NYYANKEES" team_name="NY YANKEES" starter_first_name="Shawn" starter_last_name="Chacon" st_player_id="10450" record="3-1" era="4.56" />
    <GAME schedule_id="20060502AL---BALTIMORE-0" interleague="N" double_header="N" game_time="0705PM">
    <HOME team_code="BALTIMORE" team_name="BALTIMORE" starter_first_name="Kris" starter_last_name="Benson" st_player_id="1847578" record="3-2" era="3.45" />
    <VISIT team_code="TORONTO" team_name="TORONTO" starter_first_name="Casey" starter_last_name="Janssen" st_player_id="2154344" record="0-1" era="4.50" />
    <GAME schedule_id="20060502AL---TAMPABAY--0" interleague="N" double_header="N" game_time="0715PM">
    <HOME team_code="TAMPABAY" team_name="TAMPA BAY" starter_first_name="Mark" starter_last_name="Hendrickson" st_player_id="11010" record="1-1" era="0.00" />
    <VISIT team_code="TEXAS" team_name="TEXAS" starter_first_name="Robinson" starter_last_name="Tejeda" st_player_id="2084373" record="0-0" era="0.00" />
    <GAME schedule_id="20060502AL---MINNESOTA-0" interleague="N" double_header="N" game_time="0810PM">
    <HOME team_code="MINNESOTA" team_name="MINNESOTA" starter_first_name="Johan" starter_last_name="Santana" st_player_id="1848071" record="1-3" era="4.45" />
    <VISIT team_code="SEATTLE" team_name="SEATTLE" starter_first_name="Jarrod" starter_last_name="Washburn" st_player_id="1847666" record="2-3" era="3.51" />
    LVL 52

    Accepted Solution

    Ok, the problem lies in the fact that GAME is a child of LEAGUE. And HOME and VISIT are children of GAME.

    So, in order to access their values you would need to use:

        r9 = objBookNode.selectSingleNode("LEAGUE/GAME").nodeTypedValue
        r10 = objBookNode.selectSingleNode("LEAGUE/GAME/HOME").nodeTypedValue
        r11 = objBookNode.selectSingleNode("LEAGUE/GAME/VISIT").nodeTypedValue

    However, if you do this, you will only get the values of the first GAME in the first LEAGUE. If you want to process each GAME you will need to loop through LEAGUE's childnodes.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now