View Single Post
  #1 (permalink)  
Old January 4th, 2011, 04:27 AM
loic2810 loic2810 is offline
Registered User
 
Join Date: Jan 2011
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Convert access data to xml

Hi,
I want to create a xml file from asp and access database.
I retreiving data from the database without any problem but i have to respect xml schema and i didn't succeed in.

Is someone can help me?

here the xml schema:
Code:
<photo image="images/01.jpg" bigimage = "images/big/01.jpg" target="_blank" lightboxinfo="Description for image 1" url="http://www.flashxml.net"><![CDATA[Hello]]></photo>
and here my asp code to generate xml:

Code:
<%
' Name of the access db being queried
accessdb="state_info" 

' Connection string to the access db
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & "F:\inetpub\vhosts\comptoirdumatelot.fr\httpdocs\fpdb\al1234.mdb"

' Create a server recordset object
Set rs = Server.CreateObject("ADODB.Recordset")

' Query the states table from the state_info db
sql = "select pid,pname,pImage from products " 

' Execute the sql
rs.Open sql, cn

' Move to the first record
rs.MoveFirst

' Name for the ouput document 
file_being_created= "cat.xml"

' create a file system object
set fso = createobject("scripting.filesystemobject")

' create the text file  - true will overwrite any previous files
' Writes the db output to a .xml file in the same directory 
Set act = fso.CreateTextFile(server.mappath(file_being_created), true)

' All non repetitive xml on top goes here
act.WriteLine("<?xml version=""1.0"" encoding=""ISO-8859-1"" ?>")


act.WriteLine("<slideshow>")

'Loop to output all the query results to the xml document
do while not rs.eof

' counter to give each record a sequential listing
act.WriteLine("<photo image="""& rs("pImage") )
act.WriteLine("bigimage=""" & rs("pImage")&"")
act.WriteLine("lightboxinfo=" & rs("pname"))
act.WriteLine("url=" & rs("pIid"))

' move to the next record
rs.movenext
loop

' All non repetitive xml on bottom goes here
act.WriteLine("</slideshow>")

' close the object (xml)
act.close

' Writes a link to the newly created xml document in the browser
response.write "<a href='cat.xml'>cat</a> (.xml) has been created <br>"
response.write "on  " & now() & "<br>"
%>
thanks
Reply With Quote