MartyConnelly
martyconnelly at shaw.ca
Mon Apr 23 20:03:36 CDT 2007
Maybe when I finally get around to getting my IBM XML certification
if they don't stop moving the goal posts. Last time I went to try it,
they changed the exam and dropped XSL patterns as obsolete
after I finally figured them out and the penny dropped..
Yeah now I have to find out the differences between XQuery and LINQ.
NetFramework 3.5 is now out with changes to VSTO.
Jim DeMarco wrote:
>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
>>
>>
>>
>>
>
>
>
--
Marty Connelly
Victoria, B.C.
Canada