[AccessD] XQuery in VB 6

Jim DeMarco Jdemarco at hudsonhealthplan.org
Mon Apr 23 07:16:13 CDT 2007


I'll second that. 


Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Ervin Brindza
Sent: Friday, April 20, 2007 2:42 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] XQuery in VB 6

Marty,
when will be your MVP announcement?
Regards,
    Ervin
----- Original Message -----
From: "MartyConnelly" <martyconnelly at shaw.ca>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Friday, April 20, 2007 3:02 AM
Subject: Re: [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
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
> -- 
> Internal Virus Database is out-of-date.
> Checked by AVG Free Edition.
> Version: 7.5.446 / Virus Database: 268.18.24/742 - Release Date:
4/1/2007 
> 8:49 PM
>
> 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list