Convert access data to xml

loic2810 · January 4th, 2011, 04:27 AM

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

Old Pedant · January 4th, 2011, 06:35 PM

Ummm...your XML isn't at all valid.

These lines:

Code:

act.WriteLine("<photo image="""& rs("pImage") )
act.WriteLine("bigimage=""" & rs("pImage")&"")
act.WriteLine("lightboxinfo=" & rs("pname"))
act.WriteLine("url=" & rs("pIid"))

would end up writing out something such as

Code:

<photo image="xyz.jpg
bigimage="big_xyz.jpg
lightboxinfo=xyz
url=www.foo.com

for each record. That doesn't BEGIN to look like valid XML.

Maybe you need to show us what your valid XML *should* look like and we can help you create it from ASP??

loic2810 · January 5th, 2011, 03:29 AM

Hi thanks a lot for tour response.

I m beginner to xml so sure my code is quite wrong lol.

So i have to format values from database to xml file like this one :

Xml template:

Code:

<slideshow>
	<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>
	<photo image="images/02.jpg" bigimage = "images/big/02.jpg" target="_blank" lightboxinfo="Description for image 2" url="http://www.flashxml.net"><![CDATA[Download the new<br>Image Scroller]]></photo>
	</slideshow>

And values are coming from access database (see my first post)

So could you help me?

Thanks a lot.

Mckenzie83 · October 3rd, 2012, 09:39 AM

Thank you for the post!
I'm relatively new to xml and this problem seemed rather impossible to solve by myself. But now I got what I did wrong.

________________________
mkv to mp4