I wish to use XPath to access data from within an Excel generated XML Spreadsheet (generated with MS Excel 2003). I am using an asp Web page (not .NET, not at this time). I instantiate MSXML.DomDocument.3.0. After loading the Excel generated XML Spreadsheet parse error free, I issue the following XPath query to create a set of row nodes from within the document:
- "//Workbook/Worksheet[@ss:Name='Upload Data']/Table/Row"
I am greeted with the following error:
Reference to undeclared namespace prefix: 'ss'. When I examine the Workbook element of the document I see clearly defined the namespace for 'ss' as well as others. I have fiddled around with the Excel generated XML Spreadsheet and have occassionally gotten the XPath query to work. However, I would like to be able to use the Excel generated XML Spreadsheet document without changes. Are there any suggestions as to what I might do or what I am doing that is wrong? Below is some asp test code that I use for evaluation:
-
dim sXPath, objXML: set objXML = server.CreateObject("MSXML2.DOMDocument.3.0")
-
dim objRoot, objNode, objNodeList, objRows, objDataColumns, objData, v
-
dim sPath: sPath = "C:\Sample_Dataset.xml"
-
if objXML.load(sPath) then
-
objXML.setProperty "SelectionLanguage", "XPath"
-
set objNodeList = objXML.selectNodes("//Workbook/Worksheet[@ss:Name='Upload Data']/Table/Row")
-
for each objRows in objNodeList
-
set objDataColumns = objNode.selectNodes("./Cell/Data")
-
for each objData in objDataColumn
-
v = objData.text
-
next
-
next
-
end if
-