Results 1 to 15 of 15

Thread: How to read XML result from web api

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2005
    Location
    South Africa
    Posts
    400

    How to read XML result from web api

    Hi All

    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 ?


    many thanks in advance.
    AJ.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: How to read XML result from web api

    You don't need a file. The XML Dom object has a LoadXML method where you can feed it an XML String.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2005
    Location
    South Africa
    Posts
    400

    Re: How to read XML result from web api

    Thank you techgnome, that worked like a charm.

    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

    Many thanks again.

    AJ

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: How to read XML result from web api

    beats me.... hard to tell w/o also seeing the xml... what's empty? objTableRecords? or objTable.childNodes?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2005
    Location
    South Africa
    Posts
    400

    Re: How to read XML result from web api

    Hi techgnome

    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 ?

    Thanks again.
    Attached Files Attached Files

  6. #6
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: How to read XML result from web api

    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
    3. Include the namespace nickname in all queries
    Code:
    MsgBox xmlDoc.documentElement.selectNodes(m_NS & "rptRetailProductSales_Result").length
    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"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2005
    Location
    South Africa
    Posts
    400

    Re: How to read XML result from web api

    Hi LaVolpe


    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.

    Name:  Error.png
Views: 794
Size:  6.3 KB


    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
    Many thanks.

  8. #8
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: How to read XML result from web api

    Quote Originally Posted by alexanderjames View Post
    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"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2005
    Location
    South Africa
    Posts
    400

    Re: How to read XML result from web api

    Thanks so much laVolpe

    WOW, I cant believe I missed that, what a rookie move

    Thanks a mil for all your help.

  10. #10
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: How to read XML result from web api

    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
    HTH

    Olaf

  11. #11
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to read XML result from web api

    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
    Name:  sshot.png
Views: 741
Size:  6.5 KB
    Attached Files Attached Files
    Last edited by dilettante; Jul 14th, 2018 at 11:49 PM. Reason: added a few comments

  12. #12
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: How to read XML result from web api

    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.
    Code:
    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <assembly manifestVersion="1.0" xmlns="urn:schemas-microsoft-com:asm.v1">
    	<assemblyIdentity name="Microsoft.Visual.Basic" version="6.00.9782.0" type="win32" processorArchitecture="x86"/>
    	<description>VB6</description>
    	<dependency>
    		<dependentAssembly>
    			<assemblyIdentity name="Microsoft.Windows.Common-Controls" version="6.0.0.0" type="win32" processorArchitecture="x86" publicKeyToken="6595b64144ccf1df" language="*"/>
    		</dependentAssembly>
    	</dependency>
    	<dependency>
    		<dependentAssembly>
    			<assemblyIdentity name="Microsoft.Windows.GdiPlus" version="1.1.0.0" type="win32" processorArchitecture="x86" publicKeyToken="6595b64144ccf1df" language="*"/>
    		</dependentAssembly>
    	</dependency>
    	<trustInfo xmlns="urn:schemas-microsoft-com:asm.v2">
    		<security>
    			<requestedPrivileges>
    				<requestedExecutionLevel level="highestAvailable" uiAccess="false"/>
    			</requestedPrivileges>
    		</security>
    	</trustInfo>
    	<compatibility xmlns="urn:schemas-microsoft-com:compatibility.v1">
    		<application>
    			<supportedOS Id="{e2011457-1546-43c5-a5fe-008deee3d3f0}"/>
    			<supportedOS Id="{35138b9a-5d96-4fbd-8e2d-a2440225f93a}"/>
    			<supportedOS Id="{4a2f28e3-53b9-4441-ba9c-d69d4a4a6e38}"/>
    			<supportedOS Id="{1f676c76-80e1-4239-95bb-83d0f6d0da78}"/>
    	 		<supportedOS Id="{8e0f7a12-bfb3-4fe8-b9a5-48fd50a15a9a}"/>
    
    		</application>
    	</compatibility>
    	<application xmlns="urn:schemas-microsoft-com:asm.v3">
    		<windowsSettings>
    			<dpiAware xmlns="http://schemas.microsoft.com/SMI/2005/WindowsSettings">true</dpiAware>
    			<dpiAwareness xmlns="http://schemas.microsoft.com/SMI/2016/WindowsSettings">system</dpiAwareness>
    			<longPathAware xmlns="http://schemas.microsoft.com/SMI/2016/WindowsSettings">true</longPathAware>
    		</windowsSettings>
    	</application>
    </assembly>
    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"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  13. #13
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to read XML result from web api

    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.

  14. #14
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: How to read XML result from web api

    @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"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  15. #15
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to read XML result from web api

    It is just a minimal example of an OLEDB Provider with its DataSource collapsed into it as a unified object.

    Here I just filled it in with enough code to handle XML of a specific category of "shape" as in:

    Code:
    <doc>
      <row>
        <col1>data1</col1>
        <col2>data2</col2>
      </row>
      <row>
        <col1>data1</col1>
        <col2>data2</col2>
      </row>
      :
    </doc>
    Obviously you could write such a class to be more versatile or have different behavior. It depends on the purpose at hand.

    For example you might want to process XML shaped more compactly as in:

    Code:
    <doc>
      <row col1="data1" col2="data2"/>
      <row col1="data1" col2="data2"/>
      :
    </doc>
    That would be a simple enough change.

    You might also modify the code to handle documents shaped like:

    Code:
    <doc>
      <list1>
        <row col1="data1" col2="data2"/>
        <row col1="data1" col2="data2"/>
        :
      </list1>
      <list2>
        <row colA="dataA" colB="dataB"/>
        <row colA="dataA" colB="dataB"/>
        :
      </list1>
    </doc>
    You could use a value passed via DataMember to name the specific "list" or "table" desired.


    I'm starting to feel like Doc Brown:

    Please excuse the crudity of this model as I didn't have time to build it to scale or paint it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width