asp / sql code in xml file possible? or other options to retrieve recordset in xml

[omar999]

hi guys

im fairly new to xml so bear with me if this is a newbie question. I have an xml file

<?xml version="1.0" encoding="utf-8" ?>
<promos>
  <flight type="OW">
  <url></url>
    <departure>Gatwick</departure>
    <destination>TORONTO</destination>
    <departuredate year="2010" month="5" day="27, 28"/>
    <price>139</price>
    <currencycode>£</currencycode>
  </flight>
  <flight type="OW">
  <url></url>
    <departure>Gatwick</departure>
    <destination>Vancouver</destination>
    <departuredate year="2010" month="5" day="7, 8, 9"/>
    <price>149</price>
    <currencycode>£</currencycode>
  </flight>
</promos>
 

and I'd like to retrieve the price from our sql database - but when I try to insert asp code in to this xml file it errors.. is it not possible to insert asp code within an xml file?

can you insert xml code in an asp file as an alternative? or is there a way to insert an asp file in to an xml file as an include??

please advise
thanks

[Dormilich]

you can insert ASP code, but you have to do it in a processing instruction node or CDATA node (ref.)

[omar999]

thanks for the quick reply - so in my case rather than

<price>139</price>
 

instead wrap it within cdata node?

<price><![CDATA[ ASP CODE HERE ]]></price>
 

?

[Dormilich]

that depends on how you want to handle the code.

[omar999]

im not with you - can you please elaborate...

[Dormilich]

what is the purpose of the ASP code?

[omar999]

to connect to sql db and retrieve data - i.e in this case an integer to go inbetween the price xml tag..

regards
Omar.

[Dormilich]

so you want to execute the ASP before loading/reading/whatever the XML?

then write an ASP page, that returns the ready made XML file.

you’d need the processing instruction, if you’d execute the ASP afterwards.

example in PHP, since I don’t know ASP

<?php
// file: price.php
    header("Content-Type: text/xml");
    echo '<?xml version="1.0" ?>';
    include "functions.inc";
    $price = getPriceFromDB();
?>
<price><?php echo $price; ?></price>

[omar999]

I don't want to create another asp page as I'm trying to maintain as few pages as possible.

Ideally I just want to use an xml page and retrieve a value [in my case a price] using asp from a sql database.

can anyone else advise of any solutions please?

thanks in advance

[Dormilich]

I don't want to create another asp page as I'm trying to maintain as few pages as possible.

and how do you tell the ASP interpreter to execute the code?

Ideally I just want to use an xml page and retrieve a value [in my case a price] using asp from a sql database.

XML doesn’t do that.

[omar999]

okay - alternatively if I created another asp file to retrieve a value from sql db and then pull in this asp file within the xml as an include? so something like this

<price><![CDATA[<!--#include virtual="price.asp" -->]]></price>
 

would that work?

[Dormilich]

a) you don’t need CDATA tags
b) if you tell the ASP interpreter to parse the XML file
… maybe, you have to ask someone knowledgeable in ASP for the exact procedure.

[omar999]

correct me if im wrong but are you trying to say to use an asp file instead of an xml file and then output/convert the asp to xml?

I have to use an xml file as we are sending this xml file to a 3rd party company so they are expecting an xml file.

I will ask in the asp section for some advise - if you have any other ideas please do let me know.

be well

Omar.

[Dormilich]

I have to use an xml file as we are sending this xml file to a 3rd party company so they are expecting an xml file.

that depends on how you deliver. if they just access the file over the net (i.e. only reading) that doesn’t matter, because you give them an XML file (MIME type overrides file extension).
if you send the file (e.g. via email), you have to dump the result into a file (which may bear any name or file extension you like)

[omar999]

we do not send them the file via email thats for sure - they appear to read it as they have ftp access to our files etc but I will find out for 100% sure on details.

im not sure what you mean by 'dump the result into a file'...

[Dormilich]

im not sure what you mean by 'dump the result into a file'...

I mean: "save as …"

so it appears that you have to save the result XML code to a file, since the 3rd party does not/cannot make use of the ASP interpreter. (you have to write a script, that generates the XML file)

[JamieHowarth0]

OK, so you need to generate an XML file from database data, which is then accessed via FTP via a third-party.

You'd generate the XML file as a string in ASP, then save it as an XML file to disk.
In pseudo-code:

<%
'Connect to DB
String myXml = "<price>{0}</price>"
'dr is your recordset with the data pulled from your DB
String myXml = Replace(myXml, "{0}", dr("Price"))
Dim fso = CreateObject("Scripting.FileSystemObject")
Set fs = fso.CreateTextFile("myXmlFile.xml") 
fs.WriteLine(myXml) 
fs.Close
Set fs = Nothing
%>
 

Hope this helps.

codegecko

[Monomachus]

@omar999
Excuse me, but this is not a great idea. Why don't you try to work with XML from ASP and not inverse? I mean usually you'd have some data you need to insert into XML but this isn't too complex. I mean putting an ASP into XML just to take one value from database... is too strange and isn't really professional. I mean look at alternatives really. Perhaps the most correct thing to have a method that will do that for you and you just insert this value into xml.

[omar999]

codegecko - thanks for your reply. I've read your post several times and although I may not fully understand but I think this may be the way forward to generate the xml and then save the file on our server.

Monomachus - im trying to eradicate the need to manually enter in any data in to the xml file while it can just retrieve this data directly from our db. hence i want to automate this process. i need to retrieve aprox 4 values from sql db in to this xml file - and there's about 8 xml files so in the long run it will be worthwhile.