Ervin Brindza
viner at EUnet.yu
Fri Apr 20 01:42:21 CDT 2007
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
>
>