Can you read an XML file into an ADO recordset - if so, how 1

[SBendBuckeye]

I have an XML file that was created by Excel. From reading other threads on this topic, it doesn't sound like Import works all that well. Can I read it directly into an ADO recordset? If so, could someone post a little bit of code to get me started?

Thanks!

Have a great day!

j2consulting@yahoo.com

 

[PeteJohnston]

I've mailed something to the address you gave which I hope helps you. I forgot to make it clear that I got the meaty part of the OpenXML function in the rwInetXfer module from Ryan Woodward, who I've never met, but he has my undying thanks anyway.


PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[CajunCenturion]

Pete - I'm sure that SBendBuckeye appreciates you sending the email, but I don't know how much good that does anyone else who has an interest in this question. The value of a public forum such as this is in the availability and access to the information.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

Just out of curiosity, does this code use the MS XML Document Object Model?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[SBendBuckeye]

Pete,

Thanks for sending the sample to me. I am currently getting a dll error so I don't know whether it will help me or not.

CC,

No it does not. It is a series of API calls almost all of which are new to me so I can't elaborate in any meaningful sense.

Have a great day!

j2consulting@yahoo.com

 

[CajunCenturion]

I hope it all works out for you SBendBuckeye.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[evalesthy]

I agree with CajunCenturion - there are others of us out here trying to wrestle with/learn the same issues. It would be more helpful if these 'solutions' could be posted to the forum for the benefit of all unless there is come compelling reason to only share them one-on-one. Thanks.

 

[PeteJohnston]

Sorry CajunCenturion, I totally agree with you about the public forum thing but the code was a bit too big to just post it in this thread and I couldn't remember the link to where I found Ryan Woodward's solution. I've added the code as a FAQ this morning under the heading "Read text from a web-site", but I don't know how to find out the link. Could anyone else help there?

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[CajunCenturion]

That's great PeteJohnston, should be appreciated by those you need to use the function from the wininet api, not only for reading but some of the those very same API's are used for FTP operations as well.

The FAQ number is shown immediately below the title of the FAQ as a link, and what I do, is copy and paste the FAQ number

faq705-4579

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[HamotIntern]

Here's how I did it. This puts the XML into a record set.

Function Main()
Dim strConnect
strConnect = "Provider=MSPersist"
Dim rs
SET rs = CreateObject ("ADODB.Recordset")
Set SQLcn = CreateObject("ADODB.Connection")
SQLcn.ConnectionString = "Driver={SQL Server}; Server= [Server Name]; DATABASE=[DB Name]; UID= [UID]; PWD= [PWD];"
SQLcn.Open
rs.Open "[Full path to XML Document]", strConnect
Set GetVBOSPData= rs
'Do while not rs.eof
'msgbox(rs.fields(6)) [Commented out because while good for troubleshooting thing, it produces many message boxes.]
'rs.movenext
'loop
Main = DTSTaskExecResult_Success
End Function