[AccessD] XQuery in VB 6

Option Compare Database
Option Explicit

Dim mcolRate As Collection
Sub testxml()
  Set mcolRate = New Collection
'find daily US dollar fixed rate vs Euro from Euro Central Bank
' via XPath

GrabXMLFile ("http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml")

Debug.Print mcolRate("USD")
MsgBox "US Euro Rate ECB " & mcolRate("USD")
End Sub
Public Function GrabXMLFile(ByRef AdviserXML As String)
  'http://www.ecb.int/stats/exchange/eurofxref/html/index.en.html

'Base currency is Euro so will have to do a conversion for USD
'Note the link for other pages with sources for XML etc.

  'http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml
    'On Error GoTo ErrorHandler
    'needs reference set to XML 4.0 and maybe ADO 2.8
    Dim oDOMDocument As MSXML2.DOMDocument40
    Dim oNodeList As IXMLDOMNodeList
    Dim oAdviserDetailsNode As IXMLDOMNode
    Dim oLowestLevelNode As IXMLDOMElement
    Dim oNode As IXMLDOMNode
    Dim objXMLDOMNamedNodeMap As IXMLDOMNamedNodeMap
     Dim xPError As IXMLDOMParseError
    Dim Mydb As Database
    Dim myrs As ADODB.Recordset
    Dim sTempValue As String
  
    Set oDOMDocument = New MSXML2.DOMDocument40

    oDOMDocument.async = False
    oDOMDocument.validateOnParse = True 'you may want to parse for errors
    oDOMDocument.resolveExternals = False
    oDOMDocument.preserveWhiteSpace = True
 
    'use if xml disk file
      If Not oDOMDocument.Load(AdviserXML) Then
        MsgBox ("XML File error")
             Set xPError = oDOMDocument.parseError
        DOMParseError xPError
      
       End If
   Set oAdviserDetailsNode = oDOMDocument.documentElement
     Debug.Print oDOMDocument.xml

   'use appropriate XPath expression to select nodes
  
  ' Set oNodeList = oAdviserDetailsNode.selectNodes("Envelope/Cube/Cube/@*")
  Set oNodeList = oAdviserDetailsNode.selectNodes("//@*")
    
     Debug.Print oNodeList.length
  
    For Each oNode In oNodeList
 
      '  Debug.Print "*" & oNode.Text; oNode.nodeName & "*"
      
        Select Case oNode.nodeName
            Case "currency"
                 sTempValue = oNode.Text
              
            Case "rate"
             'This path is used to store a variable on the collection
                On Error Resume Next
                 mcolRate.Remove sTempValue
                 mcolRate.Add oNode.Text, sTempValue
                   Debug.Print sTempValue & " rate " & oNode.Text
                On Error GoTo ErrorHandler
           
        End Select
     
      Next
    Set oNodeList = Nothing
    Set oDOMDocument = Nothing
    Set oAdviserDetailsNode = Nothing
    Set objXMLDOMNamedNodeMap = Nothing
    Exit Function

ErrorHandler:

  '  Call NewError.Raise(Err.Number, Err.Source, Err.Description)

End Function
Sub DOMParseError(xPE As IXMLDOMParseError)

    ' The document failed to load.
    Dim strErrText As String
    ' Obtain the ParseError object
    With xPE
    strErrText = "Your XML Document failed to load" & _
        "due the following error." & vbCrLf & _
        "Error #: " & .errorCode & ": " & xPE.reason & _
        "Line #: " & .Line & vbCrLf & _
        "Line Position: " & .linepos & vbCrLf & _
        "Position In File: " & .filepos & vbCrLf & _
        "Source Text: " & .srcText & vbCrLf & _
        "Document URL: " & .url
    End With
    Debug.Print strErrText

  Dim s As String
  Dim r As String
  Dim i As Long
 
   s = ""
  For i = 1 To xPE.linepos - 1
    s = s & " "
  Next
  r = "XML Error loading " & xPE.url & " * " & xPE.reason
  Debug.Print r
    'show character postion of error; tired of counting chars in xml file
  If (xPE.Line > 0) Then
    r = "at line " & xPE.Line & ", character " & xPE.linepos & vbCrLf & _
         xPE.srcText & vbCrLf & s & "^"
  End If
  Debug.Print r
    MsgBox strErrText, vbExclamation
End Sub


Jim DeMarco wrote:

>Thanks Marty.  I'll pass this on to my team.
>
>We're using XQuery but we're not strongly tied to it.  I had mentioned
>XPath but I thought that was used to access one piece of data (or one
>related set of nodes out of a structure).  We need to  return select
>nodes based on criteria.  Is this an accurate description of differences
>betweent the two do you think?
>
>
>Jim
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of MartyConnelly
>Sent: Thursday, April 19, 2007 12:59 PM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] XQuery in VB 6
>
>I have only used XQuery with SQL 2005 & VB.Net I have XPath examples in
>VBA
>
>Are you using XQuery or XPath?
>
> XPath is imbedded in both XSLT and XQuery. In those languages it serves
>the role of node-set identification (selection)
>
>XQuery example
>
>Dim sql_getbank As String = "SELECT
>Demographics.query('data(//BankName)') " _
>
>                          & "FROM Store WHERE CustomerID = @CustomerID"
>
>
>XPath example
>
>    Set oAdviserDetailsNode = oDOMDocument.documentElement
>   'use appropriate XPath expression to select nodes
>   Set oNodeList =
>oAdviserDetailsNode.selectNodes("//BusinessDetails/*")
>
>Have a look at
>SQL Server 2005 XQuery and XML-DML - Part 2 By Alex Homer
>
>http://www.15seconds.com/issue/050811.htm
>
>
>Jim DeMarco wrote:
>
>  
>
>>X-posted AccessD, VB
>>
>>Hello All,
>>
>>I've been absent for a while but an issue has just come up that I hope 
>>someone can help with.
>>
>>Does anyone have any VB 6 code that uses XQuery to return a set of 
>>nodes?  I find plenty of XQuery examples that show the query and the 
>>returned nodes but no VB 6 implementation.
>>
>>Any short code snip will do.
>>
>>TIA,
>>
>>Jim DeMarco
>>    
>>
-- 
Marty Connelly
Victoria, B.C.
Canada