?
Solved

Selecting XML Nodes

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

Expert Comment

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

Featured Post

Create CentOS 7 Newton Packstack Running Keystone

A bug was filed against RDO for the installation of Keystone v3. This guide is designed to walk you through the configuration for using Keystone v3 with Packstack. You will accomplish this using various repos and the Answers file.

Question has a verified solution.

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

Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

777 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