I have a VB6 application that gets results back from a web api, the result is returned in XML format but I'm struggling to find a way to read it in VB6. I have investigated the DOM document but from what I understand and from how I have used it in the past, you need to locate the .xml file for it to work. In the instance of the web api, the xml comes through in the result and not as a .xml file. The last thing I want to do is save the result to the disk as "blah.xml" and then read that file using the DOM object. Is there any way to read a XML result in VB6 that's not in a .xml file ?
Below is a code sample of what I'm trying to do, the api is returning a result but the objects are empty and I cant figure out why. Can you spot anything that i'm doing incorrectly ?
Code:
Private Sub LoadRetailSalesReport()
Dim xDoc As New MSXML2.DOMDocument60
Dim objTableRecords As MSXML2.IXMLDOMNodeList
Dim objTable As MSXML2.IXMLDOMNode
Dim objTableColumns As MSXML2.IXMLDOMElement
Set xDoc = New MSXML2.DOMDocument60
If xDoc.LoadXML(ApiResult) = False Then
MsgBox "Connection failure !", vbInformation
Exit Sub
End If
Set objTableRecords = xDoc.documentElement.selectNodes("rptRetailProductSales_Result")
For Each objTable In objTableRecords
For Each objTableColumns In objTable.childNodes
Next objTableColumns
Next objTable
End Sub
Apologies, I wasn't very clear in my last reply. The objTablerecords is empty so the for each loop just gets skipped over. I've attached 2 .xml files, the "BinLocations.xml" works 100% but the "retailsales.xml" doesn't work at all and I cant for the life of me figure out why. Can you spot any reasons why the retailsales.xml wont work ?
Your retailsales.xml uses a non-null/blank namespace. So, you must use that namespace in your xml queries.
The namespaces used, bolded is the default
xmlns:i="http://www.w3.org/2001/XMLSchema-instance
xmlns="http://schemas.datacontract.org/2004/07/DataAccess
Problem with DOM is that these aren't loaded automatically, you'll need to do it yourself. This can get a bit messy, but in cases where the entire xml uses the default namespace, this is relatively easy...
xmlDoc below is the loaded xml file/text
1. Set a form/class/project wide variable
Code:
Private m_NS As String
' declared in declarations section or public in a module maybe
2. Get and add the namespace for query usage & give it nickname. Capital NS below is the nickname. Note, should no default be provided (as is the case in your other xml file), the m_NS variable remains empty/null string
Code:
m_NS = xmlDoc.documentElement.namespaceURI
If Len(m_NS) Then
xmlDoc.setProperty "SelectionNamespaces", "xmlns:NS='" & m_NS & "'"
m_NS = "NS:"
End If
Edited: The m_NS prefix will be used in all portions of your queries where you reference an element name. Example...
selectSingleNode(m_NS & "rptRetailProductSales_Result/" & m_NS & "intCost")
Last edited by LaVolpe; Jul 13th, 2018 at 07:09 AM.
Insomnia is just a byproduct of, "It can't be done"
Thank you so much for your reply, what you are saying makes sense but I just cannot get it to work. I keep getting the attached error on this line.
Code:
Set objTableRecords = xDoc.documentElement.selectNodes(m_NS & "rptRetailProductSales_Result")
here is the code sample I'm working with at the moment.
Code:
Private Sub LoadRetailSalesReport()
Dim xDoc As New MSXML2.DOMDocument60
Dim objTableRecords As MSXML2.IXMLDOMNodeList
Dim objTable As MSXML2.IXMLDOMNode
Dim objTableColumns As MSXML2.IXMLDOMElement
Set xDoc = New MSXML2.DOMDocument60
If Len(m_NS) Then
xDoc.setProperty "SelectionNamespaces", "xmlns:NS='" & m_NS & "'"
m_NS = "NS:"
End If
If xDoc.Load("C:\retailsales.xml") = False Then
MsgBox "Connection failure !", vbInformation
Exit Sub
End If
m_NS = xDoc.documentElement.namespaceURI
Set objTableRecords = xDoc.documentElement.selectNodes(m_NS & "rptRetailProductSales_Result")
For Each objTable In objTableRecords
For Each objTableColumns In objTable.childNodes
Next objTableColumns
Next objTable
End Sub
Thank you so much for your reply, what you are saying makes sense but I just cannot get it to work. I keep getting the attached error on this line.
Couple things. You are testing length of m_NS without even assigning it a value. You should've caught that
Also, you cannot retrieve the namespace until the document is loaded.
Here's your code with the correct order of statements
Code:
...
Set xDoc = New MSXML2.DOMDocument60
If xDoc.Load("C:\retailsales.xml") = False Then
MsgBox "Connection failure !", vbInformation
Exit Sub
End If
m_NS = xDoc.documentElement.namespaceURI
If Len(m_NS) Then
xDoc.SetProperty "SelectionNamespaces", "xmlns:NS='" & m_NS & "'"
m_NS = "NS:"
End If
Set objTableRecords = xDoc.documentElement.selectNodes(m_NS & "rptRetailProductSales_Result")
For Each objTable In objTableRecords
For Each objTableColumns In objTable.childNodes
Insomnia is just a byproduct of, "It can't be done"
Here's an approach that works generically on the "table-like" structured XML-input-sources you've posted
(it's also working LateBound - so no project-references are needed for XML or ADO).
The XMLtoRs-routine will parse the XML - filling and then returning an ADO-Recordset (also LateBound):
Code:
Private Function XMLtoRs(Source, RecordName As String)
Dim Doc, Rec, Col, Rs
Set Doc = CreateObject("MSXML2.DOMDocument")
If Not Doc.Load(Source) Then Err.Raise vbObjectError, , "XML-Load failure!"
For Each Rec In Doc.getElementsByTagName("*")
If Rec.baseName = RecordName Then
If IsEmpty(Rs) Then
Set Rs = CreateObject("AdoDB.Recordset")
For Each Col In Rec.childNodes: Rs.Fields.Append Col.baseName, 8: Next
Rs.open
End If
Rs.AddNew
For Each Col In Rec.childNodes: Rs(Col.baseName) = Col.Text: Next
End If
Next
If Not IsEmpty(Rs) Then Rs.UpdateBatch: Set XMLtoRs = Rs
End Function
For testing-purposes, you can simply put an MSHFlexGrid-Control onto a Form
(which also contains the above routine) and in addition the following code:
Code:
Option Explicit
Private Sub Form_Load()
' Set MSHFlexGrid1.DataSource = XMLtoRs("C:\temp\BinLocations.xml", "BinLocations")
Set MSHFlexGrid1.DataSource = XMLtoRs("C:\temp\RetailSales.xml", "rptRetailProductSales_Result")
End Sub
You can also do this by wrapping an XML DOM within a data source class.
Code:
Dim F As Integer
Dim XML() As Byte
Dim DSO As XmlDataSource
Dim RS As ADODB.Recordset
F = FreeFile(0)
Open "retailsales.xml" For Input As #F
XML = input$(LOF(F), #F)
Close #F
Set DSO = New XmlDataSource
DSO.XML = XML
Set RS = New ADODB.Recordset
With RS
.DataMember = ""
Set .DataSource = DSO.DataAdapter
End With
Set MSHFlexGrid1.DataSource = RS
MSHFlexGrid1.ColWidth(0) = 270
Last edited by dilettante; Jul 14th, 2018 at 11:49 PM.
Reason: added a few comments
Though this doesn't apply to the sample xml file provided in this thread, but using an ADO recordset may only work well with the simplest XML files and/or those that don't use multiple namespaces.
For example, both solutions provided by Olaf and dilettante will fail with a typical manifest XML.
dilettante's reads zero columns for the recordset because the 1st child element may have no sub-elements
Olaf's errors out because it attempts to create multiple fields using the same field name when a child element name exists more than once: dependency, supportedOS, etc
Above being said, simple recordsets are a fine example of handling XML, when they can be used. In your case, these should work also. Assumption: XML format doesn't change that would break the ability to use a recordset.
Insomnia is just a byproduct of, "It can't be done"
Well you can always use the XML Simple Provider too. While it requires a source file name or URL instead of a raw XML in memory, it can handle most XML. The result is a hierarchical Recordset.
You always need to know the schema of the XML data to make productive use of it as a tabular data source. Jamming in random XML doesn't make a lot of sense, especially since most XML isn't tabular. So I'm not sure what the point of this remark is except to state what should be obvious: Recordsets are not XML DOMs.
If you had XML where portions of it are tabular you could always designate the "row tags" or parent element of a group of "rows" via the DataMember property and the class can extract and process those instead of all nodes under the document node. You could even get fancier and design a query syntax to pass via DataMember instead of a "table name" within the document.
The main reason to present XML "rows" as a Recordset is to enable data binding. Another reason is to make XML data easy to "plug in" to existing Recordset-based code.
@dilettante. I found it interesting that your example can handle multiple child elements that use the same base name. For example, if some element had multiple child <Property Name="xxx"> elements where the difference in the element was the attribute value, the flexgrid/recordsert would show multiple "Property" columns. However, querying the bound recordset would be challenging, if the elements were not in a strict order, since querying RS("Property").Value only returns the 1st of those columns.
Since you use the nodeName vs baseName property of the element to create the recordset fields, any namespace is appended to the field name, i.e., <size> and <metric:size> would be two different field names. However, if prefixes weren't used in the xml elements, instead the xmlns attribute were used, then we'd have 2 fields with the same name again.
I like the idea how your example could potentially handle multiple namespaces and multiple same-named child elements. I'd think, however, this would require aliasing/cross-referencing recordset field names to enable querying the recordset with ADO.
Insomnia is just a byproduct of, "It can't be done"