Solved

Selecting XML Nodes

Posted on 2013-01-18
5
543 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
  • 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

831 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