A disconnected ADO recordset from an XML string sounds great, but how?

Hi all!

It must be friday fogging up my head. I can't seem to get a correctly
formatted XML string into a recordset object so that I can access any
value within the lifecycle of the object (an ASP page, in this case).
I found some mighty interesting VB snippets, though it got me nowhere.

Has anyone of you guys & gals ever wrestled with this sorta thing? If
so, what is the best way to get around this matter? I googled a lot,
and the following URL shows something that might be a good start, but
still ... http://abstractvb.com/code.asp?A=814

Ah well, to make it short, does anyone of you know of an example that
enlightens my blurred vision?

Thanks in advance!

Best regards,
- Alex

Re: A disconnected ADO recordset from an XML string sounds great, but how?

Alex wrote:
> Hi all!
>
> It must be friday fogging up my head. I can't seem to get a correctly
> formatted XML string into a recordset object so that I can access any
> value within the lifecycle of the object (an ASP page, in this case).

?
The string will exist for the lifetime of the page if it's declared outside
of a procedure.

> I found some mighty interesting VB snippets, though it got me nowhere.
>
> Has anyone of you guys & gals ever wrestled with this sorta thing? If
> so, what is the best way to get around this matter? I googled a lot,
> and the following URL shows something that might be a good start, but
> still ... http://abstractvb.com/code.asp?A=814
>
> Ah well, to make it short, does anyone of you know of an example that
> enlightens my blurred vision?
>
> Thanks in advance!
>
> Best regards,
> - Alex

It's just a string. What is the problem?

dim rs
'****this line is not needed if you've defined the ado constants*****
const adVarChar=200
'*********************************************************** ***************
set rs=createobject("adodb.recordset")
rs.fields.append "xml",adVarchar,8000
rs.open
rs.addnew
rs(0)=yourxmlstring
rs.update

Actually, I don't understand why you are putting it into a recordset. Are
you planning to store it in a database? i hope you aren't planning to store
the recordset in Session or Application ... http://www.aspfaq.com/2053
Or is the problem that you are unaware of the existence of the xml
domdocument object? You can store a free-threaded domdocument in Session or
Application with no problems. Plus, using the methods provided by the msxml
parser (selectnodes, selectsinglenode) you can access any value within the
domdocument ... without creating a recordset object.

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: A disconnected ADO recordset from an XML string sounds great, but how?

Hi Bob!
I was kinda hoping you'd jump in, given your huge lot of golden ASP
medals earned in this group. The problem is not that easy to explain.
First of all, I used to be able to use msxml3.dll. Somehow since the
las XP-patchday usage of the DLL by
"Server.CreateObject("MSXML2.ServerXMLHTTP")" results in a "module not
found" error. This is just the machine I develop on, the "real"
server (Win2003) continues to work smoothly. I tried installing
servicepacks of the DLL (upto 7 I believe) & dug into the registry to
see if I can see anything being missing or erronous. I won't get into
all that too much, it merely leads to my confusion. I resorted to
using "Server.CreateObject("Microsoft.XMLDOM")" and
"MSXML2.ServerXMLHTTP.3.0" instead. They both complained about the
WinHTTP.dll being missing, so I got that from my Win2003 server &
regsvr'ed it. It seems to be working, albeit that I sometimes stumble
across funny "method not supported' messages while diving into stuff
like 'Set objChildNodes =
objXMLDoc.documentElement.childNodes.item(i).childNodes". Especialy
when I want to iterate/traverse the entire XML branche. I have read a
lot about the XML DOM, I admit I'm very new at it at that level,
though I believe I'm not wanting the impossible; an array with one
level of all nodenames, disregarding any nodelevels, and the other
level carrying all the accompanying values. I have an app that needs
to talk to a different server through a socket. The asynchronous stuff
works, I get the XML back, I can parse the XML & use the XSLProcessor
to display it in a HTML page. However, I need some values , which I do
not know yet, for later use (on the same page). I wrestled with the
XML DOM, & so far, the easiest solution works best;

Function getField(strXML,knoop)
Dim objDoc, objNodeList, arrResponse(), i

Set objDoc = Server.CreateObject("Microsoft.XMLDOM")
objDoc.async = False
objDoc.LoadXML strXML

Set objNodeList = objDoc.documentElement.selectNodes(knoop)
ReDim arrResponse(objNodeList.length)
For i = 0 To objNodeList.length - 1
arrResponse(i) = objNodeList.item(i).Text
Next
getField = arrResponse
End Function

I couldn't get the loop right to traverse through each node & create
the array I want. It instead ceated the array allright, but it
combines node values whenever there are subnodes. So, this XML:



2006-05-08 11:23:27
00033
192.168.0.78


0
0
0
0



10
773
qtid ontbreekt




results in an array with 3 levels, the first (0) containing
"2006-05-08 11:23:2700033192.168.0.78", the second (1) '0000" and the
third (2) "'10773qtid ontbreekt". There's no division between the
values, disabling the selection I want to make. Whenever I start
mashing loops in, as I mentioned above, I stumble upon "method not
supported" errors. Then again, my XML DOM knowledge is not profound
yet.

So, I then thought of setting the array values from within the XSL
file, by adding a simple ASP object to the XSL header. This works
great, although I can't seem to access the XML values like so (in the
XSL):

"
select="xasp:SetSessionVariable('qtid','$qtester')" />"

Going back to the drawingboard, I thought of creating the recordset,
as I can access all values at will throughout the page. Maybe it
indeed is overkill, maybe I'm overlooking a simple "for each"
statwement in my messing around with the following

"(--this is inside a class--)
Public Property Get GetNodesArray(xpath, ByVal arrNodes)
CheckConnectionState()

Dim objNodeList, i, j
Set objNodeList =
objXML.documentElement.selectNodes(xpath)
intNumNodes = objNodeList.length
If intNumNodes > 0 Then
intNumChildNodes =
objNodeList.item(0).childNodes.Length

ReDim arrNodes(intNumNodes, intNumChildNodes)
For i = 0 To intNumNodes-1
For j = 0 To intNumChildNodes-1
arrNodes(i,j) =
objNodeList.item(i).childNodes.Item(j).Text
Next
Next
Else
ReDim arrNodes(0)
End If
Set objNodeList = Nothing
GetNodesArray = arrNodes
End Property
"

So, to round it all, the function GetField works. This implies though
that I need to know all about each & every XML response I'm getting
back from the socket communication. This might be the case after all,
but I believe there must be a more generic solution somehow, like
building the page according to the array instead of at XML level, just
to keep the functions & classes more generic & easier to maintain for
everybody.

However, I do realize I ran into way to many funny errors.
Hardwarepeople have checked the machine, & found no resolution. Maybe
you don't even see where my problem starts due to the massive mesage
:-) Sorry for that. I most probably am making something trivial very
difficult.

Thanks for your time, whichever way!
- Alex.

On Fri, 12 May 2006 09:41:56 -0400, "Bob Barrows [MVP]"
wrote:

>Alex wrote:
>> Hi all!
>>
>> It must be friday fogging up my head. I can't seem to get a correctly
>> formatted XML string into a recordset object so that I can access any
>> value within the lifecycle of the object (an ASP page, in this case).
>
>?
>The string will exist for the lifetime of the page if it's declared outside
>of a procedure.
>
>> I found some mighty interesting VB snippets, though it got me nowhere.
>>
>> Has anyone of you guys & gals ever wrestled with this sorta thing? If
>> so, what is the best way to get around this matter? I googled a lot,
>> and the following URL shows something that might be a good start, but
>> still ... http://abstractvb.com/code.asp?A=814
>>
>> Ah well, to make it short, does anyone of you know of an example that
>> enlightens my blurred vision?
>>
>> Thanks in advance!
>>
>> Best regards,
>> - Alex
>
>It's just a string. What is the problem?
>
>dim rs
>'****this line is not needed if you've defined the ado constants*****
>const adVarChar=200
>'********************************************************** ****************
>set rs=createobject("adodb.recordset")
>rs.fields.append "xml",adVarchar,8000
>rs.open
>rs.addnew
>rs(0)=yourxmlstring
>rs.update
>
>Actually, I don't understand why you are putting it into a recordset. Are
>you planning to store it in a database? i hope you aren't planning to store
>the recordset in Session or Application ... http://www.aspfaq.com/2053
>Or is the problem that you are unaware of the existence of the xml
>domdocument object? You can store a free-threaded domdocument in Session or
>Application with no problems. Plus, using the methods provided by the msxml
>parser (selectnodes, selectsinglenode) you can access any value within the
>domdocument ... without creating a recordset object.
>
>Bob Barrows

Re: A disconnected ADO recordset from an XML string sounds great, but how?

"Alex" wrote in message
news:bk5962927gadsbn8me4fb3b9tpmfqidjg9@4ax.com...
> Hi Bob!
> I was kinda hoping you'd jump in, given your huge lot of golden ASP
> medals earned in this group. The problem is not that easy to explain.
> First of all, I used to be able to use msxml3.dll. Somehow since the
> las XP-patchday usage of the DLL by
> "Server.CreateObject("MSXML2.ServerXMLHTTP")" results in a "module not
> found" error.


This is usually caused by the WinHTTP component not being present and/or
registered.

Find it in System32 and register it.

Re: A disconnected ADO recordset from an XML string sounds great, but how?

Alex wrote:
> arrResponse(i) = objNodeList.item(i).Text

Text is the wrong property to use if you don't want child nodes. You need to
be aware of what type of node you are dealing with.

> Next
> getField = arrResponse
> End Function
>
> I couldn't get the loop right to traverse through each node & create
> the array I want.

I still don't understand why you are moving the data into another structure.
For one thing, this xml contains nodes with different numbers of child
nodes, requiring you to use an array of arrays. Personally, I would just
leave it in the xml document. This is not a very complicated structure and
you should be able to parse any value you want out of it using xpath
queries. Here is an example I created after modifying your sample xml (see
after the code):

<%@ Language=VBScript %>
<%
dim xdoc
set xdoc=createobject("msxml2.domdocument")
xdoc.load(server.MapPath("xml/transaction.xml"))

WriteParsedXMLtoResponse

Response.Write "
Time of first transaction: "
createinput "/transactions/transaction/request/time"

Response.Write "
IP of second transaction: "
createinput "/transactions/transaction/request" & _
"[time='2006-05-08 11:23:27']/ip"

Response.Write "
Prodid of second transaction: "
createinput "/transactions/transaction[request" & _
"/time='2006-05-08 11:23:27']/input/prodid"

sub WriteParsedXMLtoResponse()
dim root,node
set root=xdoc.documentElement
Response.Write root.nodename
for each node in root.childNodes
ProcessChildNode node,1
next
end sub

sub ProcessChildNode(pNode, pLevel)
dim node, val
if pNode.nodeTypeString="text" then
val=pNode.nodevalue
Response.Write ": " & val
elseif pNode.nodeTypeString="element" then
Response.Write "
"
Response.Write replace(space(pLevel*2)," "," ") & _
pNode.nodeName
for each node in pNode.childNodes
ProcessChildNode node, pLevel + 1
next
end if
end sub

sub CreateInput(xpath)
dim node
set node=xdoc.selectSingleNode(xpath)
Response.Write node.nodename & ": node.nodename& """ value=""" & node.text & _
""">
"
if node.childnodes.length = 0 then
else
end if
end sub
%>
------------------------------------------------------------ --------------------------
xml saved in xml/transaction.xml:




2006-04-08 11:23:27
00053
192.168.0.78


0
0
0
0






2006-05-08 11:23:27
00033
192.168.0.83


g
f
abc
1



10
773
qtid ontbreekt







HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: A disconnected ADO recordset from an XML string sounds great, but how?

Hi Anthony!

I found that out already as I wrote to Bob, but thanks anyway! I
indeed found much info on the matter through Google, though somehow
each & every resolution offered didn't do the trick for me. I also
regsvr'ed that WinHTTP.dll which I copied from a Win2003 server, which
is stated to have the latest version of the file for all platforms.
This in itself didn't resolve anything either, untill I started to use
"MSXML2.ServerXMLHTTP.3.0" instead of "MSXML2.ServerXMLHTTP". The
addition of the .3.0 seems to work slightly different. Before I
regsvr'ed the Win2003 WinHTTP.dll the addition of .3.0 resulted in
the message that WinHTTP.dll was not present. This led me to the
(temporary?) solution.

Your answer is correct, allbeit that my machine had no WinHTTP.dll
anywhere. Aparantly, WinHTTP.dll is not distributed with WinXP &/or
later versions of MSXML[n].DLL. I could not find any information about
the "proper" msxml version to use. Is it possible to complete
uninstall any msxml versions present, & install only the "proper"
version? Somewhere along the line something went AWOL &/or wrong with
my machine, maybe.

Anyway, thanks again!
- Alex.

On Fri, 12 May 2006 17:06:12 +0100, "Anthony Jones"
wrote:

>
>"Alex" wrote in message
>news:bk5962927gadsbn8me4fb3b9tpmfqidjg9@4ax.com...
>> Hi Bob!
>> I was kinda hoping you'd jump in, given your huge lot of golden ASP
>> medals earned in this group. The problem is not that easy to explain.
>> First of all, I used to be able to use msxml3.dll. Somehow since the
>> las XP-patchday usage of the DLL by
>> "Server.CreateObject("MSXML2.ServerXMLHTTP")" results in a "module not
>> found" error.
>
>
>This is usually caused by the WinHTTP component not being present and/or
>registered.
>
>Find it in System32 and register it.
>
>

Re: A disconnected ADO recordset from an XML string sounds great, but how?

Hi Bob!

Yes, an array of arrays is right on the nose, that was my intention.
However, when I was pondering about it over the weekend I didn't see
the advantage of building a more complicated structure of values then
it already was presented in. So, I see y'r point now :-)

I haven't fiddled around your code yet, I will do so later on today.
It looks inetersting enough, for sure! Thanks for that, & for your
time, ofcourse. I'll need to change the load events to loadxml
counterparts, as I'll recieve strings, not files, & the XML is not
formatted by me, but by a piece of software I have no say about, so
I'm losing the extra "transactions" tag, but I guess that won't do
much harm.

Thanks again Bob! Very much appreciated indeed!
Best regards,
- Alex.


On Fri, 12 May 2006 13:00:08 -0400, "Bob Barrows [MVP]"
wrote:

>Alex wrote:
>> arrResponse(i) = objNodeList.item(i).Text
>
>Text is the wrong property to use if you don't want child nodes. You need to
>be aware of what type of node you are dealing with.
>
>> Next
>> getField = arrResponse
>> End Function
>>
>> I couldn't get the loop right to traverse through each node & create
>> the array I want.
>
>I still don't understand why you are moving the data into another structure.
>For one thing, this xml contains nodes with different numbers of child
>nodes, requiring you to use an array of arrays. Personally, I would just
>leave it in the xml document. This is not a very complicated structure and
>you should be able to parse any value you want out of it using xpath
>queries. Here is an example I created after modifying your sample xml (see
>after the code):
>
><%@ Language=VBScript %>
><%
>dim xdoc
>set xdoc=createobject("msxml2.domdocument")
>xdoc.load(server.MapPath("xml/transaction.xml"))
>
>WriteParsedXMLtoResponse
>
>Response.Write "
Time of first transaction: "
>createinput "/transactions/transaction/request/time"
>
>Response.Write "
IP of second transaction: "
>createinput "/transactions/transaction/request" & _
> "[time='2006-05-08 11:23:27']/ip"
>
>Response.Write "
Prodid of second transaction: "
>createinput "/transactions/transaction[request" & _
> "/time='2006-05-08 11:23:27']/input/prodid"
>
>sub WriteParsedXMLtoResponse()
> dim root,node
> set root=xdoc.documentElement
> Response.Write root.nodename
> for each node in root.childNodes
> ProcessChildNode node,1
> next
>end sub
>
>sub ProcessChildNode(pNode, pLevel)
> dim node, val
> if pNode.nodeTypeString="text" then
> val=pNode.nodevalue
> Response.Write ": " & val
> elseif pNode.nodeTypeString="element" then
> Response.Write "
"
> Response.Write replace(space(pLevel*2)," "," ") & _
> pNode.nodeName
> for each node in pNode.childNodes
> ProcessChildNode node, pLevel + 1
> next
> end if
>end sub
>
>sub CreateInput(xpath)
> dim node
> set node=xdoc.selectSingleNode(xpath)
> Response.Write node.nodename & ": > node.nodename& """ value=""" & node.text & _
> """>
"
> if node.childnodes.length = 0 then
> else
> end if
>end sub
>%>
>----------------------------------------------------------- ---------------------------
>xml saved in xml/transaction.xml:
>
>
>
>
> 2006-04-08 11:23:27
> 00053
> 192.168.0.78
>
>
> 0
> 0
> 0
> 0
>
>
>
>
>
>
> 2006-05-08 11:23:27
> 00033
> 192.168.0.83
>
>
> g
> f
> abc
> 1
>
>
>
> 10
> 773
> qtid ontbreekt
>
>
>
>
>
>
>
>HTH,
>Bob Barrows

Re: A disconnected ADO recordset from an XML string sounds great, but how?

"Alex" wrote in message
news:ddgg629anhtc1glu4ehaavi4oj5s9ae4po@4ax.com...
> Hi Anthony!
>
> I found that out already as I wrote to Bob, but thanks anyway! I
> indeed found much info on the matter through Google, though somehow
> each & every resolution offered didn't do the trick for me. I also
> regsvr'ed that WinHTTP.dll which I copied from a Win2003 server, which
> is stated to have the latest version of the file for all platforms.
> This in itself didn't resolve anything either, untill I started to use
> "MSXML2.ServerXMLHTTP.3.0" instead of "MSXML2.ServerXMLHTTP". The
> addition of the .3.0 seems to work slightly different. Before I
> regsvr'ed the Win2003 WinHTTP.dll the addition of .3.0 resulted in
> the message that WinHTTP.dll was not present. This led me to the
> (temporary?) solution.
>
> Your answer is correct, allbeit that my machine had no WinHTTP.dll
> anywhere. Aparantly, WinHTTP.dll is not distributed with WinXP &/or
> later versions of MSXML[n].DLL. I could not find any information about
> the "proper" msxml version to use. Is it possible to complete
> uninstall any msxml versions present, & install only the "proper"
> version? Somewhere along the line something went AWOL &/or wrong with
> my machine, maybe.

WinHTTP should be present on a Windows2003 box.
Windows 2000 didn't ship with it initially but comes with a service pack.

I think if you compare the pre-requisites of your choosen install of MSXML
you will find that it requires a certain SP level of windows 2000.

>
> Anyway, thanks again!
> - Alex.
>
> On Fri, 12 May 2006 17:06:12 +0100, "Anthony Jones"
> wrote:
>
> >
> >"Alex" wrote in message
> >news:bk5962927gadsbn8me4fb3b9tpmfqidjg9@4ax.com...
> >> Hi Bob!
> >> I was kinda hoping you'd jump in, given your huge lot of golden ASP
> >> medals earned in this group. The problem is not that easy to explain.
> >> First of all, I used to be able to use msxml3.dll. Somehow since the
> >> las XP-patchday usage of the DLL by
> >> "Server.CreateObject("MSXML2.ServerXMLHTTP")" results in a "module not
> >> found" error.
> >
> >
> >This is usually caused by the WinHTTP component not being present and/or
> >registered.
> >
> >Find it in System32 and register it.
> >
> >
>