XML -> Recordset (was: RE: [thelist] XML)

[mia at miaridge.com]

On Fri, 18 Jan 2002, Ken Kogler wrote:

> So the question is: can I turn an XML file into a recordset? And if not,

Below is some sample code that grabs data from an (uploaded) xml document
and assigns it to ASP variables that can then be used to write a SQL
statement.

A bunch of caveats: I've left out variable declarations; there are
probably more elegant ways to do it (e.g. you could probably get away with
just one XMLDOM object); watch for linewraps; and some of it will be
particular to my requirements (e.g. the Load statement) and the structure
of my XML document.

This snippet sets up the XMLDOM objects, and uses XPath to loop through
the 'user' nodes in 'userList', assigning the data in each node to
variables that I use later to build a SQL statement for insertion in a SQL
database.  I've included a sample XML document at the end, but I haven't
tested the code and data after generalising it.

HTH!

Mia


'''''''''''''''''''''''''''''''''''''''''''
the ASP code
'''''''''''''''''''''''''''''''''''''''''''

' set up the XMLDOM objects
Set objDisplayXML = Server.CreateObject("Microsoft.XMLDOM")
Set objDisplayElement = Server.CreateObject("Microsoft.XMLDOM")
objDisplayXML.async = False ' you probably want this to be true (default)
objDisplayXML.Load (Server.MapPath(myXMLdocument)) ' where myXMLdocument
is the name of your doc

set objDisplayElement = objDisplayXML.documentElement

set displayNode =
objDisplayElement.selectSingleNode("/myrootnode/userList").childNodes
set displayChild =
objDisplayElement.selectSingleNode("/myrootnode/userList").firstChild


' now loop through the nodes
for i = 0 to displayNode.length - 1 ' loop through the doc
looking for our data
	for j = 0 to displayChild.childNodes.length - 1 ' loop through the
record

		' get the user's id as displayId
		if displayChild.childNodes.item(j).nodeName = "id"
then
			displayId =
displayChild.childNodes.item(j).text

		end if


		' get the user's name as displayName
		if displayChild.childNodes.item(j).nodeName =
"name" then
			displayName =
displayChild.childNodes.item(j).text
		end if


		' get the user's email as displayEmail
		if displayChild.childNodes.item(j).nodeName =
"email" then
			displayEmail =
displayChild.childNodes.item(j).text
		end if


	next ' move to the next user in userList
	set displayChild = displayChild.nextSibling

	' do whatever is appropriate for your project with the variables
so they're not
	' overwritten with data from the next user
	' for example, add them to an 'INSERT' statement as appropriate

next

'''''''''''''''''''''''''''''''''''''''''''
the sample xml data
'''''''''''''''''''''''''''''''''''''''''''
<?xml version="1.0" encoding="utf-8" ?>
<myrootnode>
<userList>
<User>
	<id>1</id>
	<name>Administrator</name>
	<email>admin at dw.com</email>
</User>
<User>
	<id>29</id>
	<name>mia</name>
	<email>x at x.x</email>
</User>
</userList>
</myrootnode>