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