CFLib.org – sqlXMLToCFXML

sqlXMLToCFXML(doc, qry)

Last updated April 9, 2007

author

Version: 1 | Requires: ColdFusion MX | Library: DataManipulationLib

Rated 0 time(s). Average Rating: 0

Description:
Takes a query and a name for a root element and creates a XML string (not xmlObject) that can then be used within ColdFusion. This is conversion only for MSSQL generated XML.

Return Values:
Returns a string.

Example:

<cfquery datasource="mydatasource" name="xmlQuery">
SELECT * FROM myTable FOR XML AUTO, ELEMENTS
</cfquery>

<cfset retVar = sqlXMLToCFXML(doc="myRoot",qry=xmlQuery)/>

Parameters:

Name	Description	Required
doc	Name for root level element.	Yes
qry	Query to convert.	Yes

Full UDF Source:

	
	<!---
 Converts a query of XML generated by MSSQL to readable XML string.
 
 @param doc      Name for root level element. (Required)
 @param qry      Query to convert. (Required)
 @return Returns a string. 
 @author Russel Brown (russel.brown@universalmind.com) 
 @version 1, April 9, 2007 
--->
<cffunction name="sqlXMLToCFXML" access="public" output="false" returntype="Any" hint="This function will take a multiple row query result and turn it into a CF XML var.">
      <cfargument name="doc" type="String" required="false" default="xml" />
      <cfargument name="qry" type="Query" required="true" />

      <cfset var x = "" />
      <cfset var y = "" />
      <cfset var retXML = "" />

      <cfset x = listFirst(arguments.qry.columnList)>
      <cfloop index="y" from="1" to="#arguments.qry.recordCount#">
         <cfset retXML = retXML & arguments.qry[x][y]>
      </cfloop>

      <cfset retXML = "<#arguments.doc#>" & retXML & "</#arguments.doc#>">

      <cfreturn retXML>
</cffunction>