Copy link to clipboard
Copied
I am trying to write some xml within a coldfusion page, but unfortunately have no experience with XML.
In it's most basic format if i hard code the values the code below works fine;
getprice.cfm
<?xml version="1.0"?>
<PriceData Ver="2.0" Status="Complete" Design="ARGB1">
<Prices>
<Val Key="1">$ 47.50</Val>
<Val Key="6">$ 42.80</Val>
<Val Key="10">$ 38.50</Val>
<Val Key="21">$ 33.49</Val>
<Val Key="50">$ 30.15</Val>
</Prices>
</PriceData>
However I am trying to query a database to get these prices, something like below. I'm really unclear how this should be formatted but from searching google i believe something like this is possible? I cant get my page to work. Any help with syntax appreciated. Thanks...
getprice.cfm
<cfxml casesensitive="no" variable="PriceData">
<!--------------------Get prices from database---------------------------------->
<cfquery name="pricequery" datasource="#client.dsn#" username="#client.username#" password="#client.password#">
Select * from custom_signs
where material=<cfqueryparam value='#trim(url.material)#' cfsqltype="cf_sql_varchar" maxlength="20">
AND w2w_size=<cfqueryparam value='#trim(url.size)#' cfsqltype="cf_sql_varchar" maxlength="20"> and
type = <cfqueryparam value='#trim(url.Password)#' cfsqltype="cf_sql_varchar" maxlength="14"></cfquery>
<?xml version="1.0"?>
<PriceData Ver="2.0" Status="Complete" Design="ARGB1">
<Prices>
<cfoutput query="pricesquery">
<Val Key="#XMLFormat(pricesquery.LowQTY)#"><cfoutput>#XMLFormat(pricesquery.price)#</cfoutput></Val>
</cfoutput>
</Prices>
</PriceData>
</cfxml>
Copy link to clipboard
Copied
Move the query outside of the <cfxml...> tag, it is creating unnecessary and problematic whitespace.
Unless you are expecting to output multiple values in each <val...> node, you only need the outer <cfoutput...> block.
Copy link to clipboard
Copied
If you're just trying to run a query and then push the results as an XML document try this. If this isn't what you're trying to do please clarify your requirements.
Don't leave any blank spaces between start of XML text and start of file content, some XML parsers will have trouble if you do.
Use CFCONTENT to specify MIME type and clear output prior to the start of your XML file.
You won't need the CFXML tag unless you need to manipulate your XML within ColdFusion code.
There is also an un-needed CFOUTPUT tag in your code sample.
<cfcontent type="text/xml" reset="yes" /><?xml version="1.0"?>
<PriceData Ver="2.0" Status="Complete" Design="ARGB1">
<Prices>
<cfoutput query="pricesquery">
<Val Key="#XMLFormat(pricesquery.LowQTY)#">#XMLFormat(pricesquery.price)#</Val>
</cfoutput>
</Prices>
</PriceData>
Message was edited by: JR "Bob" Dobbs Removed extra CFOUTPUT
Copy link to clipboard
Copied
<!--------------------Get prices from database---------------------------------->
<cfquery name="pricequery" datasource="#client.dsn#" username="#client.username#" password="#client.password#">
Select * from custom_signs
where material=<cfqueryparam value='#trim(url.material)#' cfsqltype="cf_sql_varchar" maxlength="20">
AND w2w_size=<cfqueryparam value='#trim(url.size)#' cfsqltype="cf_sql_varchar" maxlength="20"> and
type = <cfqueryparam value='#trim(url.Password)#' cfsqltype="cf_sql_varchar" maxlength="14"></cfquery>
<!--- The cfxml tag will automatically add the processing instruction line <?xml etc etc. --->
<cfxml casesensitive="no" variable="PriceData">
<PriceData Ver="2.0" Status="Complete" Design="ARGB1">
<Prices>
<cfoutput query="pricesquery">
<Val Key="#XMLFormat(pricesquery.LowQTY)#">#XMLFormat(pricesquery.price)#</Val>
</cfoutput>
</Prices>
</PriceData>
</cfxml>