Xml To Query

[acampolonghi]

Hi,

This is My problem:

I receive datas from an xml Feed and I always worked in this way:

<cftry>
<!--- fetch data from web service --->
<cfhttp url="http://www............. method="get" charset="utf-8"/>
<cfset xmldata = cfhttp.fileContent>
<cftry>
<!--- extract data from XML document and convert to query --->
<cfset lstColumns = "KH,NH,KC,NC,KD,ND,KS,NS,KL,NL,KZ,NZ,LR"/>
<cfset q2 = queryNew(lstColumns)/>

<!--- <cfif isXml(cfhttp.fileContent)> avialable in CF7 only --->
<cfset xmlObject = xmlParse(xmldata)/>
<cfloop index="i" from="1" to="#arrayLen(xmlObject["ArrayOfHotelRoom"].xmlChildren)#">
<cfset queryAddRow(q2,1)/>
<cfloop index="ii" list="#lstColumns#">
<cfset querySetCell(q2,ii,xmlObject["ArrayOfHotelRoom"].xmlChildren[ii].xmlText)/>
</cfloop>
</cfloop>
<!--- </cfif> --->
<cfcatch type="any"> <!--- any errors return empty result set --->
<cfset q2 = queryNew(lstColumns)/>
</cfcatch>
</cftry>
<cfcatch type="any">
<cfoutput>There has been an internal problem. Please try again later</cfoutput>
<cfexit>
</cfcatch>
</cftry>
<!--- use QoQ to filter by hotel name --->
<cfquery name="q" dbtype="query">
select * from q2
where SH = 'A' <!--- only show available hotels --->
</cfquery>

NOW THE PROBLEM IS THAT I NEED NOW TO IMPROVE MY SYSTEM USING A NEW VERSION OF THE XML CATALOGUE THAT NOW INCLUDE XML.CHILDREN:

THE NEW XML:

<?xml version="1.0" encoding="utf-8"?>
<ArrayOfHotelRoom xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://tempuri.org/xml/xmlService">
<HotelRoom>
<KH>717</KH>
<NH>Acanto Suites & Lounge</NH>
<KC>PDC</KC>
<NC>Playa del Carmen</NC>
<KD>16</KD>
<ND>Playa del Carmen</ND>
<KS>7E</KS>
<NS>Special Category</NS>
<KL>CENT</KL>
<NL>City</NL>
<KZ>1</KZ>
<NZ>The Yucatan Peninsula</NZ>
<LR>132.5000</LR>
<Rooms>
<RoomType>
<KR>VLLBR1</KR>
<NR>One Bedroom Villa</NR>
<MP>RO</MP>
<RR>132.5</RR>
<IP>0</IP>
<IL>0</IL>
<IN>0</IN>
<IF>0</IF>
<PH>0</PH>
<SH>A</SH>
</RoomType>
</Rooms>
</HotelRoom>

AS YOU SEE AFTER "LR" NOW I FIND OTHERS CHILDREN AND I DO NOT HOW TO REDUCE MY NEW XML TO QUERY

Thank You

Andrea

[l14n]

Hi

Reply if this works (You need to replace your code's relevant portion with this)

<!--- extract data from XML document and convert to query --->
<cfset lstColumns = "KH,NH,KC,NC,KD,ND,KS,NS,KL,NL,KZ,NZ,LR,KR,NR,MP,RR,IP,IL,IN,IF,PH,SH"/>
<cfset q2 = queryNew(lstColumns)/>

<!--- <cfif isXml(cfhttp.fileContent)> avialable in CF7 only --->
<cfset xmlObject = xmlParse(xmldata)/>
<cfloop index="i" from="1" to='#arrayLen(xmlObject["ArrayOfHotelRoom"].xmlChildren)#'>
<cfset queryAddRow(q2,1)/>

<cfset iii = 0>
<cfloop index="ii" list="#lstColumns#">
<cfif iii lt 13>
<cfset querySetCell(q2,ii,xmlObject["ArrayOfHotelRoom"].xmlChildren[i][ii].xmlText)/>
<cfelse>
<cfset querySetCell(q2,ii,xmlObject["ArrayOfHotelRoom"].xmlChildren[i].xmlChildren[14].xmlChildren[1].xmlChildren[iii-12].xmlText)/>
</cfif>
<cfset iii = iii + 1>
</cfloop>

</cfloop>

[l14n]

hi

the most important line (which was broken)

<cfset querySetCell(q2,ii,xmlObject["ArrayOfHotelRoom"].xmlChildren.xml
Children[14].xmlChildren[1].xmlChildren[iii-12].xmlText)/>