[AccessD] XQuery in VB 6

MartyConnelly martyconnelly at shaw.ca
Thu Apr 19 19:59:22 CDT 2007


'criteria grab all books with price >20


Dim mcolRate As Collection
Sub testxmlinventory()
http://cairocafe.blogspot.com/2006/09/xpath-intro.html
http://msdn2.microsoft.com/en-us/library/ms256086.aspx
  Set mcolRate = New Collection

GrabXMLFile ("C:\Access files\xmlfiles\inventory.xml")

Debug.Print "mcol " & mcolRate(1)

End Sub
Public Function GrabXMLFile(ByRef AdviserXML As String)

    '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
    Dim i As Long
    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("/bookstore/book/price")
   Set oNodeList = 
oAdviserDetailsNode.selectNodes("/bookstore/book[price>20]/price")
     Debug.Print "Length = " & oNodeList.length
   i = 0
    For Each oNode In oNodeList
    i = i + 1
       Debug.Print "*" & oNode.Text & " " & oNode.nodeName & "*"
      
        Select Case oNode.nodeName
            Case "price"
             'This path is used to store a variable on the collection
                On Error Resume Next
                sTempValue = oNode.nodeName & CStr(i)
                 mcolRate.Remove sTempValue
                 mcolRate.Add oNode.Text, sTempValue
                   Debug.Print sTempValue & " price " & 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

inventory.xml

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="myfile.xsl" ?>
<bookstore specialty="novel">
  <book style="autobiography">
    <author>
      <first-name>Joe</first-name>
      <last-name>Bob</last-name>
      <award>Trenton Literary Review Honorable Mention</award>
    </author>
    <price>12</price>
  </book>
  <book style="textbook">
    <author>
      <first-name>Mary</first-name>
      <last-name>Bob</last-name>
      <publication>Selected Short Stories of
        <first-name>Mary</first-name>
        <last-name>Bob</last-name>
      </publication>
    </author>
    <editor>
      <first-name>Britney</first-name>
      <last-name>Bob</last-name>
    </editor>
    <price>55</price>
  </book>
  <magazine style="glossy" frequency="monthly">
    <price>2.50</price>
    <subscription price="24" per="year"/>
  </magazine>
  <book style="novel" id="myfave">
    <author>
      <first-name>Toni</first-name>
      <last-name>Bob</last-name>
      <degree from="Trenton U">B.A.</degree>
      <degree from="Harvard">Ph.D.</degree>
      <award>Pulitzer</award>
      <publication>Still in Trenton</publication>
      <publication>Trenton Forever</publication>
    </author>
    <price intl="Canada" exchange="0.7">6.50</price>
    <excerpt>
      <p>It was a dark and stormy night.</p>
      <p>But then all nights in Trenton seem dark and
      stormy to someone who has gone through what
      <emph>I</emph> have.</p>
      <definition-list>
        <term>Trenton</term>
        <definition>misery</definition>
      </definition-list>
    </excerpt>
  </book>
  <my:book xmlns:my="uri:mynamespace" style="leather" price="29.50">
    <my:title>Who's Who in Trenton</my:title>
    <my:author>Robert Bob</my:author>
  </my:book>
</bookstore>

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




More information about the AccessD mailing list