Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Selecting XML Nodes

Posted on 2013-01-18
5
Medium Priority
?
550 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 19

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 19

Accepted Solution

by:
zc2 earned 2000 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 19

Expert Comment

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Create a Windows 10 custom Image with custom task bar and custom start menu using XML for deployment.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 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