Solved

XML Parsing Issue

Posted on 2013-01-21
7
225 Views
Last Modified: 2013-01-22
Hello,

I have the following code to parse an XML page (below):

Set xmlDOM = CreateObject("MSXML2.DOMDocument.3.0")
xmlDOM.async = False
xmlDOM.setProperty "ServerHTTPRequest", True
xmlDOM.Load(Filename)
xmlDOM.setProperty "SelectionLanguage", "XPath"

set CountryList = XMLDom.SelectNodes("//result")
            For Each i In CountryList
            
            CountryCode = ""
            CountryDescription = ""
            
            CountryCode = i.SelectSingleNode("//CountryCode").text
          CountryDescription = i.SelectSingleNode("//CountryDescription/element[Language='EN']/Description").text
            
            set RegionList = i.SelectNodes("//Regions")
            
                  for each j in RegionList
                  
                  RegionCode = ""
                  RegionDescription = ""
                  
                  RegionCode = j.SelectSingleNode(".//RegionCode").text
                RegionDescription = j.SelectSingleNode(".//RegionDescription/element[Language='EN']/Description").text

                  SQL = "Insert INTO CountryReference ("
                  SQL = SQL & "CountryCode, CountryDescription, RegionCode, RegionDescription"      
                  SQL = SQL & ") VALUES ('"
                  SQL = SQL & CountryCode & "','" & CountryDescription & "','" & RegionCode & "','" & RegionDescription & "')"
                        
                  response.Write(SQL & "<HR>")
                  response.Flush()
            
                  Next
            
            Next

The XML page I am parsing is attached.

The problem I have is that I am getting the regions, but the country code and description is not changing - as can be seen here: http://www.villasdirect.com/_admin/scripts/LG_Data_Feed_ReferenceRegionsV1.asp

Austria (search for >AT<) has a lot of regions, but if you look, you'll see that they all appear under Andorra.

I'm not sure what I've done wrong but help needed and appreciated!

THANK YOU!

Help
ReferenceRegionsV1.xml
0
Comment
Question by:Nico2011
  • 4
  • 3
7 Comments
 
LVL 22

Expert Comment

by:Ivo Stoykov
Comment Utility
probably should be
 i.SelectNodes(".//Regions")

Open in new window

HTH

Ivo Stoykov
0
 

Author Comment

by:Nico2011
Comment Utility
Thanks Ivo, but the country still remains as Andorra...
0
 
LVL 22

Expert Comment

by:Ivo Stoykov
Comment Utility
could you paste the xml file?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:Nico2011
Comment Utility
Hi Ivo,

Here's the XML file - thanks very much!
ReferenceRegionsV1.xml
0
 
LVL 22

Accepted Solution

by:
Ivo Stoykov earned 500 total points
Comment Utility
set CountryList = XMLDom.SelectNodes("//result")

Open in new window

should be
set CountryList = XMLDom.SelectNodes("//Country")

Open in new window

CountryCode = i.SelectSingleNode("//CountryCode").text

Open in new window

should be
CountryCode = i.SelectSingleNode("./CountryCode").text

Open in new window

set RegionList = i.SelectNodes("//Regions")

Open in new window

should be
set RegionList = i.SelectNodes("./Regions")

Open in new window

RegionCode = j.SelectSingleNode(".//RegionCode").text
RegionDescription = j.SelectSingleNode(".//RegionDescription/element[Language='EN']/Description").text

Open in new window

should be
RegionCode = j.SelectSingleNode("./RegionCode").text
RegionDescription = j.SelectSingleNode("./RegionDescription/element[Language='EN']/Description").text

Open in new window


HTH

Ivo Stoykov
0
 

Author Comment

by:Nico2011
Comment Utility
Thanks Ivo - will check in the morning - on my iPad now...!
0
 

Author Closing Comment

by:Nico2011
Comment Utility
Thank you Ivo - works perfectly now ;-)
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Xsl Nested Loop 4 20
asp.net, auto populate 6 49
Quest Defender - XML > HTML POST Data 9 27
XML template problem 4 58
The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

772 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