Solved

Selecting XML Nodes

Posted on 2013-01-18
5
546 Views
Last Modified: 2013-01-18
Hello,

I am trying to parse an XML file which has the following layout:

<?xml version="1.0" encoding="utf-8"?>
<result>
<Updated>2013-01-06T09:00:32-08:00</Updated>
<PropertiesV1>
  <element>
    <TypeNumber>10</TypeNumber>
    <TypeDescription>
      <element>
        <Language>NL</Language>
        <Description>Soort</Description>
      </element>
      <element>
        <Language>FR</Language>
        <Description>Type de bâtiment</Description>
      </element>
      <element>
        <Language>DE</Language>
        <Description>Art</Description>
      </element>
      <element>
        <Language>EN</Language>
        <Description>Type</Description>
      </element>
      <element>
        <Language>IT</Language>
        <Description>Tipo</Description>
      </element>
      <element>
        <Language>ES</Language>
        <Description>Tipo</Description>
      </element>
      <element>
        <Language>PL</Language>
        <Description>Kategoria</Description>
      </element>
    </TypeDescription>
    <Properties>
      <Property>
        <Number>20</Number>
        <PropertyDescriptions>
          <element>
            <Language>NL</Language>
            <Description>Kasteel</Description>
          </element>
          <element>
            <Language>FR</Language>
            <Description>Château</Description>
          </element>
          <element>
            <Language>DE</Language>
            <Description>Schloss</Description>
          </element>
          <element>
            <Language>EN</Language>
            <Description>Castle</Description>
          </element>
          <element>
            <Language>IT</Language>
            <Description>Castello</Description>
          </element>
          <element>
            <Language>ES</Language>
            <Description>Castillo/Fortaleza</Description>
          </element>
          <element>
            <Language>PL</Language>
            <Description>Zamek</Description>
          </element>
        </PropertyDescriptions>
      </Property>
      <Property>
        <Number>30</Number>
        <PropertyDescriptions>
          <element>
            <Language>NL</Language>
            <Description>Cottage/gîte</Description>
          </element>
          <element>
            <Language>FR</Language>
            <Description>Gîte</Description>
          </element>
          <element>
            <Language>DE</Language>
            <Description>Cottage</Description>
          </element>
          <element>
            <Language>EN</Language>
            <Description>Cottage</Description>
          </element>
          <element>
            <Language>IT</Language>
            <Description>Cottage</Description>
          </element>
          <element>
            <Language>ES</Language>
            <Description>Casa rural</Description>
          </element>
          <element>
            <Language>PL</Language>
            <Description>Dom na wsi</Description>
          </element>
        </PropertyDescriptions>
      </Property>

Open in new window


But I want to be able to differentiate between the languages and am not sure how to get, for example the English <description> of <property><number>20</number>.

My code, so far is this (not right to the end!):
Set itemList = XMLDom.SelectNodes("Result")
 
For Each itemAttrib In itemList

	set Property_list = itemAttrib.SelectNodes("//Property")

		For Each i In Property_list
		Property_Type_Code = itemAttrib.SelectSingleNode(".//Number").text
		
		
		SQL = "Insert INTO LG_Data_Feed_Reference ("
		SQL = SQL & "Feed_Name, Property_Type_Number"	
		SQL = SQL & ") VALUES ("
		SQL = SQL & "'ReferencePropertiesV1'," & Property_Type_Code & ")"
		
		response.Write(SQL & "<HR>")
		response.Flush()
		Next

Open in new window


Help apreciated as always :-)
0
Comment
Question by:Nico2011
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 18

Expert Comment

by:zc2
ID: 38793536
I see some errors...
First, XML and XPath are case sensitive, so if the XML has the node "result", you have to select "result", not "Result".
Second, selecting the result nodes (itemList) makes no sense, because then you select the "Property" at any level  from the root (// before "Property" means it ignores the current node (itemAttrib) and always searches from the document root.
Then you're selecting Number nodes again from the root (itemAttrib), ignoring just found Property.
But let's return to your question.
If the variable Property_Type_Code contains the reference to a Number element, then to get the English description, try use the following XPath expression:

Property_Type_Code.SelectSingleNode("following-sibling::PropertyDescriptions/element[Language='EN']/Description")
0
 

Author Comment

by:Nico2011
ID: 38793608
I have change my code to what is below, but get a blank page...:

	set Property_list = XMLDom.SelectNodes("//Property")

		For Each i In Property_list
		Property_Type_Code = itemAttrib.SelectSingleNode(".//Number").text
		Property_Type_.SelectSingleNode("following-sibling::PropertyDescriptions/element[Language='EN']/Description")
		
		SQL = "Insert INTO LG_Data_Feed_Reference ("
		SQL = SQL & "Feed_Name, Property_Type_Number"	
		SQL = SQL & ") VALUES ("
		SQL = SQL & "'ReferencePropertiesV1'," & Property_Type_Code & ")"
		
		response.Write(SQL & "<HR>")
		response.Flush()
		Next

Open in new window

0
 
LVL 18

Accepted Solution

by:
zc2 earned 500 total points
ID: 38793759
The XML contains an European character, but the character set is not set it "ISO-8859-1" or "windows-1252".
Please make sure the XML has the following XML declaration in the first line:
<?xml version="1.0" encoding="windows-1252"?>

You can check was the XML properly loaded or not by the following code:
If XMLDom.parseError.errorCode <> 0 Then
     Response.write( "error: " & XMLDom.parseError.reason & " in " & XMLDom.parseError.line & ":" & XMLDom.parseError.linepos )
end if

Open in new window

Then, to select the property data, please try the following code:
XMLDom.setProperty "SelectionLanguage", "XPath"
set Property_list = XMLDom.SelectNodes("//Property")
For Each i In Property_list
  Property_Type_Code = i.SelectSingleNode("Number").text
  Property_Type_Descr = i.SelectSingleNode("PropertyDescriptions/element[Language='EN']/Description").text
' ---- your SQL formation code goes here ----------
Next

Open in new window

0
 

Author Closing Comment

by:Nico2011
ID: 38793831
Thanks very much - works perfectly!

Have a good weekend.
0
 
LVL 18

Expert Comment

by:zc2
ID: 38793861
You're welcome!
0

Featured Post

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 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