xml question

Hi I'm grappling with some xml I want to export data from my database in an xml file but I only get the 1st record I'm doing this: (but I can't see how to loop over each record) <cfquery name="get_data" datasource="#request.main_dsn#" dbtype="ODBC"> SELECT tblWEBSTOCKLEVEL.CustomerID, tblWEBSTOCKLEVEL.ProductCode, tblWEBSTOCKLEVEL.ProductDescription, tblWEBSTOCKLEVEL.StockLevel FROM tblWEBSTOCKLEVEL WHERE (((tblWEBSTOCKLEVEL.CustomerID)=1)); </cfquery> <!--- create a temporary variable to hold XML document ---> <cfset tempxml="<?xml version ""1.0""?> <stock_levels> <CustomerID>#trim("#XMLFormat(get_data.CustomerID)#")#</CustomerID> <stock_details> <cfloop query=""get_data""> <item ProductCode=""#trim("#XMLFormat(get_data.ProductCode)#")#"" ProductDescription=""#trim("#XMLFormat(get_data.ProductDescription)#")#"" StockLevel=""#trim("#XMLFormat(get_data.StockLevel)#")#""/> </cfloop> </stock_details> </stock_levels> "> Can anyone help me please with the looping needed to get each record rather than just the first one. Many thanks Seamus

Seamus I think your problem is that you have coldfusion tags inside the string being set to tempxml and is thus being ignored instead of interpreted. What version of cf are you using? If you are using CF5 you can use cfsavecontent to wrap the output instead of using cfset. In CFMX I think there are some XML functions which should allow you to do this more cleanly. Finally It may sound silly but double check that the query actually returns multiple rows. HTH Kola >> Hi >> I'm grappling with some xml >> I want to export data from my database in an xml file but I only get the ----- Excess quoted text cut - see Original Post for more ----- dbtype="ODBC"> ----- Excess quoted text cut - see Original Post for more ----- ProductDescription=""#trim("#XMLFormat(get_data.ProductDescription)#")#" " ----- Excess quoted text cut - see Original Post for more -----

Thanks Kola, I have checked that the query returns multiple records - and it does. I am using CF5. I can write & read the xml file - but as you suggest the <cfloop..> is getting ignored (I think). This is my xml file - <xmp> <?xml version="1.0" ?> - <stock_levels> <CustomerID>1</CustomerID> - <stock_details> - <cfloop query="get_data"> <item ProductCode="A8-1" ProductDescription="As A Matther of Fact (32)" StockLevel="4992" /> </cfloop> </stock_details> </stock_levels> </xmp> Any help gratefully received (I tried cfsavecontent but can't get the stuff within the cfloop to get interpreted) At 22:27 11/12/02 , you wrote: ----- Excess quoted text cut - see Original Post for more -----

Seamus I noticed a few things which may stop it from working. First Cfsavecontent still requires ## around variables you want displayed and the call to cfsavecontent needs to be wrapped in cfoutput tags Second I don't think you need to escape the double quotes, if you do, Use single quotes instead this should make the code more readable Also you had quotes where they're not needed e.g. when using the trim function if the value is a function call or a variable you do not need the quotes. Finally the line.. <CustomerID>#trim("#XMLFormat(get_data.CustomerID)#")#</CustomerID> will always return one as this is not inside the loop so it will always refer to the first record not sure if that's what you wanted. I have cleaned the code up a little below let me know if this works.. <cfquery name="get_data" datasource="#request.main_dsn#" dbtype="ODBC"> SELECT tblWEBSTOCKLEVEL.CustomerID, tblWEBSTOCKLEVEL.ProductCode, tblWEBSTOCKLEVEL.ProductDescription, tblWEBSTOCKLEVEL.StockLevel FROM tblWEBSTOCKLEVEL WHERE (((tblWEBSTOCKLEVEL.CustomerID)=1)); </cfquery> <!--- create a temporary variable to hold XML document ---> <cfoutput>     <cfsavecontent variable="tempxml" >     <?xml version "1.0"?>     <stock_levels>     <CustomerID>#trim(XMLFormat(get_data.CustomerID))#</CustomerID>         <stock_details>         <cfloop query="get_data">             <item             ProductCode="#trim(XMLFormat(get_data.ProductCode))#" ProductDescription="#trim(XMLFormat(get_data.ProductDescription))#"             StockLevel="#trim(XMLFormat(get_data.StockLevel))#"/>         </cfloop>         </stock_details>     </stock_levels>     </cfsavecontent> </cfoutput> HTH Kola ----- Excess quoted text cut - see Original Post for more ----- is ----- Excess quoted text cut - see Original Post for more ----- (32)" ----- Excess quoted text cut - see Original Post for more ----- get ----- Excess quoted text cut - see Original Post for more ----- string >> >being set to tempxml and is thus being ignored instead of interpreted. ----- Excess quoted text cut - see Original Post for more ----- to ----- Excess quoted text cut - see Original Post for more ----- get ----- Excess quoted text cut - see Original Post for more ----- tblWEBSTOCKLEVEL.ProductCode, ----- Excess quoted text cut - see Original Post for more ----- <CustomerID>#trim("#XMLFormat(get_data.CustomerID)#")#</CustomerID> ----- Excess quoted text cut - see Original Post for more ----- " ----- Excess quoted text cut - see Original Post for more ----- record ----- Excess quoted text cut - see Original Post for more -----