Query to XML and XML to query in coldfusion
<!--- Query To XML, apart from xml packet creation, this will create a new myDoc.xml file :start--->
<cfquery name="empdetails" datasource="empdetails">
select empid,empname,empage,country,state,empsalary from empdetails
</cfquery>
<cfxml variable="xmlPacket">
<employees>
<cfoutput query="empdetails">
<employee id="#empid#">
<address>
<empname>#empname#</empname>
<empcountry>#country#</empcountry>
<empstate>#state#</empstate>
</address>
<other>
<empage>#empage#</empage>
<empsalary>#empsalary#</empsalary>
</other>
</employee>
</cfoutput>
</employees>
</cfxml>
<cfdump var="#xmlPacket#">
<cffile action="write" file="C:\ColdFusion9\wwwroot\myDoc.xml" output=#toString(xmlPacket)#>
<!--- Query To XML, apart from xml packet creation, this will create a new myDoc.xml file :End--->
<!--- XML To Query : start--->
<cffile action="read" file="C:\ColdFusion9\wwwroot\myDoc.xml" variable="myxml">
<cfset myxml=XmlParse(myxml)>
<cfdump var="#myxml#">
<cfset emp=myxml.employees.XmlChildren>
<cfset size=ArrayLen(emp)>
<cfoutput>Number of Employees = #size#</cfoutput>
<cfset myQuery=QueryNew("empid,empname,empage,country,state,empsalary")>
<cfset temp=QueryAddRow(myQuery,#size#)>
<cfloop index="i" from="1" to="#size#">
<cfset temp=QuerySetCell(myQuery,"empid",#myXml.employees.employee.XmlAttributes.id#,#i#)>
<cfset temp=QuerySetCell(myQuery,"empname",#myXml.employees.employee[i].address.empname.XmlText#,#i#)>
<cfset temp=QuerysetCell(myQuery,"empage",#myXml.employees.employee[i].other.empage.XmlText#,#i#)>
<cfset temp=QuerySetCell(myQuery,"country",#myXml.employees.employee[i].address.empcountry.XmlText#,#i#)>
<cfset temp=QuerySetCell(myQuery,"state",#myXml.employees.employee[i].address.empstate.XmlText#,#i#)>
<cfset temp=QuerySetCell(myQuery,"empsalary",#myXml.employees.employee[i].other.empsalary.XmlText#,#i#)>
</cfloop>
<cfdump var="#myQuery#">
<!--- XML To Query : end--->
<cfquery name="empdetails" datasource="empdetails">
select empid,empname,empage,country,state,empsalary from empdetails
</cfquery>
<cfxml variable="xmlPacket">
<employees>
<cfoutput query="empdetails">
<employee id="#empid#">
<address>
<empname>#empname#</empname>
<empcountry>#country#</empcountry>
<empstate>#state#</empstate>
</address>
<other>
<empage>#empage#</empage>
<empsalary>#empsalary#</empsalary>
</other>
</employee>
</cfoutput>
</employees>
</cfxml>
<cfdump var="#xmlPacket#">
<cffile action="write" file="C:\ColdFusion9\wwwroot\myDoc.xml" output=#toString(xmlPacket)#>
<!--- Query To XML, apart from xml packet creation, this will create a new myDoc.xml file :End--->
<!--- XML To Query : start--->
<cffile action="read" file="C:\ColdFusion9\wwwroot\myDoc.xml" variable="myxml">
<cfset myxml=XmlParse(myxml)>
<cfdump var="#myxml#">
<cfset emp=myxml.employees.XmlChildren>
<cfset size=ArrayLen(emp)>
<cfoutput>Number of Employees = #size#</cfoutput>
<cfset myQuery=QueryNew("empid,empname,empage,country,state,empsalary")>
<cfset temp=QueryAddRow(myQuery,#size#)>
<cfloop index="i" from="1" to="#size#">
<cfset temp=QuerySetCell(myQuery,"empid",#myXml.employees.employee.XmlAttributes.id#,#i#)>
<cfset temp=QuerySetCell(myQuery,"empname",#myXml.employees.employee[i].address.empname.XmlText#,#i#)>
<cfset temp=QuerysetCell(myQuery,"empage",#myXml.employees.employee[i].other.empage.XmlText#,#i#)>
<cfset temp=QuerySetCell(myQuery,"country",#myXml.employees.employee[i].address.empcountry.XmlText#,#i#)>
<cfset temp=QuerySetCell(myQuery,"state",#myXml.employees.employee[i].address.empstate.XmlText#,#i#)>
<cfset temp=QuerySetCell(myQuery,"empsalary",#myXml.employees.employee[i].other.empsalary.XmlText#,#i#)>
</cfloop>
<cfdump var="#myQuery#">
<!--- XML To Query : end--->
Comments
Post a Comment