CODEWITHSUNDEEP
xmlexcelvbscript
Raj
Raj

Reputation: 13

Importing XML Content to Excel

I'm a novice in VBScript and XML coding.However, getting my head around and trying to understand the concepts from W3 Schools and other online forums.

I want to read/parse an xml file using VBScript,my XML file is not data but xml source code from an application.

Below is the code snippet that I'm using -

Sub LoadXMLFile()

Dim objXML     'for xml document
Dim objNode    'for xml node item
Dim i As Integer
i = 0

Set objXML = CreateObject("Microsoft.XMLDOM")
objXML.Load ("C:\path\test.xml")
objXML.setProperty "SelectionLanguage", "XPath"
Set objNode = objXML.SelectNodes("/report/queries/query/selection/dataItem/text()")
'MsgBox objNode.Text

For i = 0 To (objNode.Length - 1)
NodeVal = objNode(i).NodeValue
MsgBox NodeVal
Next

End Sub

When I step through the VB code the objNode.Length value always evaluates to 0. Not sure why its not calculating the Length.

Here is the xml that I'm trying to parse -

<report xmlns="http://developer.cognos.com/schemas/report/10.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>
/content/package[@name='GO Sales (query)']/model[@name='model']
</modelPath>
<drillBehavior/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection>
<dataItem aggregate="none" rollupAggregate="none" name="Product line">
<expression>[Sales (query)].[Products].[Product line]</expression>
<XMLAttributes>
<XMLAttribute output="no" name="RS_dataType" value="3"/>
<XMLAttribute output="no" name="RS_dataUsage" value="attribute"/>
</XMLAttributes>
</dataItem>
<dataItem aggregate="none" rollupAggregate="none" name="Product type">
<expression>[Sales (query)].[Products].[Product type]</expression>
<XMLAttributes>
<XMLAttribute output="no" name="RS_dataType" value="3"/>
<XMLAttribute output="no" name="RS_dataUsage" value="attribute"/>
</XMLAttributes>
</dataItem>
</selection>
</query>
</queries>
</report>

Appreciate your time and response.

Thanks & Regards Raj

Upvotes: 1

Views: 285

Answers (1)

Axel Richter
Axel Richter

Reputation: 61985

The first problem is that the dataItem element has no text node as direct child node. So ...dataItem/text() will return null.

The dataItem element contains element nodes expression and XMLAttributes. The expression contains a text node. The XMLAttributes contains further child nodes.

We can iterate through all those nodes if found the dataItem elements. Or we can simply get all text content from all childnodes. What we can do with XML DOM Objects is described in XML DOM Objects/Interfaces

The second problem is that there is a namespace defined within your XML. This needs the XML parser to know. If not, the parser will assume all elements outside namespaces and so it will not find all elements which are within namespaces.

So with your XML you could do the following:

Sub LoadXMLFile()

Dim objXML         'for xml document
Dim objNodeList    'for xml node lists
Dim objNode        'for xml node
Dim oAttribute     'for xml attribute
Dim oChildNode     'for xml node
Dim oSubChildNode  'for xml node

Set objXML = CreateObject("Microsoft.XMLDOM")
objXML.Load ("C:\Users\Axel Richter\Desktop\test.xml")
objXML.setProperty "SelectionLanguage", "XPath"
objXML.setProperty "SelectionNamespaces", "xmlns:dcc=""http://developer.cognos.com/schemas/report/10.0/"""

Set objNodeList = objXML.SelectNodes("/dcc:report/dcc:queries/dcc:query/dcc:selection/dcc:dataItem")

For Each objNode In objNodeList
 MsgBox objNode.Text 'the text content in this element and its child elements

 'go through all attributes
 For Each oAttribute In objNode.Attributes
  MsgBox oAttribute.Name & ": " & oAttribute.Text
 Next

 'go through all child nodes
 For Each oChildNode In objNode.ChildNodes
  'if the child node has child b´nodes of its own, go through them too
  If oChildNode.HasChildNodes Then
   For Each oSubChildNode In oChildNode.ChildNodes
    MsgBox oSubChildNode.nodeName & ": " & oSubChildNode.XML
   Next
  Else
   MsgBox oChildNode.nodeName & ": " & oChildNode.Text
  End If
 Next

Next

End Sub

With

objXML.setProperty "SelectionNamespaces", "xmlns:dcc=""http://developer.cognos.com/schemas/report/10.0/"""

I define a prefix dcc for the namespace

http://developer.cognos.com/schemas/report/10.0/.

The dcc is my own choice (developer cognos com). This prefix is needed for the XPATH in selectNodes Method to work properly. Since all the elements in your XML are in this namespace because the root element report has this xmlns attribute, the XPATH needs to select all child elements of report from this namespace. Otherwise it will not work. So the dcc prefix is needed for all elements in XPATH-selects which are in this namespace. If more than one namespace were present, then multiple prefixes would be neccessary. One for each namespace.

This is one of XPATH's accurate working conditions. The getElement... methods will be more tolerant with this namespaces. But in fact the namespace is there and so it should also be respected.

Upvotes: 1

Related Questions