quering XML files with SQL/XQuery in VBA

[bartekR]

Hi

I have managed to achieve the objective which was to import records from xml file that would match my parameter (e.g. price >100)into excel spreadsheet, however i have a sneaky suspission that there is an easier (and faster) way of doing this than using loops.

Ideally i would like to replace the loop part of my code with something like sql statement
"Select... from... where price>100" which would give me a recordset which then i could write to excel in one go.

i have found some info on XQuery but not including explanation on how to use it within VBA - can anyone help?
How do you connect connect to xml file, nest xml parameters in sql select statement?
Could you please drop me an example line of code or point me to a good tutorial on the net?

i am pasting below my code and xml file :
----------------------------------------------------------
<?xml version="1.0" ?>
- <GiltValuation>
<GiltInfo SecID="3328033" SecName="TREASURY STOCK 4 3/4" SecPrice="101.0228" />
<GiltInfo SecID="3383485" SecName="ALLIED IRISH BANKS 2003 5.625" SecPrice="82.0995" />
<GiltInfo SecID="3398199" SecName="ANGEL TRAINS FIN. 2003 5 1/4" SecPrice="99.5711" />
<GiltInfo SecID="7133880" SecName="PRINCIPAL FINL.GLB. 2001 6%" SecPrice="101.2217" />
<GiltInfo SecID="B00NY17" SecName="TREASURY STOCK 4 3/4" SecPrice="103.2779" />
<GiltValuation>

-----------------------------------------------------

Sub ImportXMLData()


Dim oXMLDOC As DOMDocument 'this represents the whole XMl document
Dim oXMLNode As IXMLDOMNode
Dim oXMLNodeList As IXMLDOMNodeList 'list matching nodes of XMl document
Dim sXPath As String 'XPath syntax

Dim sXMLFilePath As String ' Path to xml file is taken from excel cell
sXMLFilePath = Range("xml_path").Value

Set oXMLDOC = New DOMDocument

'Load XML file selected by user (take path from excel cell)
With oXMLDOC

.async = False
.validateOnParse = False
.Load (sXMLFilePath)

End With

'Find all nodes where security price attribute is higher than 100
sXPath = "//GiltValuation/GiltInfo[@SecPrice>100]"
Set oXMLNodeList = oXMLDOC.selectNodes(sXPath)

'In case of no-match:
If oXMLNodeList Is Nothing Then

MsgBox "XMl data not found"

Else


'test - show results in msgbox (show security name and price - which should be >100)
For Each oXMLNode In oXMLNodeList

result = result & vbNewLine & Left(oXMLNode.Attributes.getNamedItem("SecName").nodeValue, 10) & "---" & oXMLNode.Attributes.getNamedItem("SecPrice").nodeValue

Next
End If

MsgBox result

End Sub

 

[bartekR]

anyone?

 

[xlbo]

This isn't really to do with VBA - this is about syntax for XPath queries. That's probably why you'renot getting any responses

Whether you need to loop or not is less to do with VBA and more to do with the structure of your xml file....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[sjakiePP]

What you can do is get an application like XMLSpy. Here you can open you xmlfile and go to a location you are looking for. Then select "xopy xpath" with a right-mouse-click. This you can use in your code.

There are other programs like this, but i have not worked with them.

---------------------------------------------
Yes, the world is full of strange people.

 

[bartekR]

Ok, Geoff point taken.

I will try to rephrase to make it more VBA-ish.

My XPath query works fine - it does what i want (i.e. gets reference to all records in xml matching criterion price>100).

I guess the real question that i am clumsily trying to formulate is : how do i use vba (and MSXML library )to write queried xml data to excel spreadsheet in one go?

sjakiePP - thanks for suggestion i'll have a go at this later on.

 

[taupirho]

One way would to write data to excel more quickly , which I have used in the past, is to write the buffer containing your data to the clipboard. Once its there simply paste it into excel. It looks like from your code you're half way there already. In your result variable separate your fields with vbTAb and your records with vbnewline. Paste the result into the clipboard, select the cell you want to paste the clipboard to and . .. well paste it. The type of commands you want to be looking at are:

Dim mydata As DataObject
Set mydata = New DataObject

data_line = ""
data_block = String$(1000000, " ")
....

' some kind of loop structure(s) here normally to go thro each record and each field within the record

data_line = data_line & field1 & vbtab & field2 etc ...
data_block = data_block & data_line & vbnewline

' end loop

mydata.SetText Mid(data_block, 1, 1)
mydata.PutInClipboard
Cells(1, 1).Select
ActiveSheet.Paste



In order to understand recursion, you must first understand recursion.