ASP to XML (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

RobM_01 -> ASP to XML (7/12/2005 14:50:14)

HI,

just a quick question,

is it possible to use an asp (classic) page to query an access database and have the results sent back as an XML file?

If so, please could you point me in the direction of either some sample code to look at or a tutorial.

many thanks

Rob.



rdouglass -> RE: ASP to XML (7/12/2005 15:03:21)

I found this article to be short-n-sweet and to the point. I've referred to it before. However, I've been finding that large data sets seem to be slow when I use this method.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexxml/html/xml030998.asp

Of course I'm not an XML expert but have been trying to apply it in my most recent works.

Hope it helps.



RobM_01 -> RE: ASP to XML (7/12/2005 16:39:34)

Thanks for the pointer, much appreciated.

I thought I'd create a first pass asp page to produce XML results.
The code I used is based on the example shown on the link above.

The ouptut complains about white space and yet in the example it shows the output as having spaces.

If I remove the Description field where the spaces exist I get XML, which is great, but I need the descriptions also.

any ideas what the problem might be?

Cheers Rob. 

<%@LANGUAGE = VBScript%>
<?xml version="1.0"?>
   
   <Photos>
<%
   	Dim oConn, oRS, strSQL

   	Set oConn = Server.CreateObject("ADODB.Connection")
   	Set oRS = Server.CreateObject("ADODB.Recordset")

   	oConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("gen_dib_data.mdb")
   
   	strSQL="SELECT * FROM Photo"

	oRS.Open strSQL, oConn, 2, 3
	Do While Not oRS.EOF
   %>


<Photo>
      <Description></Description>
      <Thumbnail></Thumbnail>
      <ID></ID>

</Photo>


   <Photo>
      <Description><%=oRS("PDesc")%></Description>
      <Thumbnail><%=oRS("Filename")%></Thumbnail>
      <ID><%=oRS("PhotoID")%></ID>
   </Photo>


   	<% oRS.MoveNext
   	Loop
   	%> <% oRS.Close
    Set oRS = Nothing
    oConn.Close
    Set oConn = Nothing
   	%>

   </Photos>



The XML page cannot be displayed 
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later. 


--------------------------------------------------------------------------------

Whitespace is not allowed at this location. Error processing resource 'http://thebes/ImageRealm_new_1/Test_Folder%202/dib1....

      <Description>Father & baby</Description>
---------------------------^



bobby -> RE: ASP to XML (7/12/2005 16:57:44)

Does it help if you change the "&" to "and"

?

(I don't know much about XML)




RobM_01 -> RE: ASP to XML (7/12/2005 17:19:44)

yes that worked , I removed & and the ' chars both of which XML did not like where I had a lot of instances of , so now I get a complete list just fine.

However, what if I need to keep them in place can this be done?




RobM_01 -> RE: ASP to XML (7/12/2005 17:22:18)

This the complete code corrected from the previous message.

<%@LANGUAGE = VBScript%>
<?xml version="1.0" encoding="UTF-8"?>

<Photos>
<%
Dim oConn, oRS, strSQL

Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.Recordset")

oConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("gen_dib_data.mdb")

strSQL="SELECT * FROM Photo"

oRS.Open strSQL, oConn, 2, 3
Do While Not oRS.EOF
%>

<Photo>
<Description><%=oRS("PDesc")%></Description>
<Thumbnail><%=oRS("Filename")%></Thumbnail>
<ID><%=oRS("PhotoID")%></ID>
</Photo>


<% oRS.MoveNext
Loop
%> <% oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing
%>

</Photos>



yogaboy -> RE: ASP to XML (7/14/2005 11:33:26)

If you want to keep the & and ' then use unicode in their place and they won't annoy your xml parser! [:)]

' for '
& for &

my tip would be to write a function that replaces common characters into unicode so that you can include where needed without having to write it over and over...



RobM_01 -> RE: ASP to XML (7/14/2005 11:48:27)

Ok thanks Ian I'll try that.

just as an aside, the code above produces XML sure enough, but does not create an actual XML file. I tried to display some text from the asp file after it has run and nothing appears in Flash but if I copy the XML output in notepad and create an XML file Flash reads the records. (after I change the file name to look for in the ActionScript)

Cheers Rob.






yogaboy -> RE: ASP to XML (7/14/2005 11:58:46)

I use this vbscript function I nabbed from somewhere (was it Spooky??) 

<%
function WriteToFile(FileName, Contents, Append)
on error resume next

if Append = true then
   iMode = 8
else 
   iMode = 2
end if
set oFs = server.createobject("Scripting.FileSystemObject")
set oTextFile = oFs.OpenTextFile(FileName, iMode, True)
oTextFile.Write Contents
oTextFile.Close
set oTextFile = nothing
set oFS = nothing

end function

%>


Anyway, this will help write stuff to a notepad automatically for you, below I've shown a bit of the code in use writing results from an array, which are then appended line by line to a notepad. 

	
myFileN = "myxmlfile.asp"
	myFoldN = "xmlfolder\"
						
For rowN = 0 to numrows
			
	myCode = alldata(0,rowN)
	myProgN = alldata(1,rowN)  
	
	
	myContent = "<option value='" & myCode & "'>" & myProgN & "</option>"

	WriteToFile "E:\public\web\" & myFoldN & myFileN, myContent, True

Next


Use True to append, False to overwrite. Hope this helps (anyone![:)]).



RobM_01 -> RE: ASP to XML (7/14/2005 12:17:46)

woah there, this is a bit over my head, where do I put this code - in my file somewhere?




yogaboy -> RE: ASP to XML (7/14/2005 12:37:24)

Put the first bit in a text file and save it on it's own. Then you can <!--INCLUDE FILE="pathtofile\file.asp"--> on any page you need it.

The second bit goes in the page that gets the data from the database. I tend to use getrows to put all the data from the database into an array rather than page through a recordset. Then I use a For/Next loop to put the data into the variable myContent.

I put the foldername for the new file being written to, into myFoldN. Put the filename into myFileN.

Then call WriteToFile and it will write it.

A concrete example. You have put all your data in an array call alldata (I won't do that bit here). You want a txt file called myXML.txt created in the images folder on your website. The physical path of your website is E:\public\web\

The code could be 
 <!--INCLUDE FILE="WriteToFile.asp"-->

myFileN = "myXML.txt"
        myFoldN = "\images"
                                                
For i = 0 to Ubound(alldata)
        
'put the data into a variable                
        myContent = "<start>" & alldata(i) & "</end>"

'call the write to file function
        WriteToFile "E:\public\web\" & myFoldN & myFileN, myContent, True

Next


This would give me a text file called myXML.txt and it would have my data with <start> and </end> tags around it. There's obviously a lot more you can do with this, but it's not as difficult as it looks - or as I've made it sound![:D]

Basically, if I can do it then anyone can do it!



RobM_01 -> RE: ASP to XML (7/14/2005 13:13:07)

I added the code as in below, but it seems to be failing at line 31

For i = 0 to Ubound(alldata) 


<%@LANGUAGE = VBScript%>
<?xml version="1.0" encoding="UTF-8"?>
   
   <PORTFOLIO>
<%
   	
   	Dim oConn, oRS, strSQL

   	Set oConn = Server.CreateObject("ADODB.Connection")
   	Set oRS = Server.CreateObject("ADODB.Recordset")

   	oConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("dib/gen_dib_data/gen_dib_data.mdb")
   
   	strSQL="SELECT DISTINCT Photo.PDesc, Photographer.Fullname AS Expr1, Photo.PhotoID, Photo.[Date], Photo.[Size], Photo.Filename, Photographer.photogID, Photographer.GalleryLink FROM Photographer AS Photographer_1, (tCat INNER JOIN tSCat ON tCat.CatID = tSCat.CatID) INNER JOIN ((Photo INNER JOIN Photographer ON Photo.PhotogID = Photographer.photogID) INNER JOIN (tSSCat INNER JOIN SSC2Photo ON tSSCat.SSCatID = SSC2Photo.SSCatID) ON Photo.PhotoID = SSC2Photo.PhotoID) ON tSCat.SCatID = tSSCat.SCatID WHERE (((tCat.CatID)=35));"

	oRS.Open strSQL, oConn, 2, 3
	Do While Not oRS.EOF
   %>

   <IMAGE>
      <IMAGEDESC><%=oRS("PDesc")%></IMAGEDESC>
      <PHOTOGRAPHER><%=oRS("Expr1")%></PHOTOGRAPHER>
      <IMAGEFILE><%=oRS("Filename")%></IMAGEFILE>
      <IMAGEID><%=oRS("PhotoID")%></IMAGEID>
   </IMAGE>
 <!--INCLUDE FILE="file.asp"-->
<% 
		myFileN = "myXML.txt"
        myFoldN = "\xml"
                                                
For i = 0 to Ubound(alldata)
        
'put the data into a variable                
        myContent = "<start>" & alldata(i) & "</end>"

'call the write to file function
        WriteToFile myFoldN & myFileN, myContent, True

Next

   	oRS.MoveNext
   	Loop
   	%> 
   	
   	<% oRS.Close
    Set oRS = Nothing
    oConn.Close
    Set oConn = Nothing
   	%>

   </PORTFOLIO>


Cheers Rob



yogaboy -> RE: ASP to XML (7/14/2005 13:20:27)

I use this method to get data

'SETTING RECORDSET OBJECT AND OPENING RECORDSET USING SQL STATEMENT
Set myRS = Server.CreateObject("ADODB.Recordset")
myRS.Open mySQL1, myConn

alldata=myRS.getrows

myRS.Close
Set myRS = Nothing
myConn.Close
Set myConn = Nothing

numcols=ubound(alldata,1)
numrows=ubound(alldata,2)

have a quick read of http://www.4guysfromrolla.com/ASPScripts/PrintFAQ.asp?FAQID=161 this for more info on GetRows



RobM_01 -> RE: ASP to XML (7/14/2005 16:55:59)

The message I get now is this, which is this line

myContent = "<start>" & alldata(i) & "</end>" 

Subscript out of range: 'alldata'
/ImageRealm_new_1/Test_Folder 2/dib3.asp, line 30



<%@LANGUAGE = VBScript%>

<%
   	
   	Dim oConn, oRS, strSQL

   	Set oConn = Server.CreateObject("ADODB.Connection")
   	Set oRS = Server.CreateObject("ADODB.Recordset")

   	oConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("dib/gen_dib_data/gen_dib_data.mdb")
   
   	strSQL="SELECT * FROM Photo"
	oRS.Open strSQL, oConn, 2, 3 
	alldata=oRS.getrows()

	oRS.Close
    Set oRS = Nothing
    oConn.Close
    Set oConn = Nothing
    
	numcols=ubound(alldata,1) 
	numrows=ubound(alldata,2)

%>
<!--INCLUDE FILE="file.asp"-->
<% 
		myFileN = "myXML.txt"
        myFoldN = "\xml"
                                                
        myContent = "<start>" & alldata(i) & "</end>"

        WriteToFile myFoldN & myFileN, myContent, True
%>



yogaboy -> RE: ASP to XML (7/14/2005 18:36:46)

You're missing For/Next loops to iterate through the array. Put this after myFoldN = "\xml". I haven't tested this!

'move through the rows
For i = 0 to numrows
'add the start tag
myContent = "<photo>"
'and to move along the columns, a nested loop
For j = 0 to numcols

'put the data into a variable for neatness
thisfield = alldata(j,i)

'decide on the tag and add it to myContent
Select Case j
Case 0
myContent = myContent & "<description>" & thisfield & "</description>"
Case 1
myContent = myContent & "<thumbnail>" & thisfield & "</thumbnail>"
Case 2
myContent = myContent & "<id>" & thisfield & "</id>"
End Select
Next
'add the closing tag
myContent = myContent & "</photo>"
WriteToFile myFoldN & myFileN, myContent, True
Next



RobM_01 -> RE: ASP to XML (7/14/2005 19:01:42)

Its now coming back with this message

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'WriteToFile'
/ImageRealm_new_1/Test_Folder 2/dib3.asp, line 52

WriteToFile myFoldN & myFileN, myContent, True 

<%@LANGUAGE = VBScript%>

<%
   	
   	Dim oConn, oRS, strSQL

   	Set oConn = Server.CreateObject("ADODB.Connection")
   	Set oRS = Server.CreateObject("ADODB.Recordset")

   	oConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("dib/gen_dib_data/gen_dib_data.mdb")
   
   	strSQL="SELECT * FROM Photo"
	oRS.Open strSQL, oConn, 2, 3 
	alldata=oRS.getrows()

	oRS.Close
    Set oRS = Nothing
    oConn.Close
    Set oConn = Nothing
    
	numcols=ubound(alldata,1) 
	numrows=ubound(alldata,2)

%>
<!--INCLUDE FILE="file.asp"-->
<% 
		myFileN = "myXML.xml"
        myFoldN = "xml\"
        
        'move through the rows 
		For i = 0 to numrows 
		'add the start tag 
		myContent = "<photo>" 
		'and to move along the columns, a nested loop 
		For j = 0 to numcols 
		
		'put the data into a variable for neatness 
		thisfield = alldata(j,i) 
		
		'decide on the tag and add it to myContent 
		Select Case j 
		Case 0 
		myContent = myContent & "<description>" & PDesc & "</description>" 
		Case 1 
		myContent = myContent & "<thumbnail>" & Filename & "</thumbnail>" 
		Case 2 
		myContent = myContent & "<id>" & PhotoID & "</id>" 
		End Select 
		Next 
		'add the closing tag 
		myContent = myContent & "</photo>" 
		WriteToFile myFoldN & myFileN, myContent, True 
		Next 

                                                
%>



yogaboy -> RE: ASP to XML (7/15/2005 6:18:23)

Did you put the WriteToFile function into the file called file.asp? check it has script tags around it in the file.

Also, there's a hash missing at the front of the include statement, should be

<!--#include file="file.asp"-->

that's my fault, isn't it![&:] Got to watch out for typos![:D]



RobM_01 -> RE: ASP to XML (7/15/2005 9:23:41)

Hi Iain, sorry about the hash thing should of spotted that, it was late last night when I looked at it, you know how it is.

Anyway, the code runs without a failure this time, however, it does not produce the xml either. 

WriteToFile "C:\Inetpub\wwwroot\ImageRealm_new_1\Test_Folder 2\" & myFoldN & myFileN, myContent, False


I added the full directory path as shown above and did a response.write to the path which it returns

C:\Inetpub\wwwroot\new_1\Test_Folder 2\xml\myXML.txt

so that looks ok

I also did a response.write on myContent that showed nothing, is this correct?

Cheers Rob.



yogaboy -> RE: ASP to XML (7/15/2005 11:13:46)

I had a quick go with the code and it didn't work, so I set up my own test file - here's the code 

<%@LANGUAGE = VBScript%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 TRANSITIONAL//EN">
<html>

	<head>
		<title>Test</title>
	</head>
	<body>

<!--#INCLUDE FILE="adovbs.inc"-->
<%
Dim myConnSQL
Set myConnSQL = Server.CreateObject("ADODB.Connection")
myConnSQL.Open "LocalServer"
%>

<%
        
        mySQL = "SELECT TOP 5 OrgId, OrgName, OrgTypeId FROM Org"
        'SETTING RECORDSET OBJECT AND OPENING RECORDSET USING SQL STATEMENT
		Set myRS = Server.CreateObject("ADODB.Recordset")
		myRS.Open mySQL, myConnSQL
		 
        alldata=myRS.getrows()

        myRS.Close
		Set myRS = Nothing
		myConnSQL.Close
		Set myConnSQL = Nothing
    
        numcols=ubound(alldata,1) 
        numrows=ubound(alldata,2)
        Response.Write "numcols: " & numcols & "<br />"
		Response.Write "numrows: " & numrows & "<br />"
%>
<%
function WriteToFile(FileName, Contents, Append)
on error resume next

if Append = true then
   iMode = 8
else 
   iMode = 2
end if
set oFs = server.createobject("Scripting.FileSystemObject")
set oTextFile = oFs.OpenTextFile(FileName, iMode, True)
oTextFile.Write Contents
oTextFile.Close
set oTextFile = nothing
set oFS = nothing

end function
%>
<% 
        myFileN = "myXML.txt"
        myFoldN = "xml\"
        
        'move through the rows 
        For i = 0 to numrows 
           	'add the start tag 
			 myContent = "<photo>" 
				  	'and to move along the columns, a nested loop 
                	For j = 0 to numcols 
                
               			 'put the data into a variable for neatness 
                		thisfield = alldata(j,i) 
                
               			 'decide on the tag and add it to myContent 
               			 Select Case j 
                			Case 0 
                				myContent = myContent & "<description>" & thisfield & "</description>" 
                			Case 1 
								myContent = myContent & "<thumbnail" & thisfield & "</thumbnail>" 
							Case 2 
								myContent = myContent & "<id>" & thisfield & "</id>" 
							End Select 
					Next 
                'add the closing tag 
                myContent = myContent & "</photo>" 
                Response.Write myContent & "</br>"
                Response.Write myFoldN & myFileN & "<br />"
                WriteToFile "C:\docs\web\" & myFoldN & myFileN, myContent, True 
          Next 

                                                
%>
	</body>
</html>


and this is the file it wrote 
<photo><description>1</description><thumbnail>NORTH TEES</thumbnail><id>3</id></photo><photo><description>2</description><thumbnail>SELBY AND YORK</thumbnail><id>3</id></photo><photo><description>3</description><thumbnail>EAST YORKSHIRE</thumbnail><id>3</id></photo><photo><description>4</description><thumbnail>YORKSHIRE WOLDS AND COAST</thumbnail><id>3</id></photo><photo><description>5</description><thumbnail>EASTERN HULL</thumbnail><id>3</id></photo>


so hopefully if you change the bits of the file to yours then it might just work... (fingers crossed). [8|]

You'll need to add a <root></root> to the file to make it valid xml (and a dtd or xsl etc etc).

Let me know how it goes...



RobM_01 -> RE: ASP to XML (7/15/2005 13:59:46)

Hi Iain,

during the the course of today I found this code which seems to work and it uses an xsl style sheet 

<%@LANGUAGE = VBScript%>

<%
   	
Response.Expires = -1
Response.Buffer = True
Dim conn, rs, xml, xsl
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("gen_dib_data.mdb") & ";User Id=admin;Password=;"
conn.Open
Set rs = Server.CreateObject("ADODB.Recordset")
Set rs.ActiveConnection = conn
rs.Open "SELECT * FROM Photo"
If Not rs.EOF Then
    Set xml = Server.CreateObject("MSXML2.DOMDocument")
    Set xsl = Server.CreateObject("MSXML2.DOMDocument")
    xml.async = False
    xsl.async = False
    rs.Save xml, 1 'adPersistXML
    xsl.load Server.MapPath("ADOGeneric.xsl")
    Response.Write xml.transformNode(xsl)
End If
Set xsl = Nothing
Set xml = Nothing
Set rs = Nothing
conn.Close
Set conn = Nothing 
                                                
%>



<?xml version='1.0'?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:z="#RowsetSchema">
    <s:Schema id="RowsetSchema"/>
<xsl:output method="xml" omit-xml-declaration="yes" />
<xsl:template match="/">
    <xsl:apply-templates select="//z:row"/>
</xsl:template>

<xsl:template match="z:row">
    <xsl:text disable-output-escaping="no"><row></xsl:text>
        <xsl:for-each select="@*">
            <xsl:text disable-output-escaping="no"><</xsl:text>
            <xsl:value-of select="name()"/>
            <xsl:text disable-output-escaping="no">></xsl:text>
            <xsl:value-of select="."/>
            <xsl:text disable-output-escaping="no"></</xsl:text>
            <xsl:value-of select="name()"/>
            <xsl:text disable-output-escaping="no">></xsl:text>
        </xsl:for-each>
    <xsl:text disable-output-escaping="no"></row></xsl:text>
</xsl:template>
</xsl:stylesheet>


this is the output 

<row><PDesc>Hot Rod</PDesc><Filename>ab_0001.jpg</Filename><PhotoID>223</PhotoID><PhotogID>5</PhotogID><GalleryPicture>1</GalleryPicture></row><row><PDesc>Brass Headlamps</PDesc><Filename>ab_0002.jpg</Filename><PhotoID>224</PhotoID><PhotogID>5</PhotogID><GalleryPicture>2</GalleryPicture></row><row><PDesc>St. Martins in the field</PDesc><Filename>ab_0003.jpg</Filename><PhotoID>225</PhotoID><PhotogID>5</PhotogID><GalleryPicture>3</GalleryPicture></row


although it does not produce an xml file, I was able to grab the data in Flash and display it in a text box, which is great.

So I think this is another version, let me know what you think.

I got the source from this link.

http://builder.com.com/5100-31-5078101.html

Cheers Rob.



yogaboy -> RE: ASP to XML (7/17/2005 17:22:22)

This stuff looks good. I haven't used the MSXML DOM but I will probably give it a go, thanks for the tip. I'm sure it will be easier than my way, but I'm no programming Einstein so that's not a surprise![:D]

Quick tip in return, you'll need to change the element names to lower case (PhotoID to photoid) to make it valid xml - everything needs to be lower case.



RobM_01 -> RE: ASP to XML (7/18/2005 4:15:07)

ok thanks Iain, I'll do that, it'll mean changing the database fieldnames as this code grabs the names from there to create the tag names.

My next task will be to format the output into columns in flash, currently its just one long string. I'll post the code as when I get it sorted. It may be of use to someone as it will be an end to end solution from asp to Flash

ASP >> Query >> Access DB >> XML >> FLASH

Cheers Rob.



yogaboy -> RE: ASP to XML (7/18/2005 16:38:11)

share the wealth! [8D]

check this out for the lowercase - w3schools vbscript functions



RobM_01 -> RE: ASP to XML (7/18/2005 18:59:28)

thanks Iain

that did the trick,


I replaced

Response.Write xml.transformNode(xsl)

with

Response.Write(lcase(xml.transformNode(xsl)))


Cheers Rob.



RobM_01 -> RE: ASP to XML (7/18/2005 19:14:11)

do you think there would be any advantage to run an xsl script to transform the elements to attributes? 

<collection>
                <image>
	            <pdesc>hot rod</pdesc>
	            <filename>ab_0001.jpg</filename>
	            <photoid>223</photoid>
	            <photogid>5</photogid>
                </image>
                <image>
	            <pdesc>brass headlamps</pdesc>
	            <filename>ab_0002.jpg</filename>
	            <photoid>224</photoid>
	            <photogid>5</photogid>
                </image>
</collection>




<collection>
   <image pdesc="hot rod" filename="ab_0001.jpg" photoid="223" photogid="5"/>
   <image pdesc="brass headlamps" filename="ab_0002.jpg" photoid="224" photogid="5"/>
</collection>


I thought I read somewhere that flash could read it quicker.



yogaboy -> RE: ASP to XML (7/19/2005 14:42:40)

No idea![:)]

I've never used Flash personally, so can't tell you a bean about it... on a pure xml thing, it just depends, but rule of thumb is to have more elements and less attributes.



kt -> RE: ASP to XML (7/30/2005 17:04:13)

I've been reading this thread with interest but got a bit lost about half way down...

I'm trying to produce an xml file and have got this far: 

<%@LANGUAGE = VBScript%> 
<?xml version="1.0" encoding="UTF-8"?> 

<Jobs>

<%
Dim oConn, oRS, strSQL

   	Set oConn = Server.CreateObject("ADODB.Connection")
   	Set oRS = Server.CreateObject("ADODB.Recordset")

   	oConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("fpdb/jobs.mdb")
   
   	strSQL="SELECT * FROM jobs"

	oRS.Open strSQL, oConn, 2, 3
	Do While Not oRS.EOF

   %>

<Job>
      <company></company>
      <country></country>
      <city></city>

</Job>


<Job>
      <company><%=oRS("company")%></company>
      <country><%=oRS("country")%></country>
      <city><%=oRS("city")%></city>

</Job>

   	<% oRS.MoveNext
   	Loop
   	%> <% oRS.Close
    Set oRS = Nothing
    oConn.Close
    Set oConn = Nothing
   	%>

   </Jobs>


which produces an asp page with good xml.

What I need though is the file to be .xml, not .asp. I'm guessing that the second half of this thread is talking about that, but it gets a little over my head....

How can I make this an .xml page? (Step by step please...!)

thanks, kt



kt -> RE: ASP to XML (7/31/2005 9:44:49)

Okay, trying all sorts of things here, and learning (a little) as I go along...

I came across this at http://www.asp101.com/samples/db_xml.asp: 

<%

Dim objFSO
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(Server.MapPath("db_xml.xml")) Then
	objFSO.DeleteFile Server.MapPath("db_xml.xml")
End IF
Set objFSO = Nothing

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("fpdb/jobs.mdb")

Set oRS = Server.CreateObject("ADODB.Recordset")

Set oRS = oConn.Execute("SELECT country, city, company FROM jobs")

Response.Write "<p>Saving data as XML...</p>" & vbCrLf

oRS.Save Server.MapPath ("db_xml.xml"), adPersistXML

oRS.Close
Set oRS = Nothing
oComm.Close
Set oComm = Nothing

Response.Write "<p>XML file written...</p>" & vbCrLf

Response.Write "<p>Click <a href=""db_xml.xml"">here</a> to view the file.</p>" & vbCrLf
%>


At this line:

oRS.Save Server.MapPath ("db_xml.xml"), adPersistXML

I get this error:

Provider error '80030103'
Can't save.

Anyone have any idea how to get around that?

Thanks, kt (learning slowly...)








yogaboy -> RE: ASP to XML (7/31/2005 17:14:03)

Hi kt

To be honest, I don't know about either the method you've added or Rob's method, but if you use the method I put up then you only need to change the variable FileN

FileN = "myfile.xml"

Hope that helps, but I think the other 2 methods are superior (which is why I don't understand them!) [:D]



kt -> RE: ASP to XML (7/31/2005 18:38:30)

Hi Iain

Thanks for the reply.

I think I've figured it out, but in a totally different way - with the help of http://www.codeave.com/asp/code.asp?u_log=149.

Here's what I'm using now: 

<%
Set cn = Server.CreateObject("ADODB.Connection")

cn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("../fpdb/jobs.mdb")

Set rs = Server.CreateObject("ADODB.Recordset")

Set rs = cn.Execute("SELECT ID, country, city, title, dateposted FROM jobs ORDER BY dateposted DESC")

rs.MoveFirst

file_being_created= "jobs12.xml"

set fso = createobject("scripting.filesystemobject")

Set act = fso.CreateTextFile(server.mappath(file_being_created), true)

act.WriteLine("<?xml version=""1.0""?>")
act.WriteLine("<rss version=""2.0"">")
act.WriteLine("<channel>")
act.WriteLine("<title>Latest jobs from eslbase.com</title>")
act.WriteLine("<description>Latest jobs</description>")
act.WriteLine("<link>http://www.eslbase.com/tefl_jobs.asp</link>")

do while not rs.eof

counter=counter+1

act.WriteLine("<item>")
act.WriteLine("<title>" & rs("country") & "</title>" )
act.WriteLine("<description>" & rs("title") & "</description>")
act.WriteLine("<link>http://www.eslbase.com/tefl_job_details?ID=</link>")
act.WriteLine("</item>")

rs.movenext
loop

act.WriteLine("</channel>")
act.WriteLine("</rss>")

act.close
response.write "<a href='jobs12.xml'>Jobs</a> (.xml) has been created <br>"
response.write "on " & now() & "<br>"
%>


The output is this RSS page: 

<?xml version="1.0"?>
<rss version="2.0">
<channel>
<title>Latest jobs from eslbase.com</title>
<description>Latest jobs</description>
<link>http://www.eslbase.com/tefl_jobs.asp</link>
<item>
<title>Oman</title>
<description>REQUIRED ENGLISH TEACHERS TO OMAN</description>
<link>http://www.eslbase.com/tefl_job_details?ID=</link>
</item>
<item>
<title>China</title>
<description>Working In a Famous Training Center 1000USD/MONTH</description>
<link>http://www.eslbase.com/tefl_job_details?ID=</link>
</item>
<item>
<title>China</title>
<description>International Primary School Teacher</description>
<link>http://www.eslbase.com/tefl_job_details?ID=</link>
</item>

.....Items go on until the end here

</channel>
</rss>


Basic RSS but works well for what I need...

A couple of questions you (or anyone else!!) may be able to help me out with though...

1 How can I change this line on the asp page...

Set rs = cn.Execute("SELECT ID, country, city, title, dateposted FROM jobs ORDER BY dateposted DESC")

...to retun only, say, 10 results? (i know there's a "TOP" command, but can't seem to make it work...)


2 On this line on the asp page...

act.WriteLine("<link>http://www.eslbase.com/tefl_job_details?ID=</link>")

...I want it to say .../tefl_job_details?ID=rs("ID") (i.e. the ID called in the SQL)

I've played around with it but can't seem to get the syntax right.


Thanks again for the help

kt

EDIT: OK, both problems solved - it was late at night, wasn't thinking straight!!

1 Set rs = cn.Execute("SELECT TOP 10 ID, country, city, title, dateposted FROM jobs ORDER BY dateposted DESC")

...was sure I'd tried this before.


2 act.WriteLine("<link>" & "http://www.eslbase.com/tefl_job_details.asp?ID=" & rs("ID") & "</link>")



Page: [1] 2   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.1879883