Solved

Selecting XML Nodes

Posted on 2013-01-18
5
541 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks very much - works perfectly!

Have a good weekend.
0
 
LVL 18

Expert Comment

by:zc2
Comment Utility
You're welcome!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Hello again, all.  For those of you that have been following along, you'll know that this is my third article on this topic (though it is not Part III).  This article is sort of remedial, and probably the topic with which I should have started the s…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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.

771 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

10 Experts available now in Live!

Get 1:1 Help Now