cottyengland
asked on
Convert XML to Query Object (Continued)
The code below does create a query with 5 rows... the problem is it is the same information all five times.
How do I modify the code to pull out the specific data for the row?
<cfset loopN1L = XMLSearch(xml,"Envelope/Pu rchaseOrde r/header/l oopN1") />
<cfset columnNames = "name">
<cfset columnNames = listAppend(columnNames, "entityIdentifierCode")>
<cfset columnNames = listAppend(columnNames, "identificationCode")>
<cfset columnNames = listAppend(columnNames, "identificationCodeQualifi er")>
<cfset columnNames = listAppend(columnNames, "AdditionalNameInformation ")>
<cfset columnNames = listAppend(columnNames, "addressInformation")>
<cfset columnNames = listAppend(columnNames, "CityName")>
<cfset columnNames = listAppend(columnNames, "StateOrProvinceCode")>
<cfset columnNames = listAppend(columnNames, "PostalCode")>
<cfset columnNames = listAppend(columnNames, "ContactFunctionCode")>
<cfset columnNames = listAppend(columnNames, "ContactName")>
<cfset columnNames = listAppend(columnNames, "CommunicationNumber")>
<cfset columnNames = listAppend(columnNames, "CommunicationNumberQualif ier")>
<cfset yourQuery = QueryNew(columnNames)>
<!--- <cfset row = queryAddRow(yourQuery, 1)> --->
<!--- <cfset xmlDoc = XMLParse(theXMLString, false)> --->
<cfset xmlDoc = XMLParse(xmlObject, false) />
<cfset loopN1L = XMLSearch(xmlDoc,"Envelope /PurchaseO rder/heade r/loopN1") />
<!--- <cfdump var="#xmlDoc#"> --->
<cfloop index="i" from="1" to="#ArrayLen(loopN1L)#">
<cfset row = QueryAddRow(yourQuery) />
<cfset yourQuery["Name"][row]= xmlDoc.Envelope.PurchaseOr der.header .loopN1.Na me.name.Xm lText>
<cfset yourQuery["EntityIdentifie rCode"][ro w] = xmlDoc.Envelope.PurchaseOr der.header .loopN1.Na me.entityI dentifierC ode.XmlTex t>
<cfset yourQuery["IdentificationC ode"][row] = xmlDoc.Envelope.PurchaseOr der.header .loopN1.Na me.identif icationCod e.XmlText>
<cfset yourQuery["IdentificationC odeQualifi er"][row] = xmlDoc.Envelope.PurchaseOr der.header .loopN1.Na me.identif icationCod eQualifier .XmlText>
<cfset yourQuery["AdditionalNameI nformation "][row] = xmlDoc.Envelope.PurchaseOr der.header .loopN1.Ad ditionalNa meInformat ion.Name.X mlText>
<cfset yourQuery["AddressInformat ion"][row] = xmlDoc.Envelope.PurchaseOr der.header .loopN1.Ad dressInfor mation.Add ressInform ation.XmlT ext>
<cfset yourQuery["CityName"][row] = xmlDoc.Envelope.PurchaseOr der.header .loopN1.Ge ographicLo cation.Cit yName.XmlT ext>
<cfset yourQuery["StateOrProvince Code"][row ] = xmlDoc.Envelope.PurchaseOr der.header .loopN1.Ge ographicLo cation.Sta teOrProvin ceCode.Xml Text>
<cfset yourQuery["PostalCode"][ro w] = xmlDoc.Envelope.PurchaseOr der.header .loopN1.Ge ographicLo cation.Pos talCode.Xm lText>
<cfset yourQuery["ContactName"][r ow] = xmlDoc.Envelope.PurchaseOr der.header .loopN1.Ad ministrati veCommunic ationsCont act.name.X mlText>
<cfset yourQuery["ContactFunction Code"][row ] = xmlDoc.Envelope.PurchaseOr der.header .loopN1.Ad ministrati veCommunic ationsCont act.contac tFunctionC ode.XmlTex t>
<cfset yourQuery["CommunicationNu mberQualif ier"][row] = xmlDoc.Envelope.PurchaseOr der.header .loopN1.Ad ministrati veCommunic ationsCont act.Commun icationNum berQualifi er.XmlText >
<cfset yourQuery["CommunicationNu mber"][row ] = xmlDoc.Envelope.PurchaseOr der.header .loopN1.Ad ministrati veCommunic ationsCont act.Commun icationNum ber.XmlTex t>
</cfloop>
<!--- show the query --->
<cfdump var="#yourQuery#">
How do I modify the code to pull out the specific data for the row?
<cfset loopN1L = XMLSearch(xml,"Envelope/Pu
<cfset columnNames = "name">
<cfset columnNames = listAppend(columnNames, "entityIdentifierCode")>
<cfset columnNames = listAppend(columnNames, "identificationCode")>
<cfset columnNames = listAppend(columnNames, "identificationCodeQualifi
<cfset columnNames = listAppend(columnNames, "AdditionalNameInformation
<cfset columnNames = listAppend(columnNames, "addressInformation")>
<cfset columnNames = listAppend(columnNames, "CityName")>
<cfset columnNames = listAppend(columnNames, "StateOrProvinceCode")>
<cfset columnNames = listAppend(columnNames, "PostalCode")>
<cfset columnNames = listAppend(columnNames, "ContactFunctionCode")>
<cfset columnNames = listAppend(columnNames, "ContactName")>
<cfset columnNames = listAppend(columnNames, "CommunicationNumber")>
<cfset columnNames = listAppend(columnNames, "CommunicationNumberQualif
<cfset yourQuery = QueryNew(columnNames)>
<!--- <cfset row = queryAddRow(yourQuery, 1)> --->
<!--- <cfset xmlDoc = XMLParse(theXMLString, false)> --->
<cfset xmlDoc = XMLParse(xmlObject, false) />
<cfset loopN1L = XMLSearch(xmlDoc,"Envelope
<!--- <cfdump var="#xmlDoc#"> --->
<cfloop index="i" from="1" to="#ArrayLen(loopN1L)#">
<cfset row = QueryAddRow(yourQuery) />
<cfset yourQuery["Name"][row]= xmlDoc.Envelope.PurchaseOr
<cfset yourQuery["EntityIdentifie
<cfset yourQuery["IdentificationC
<cfset yourQuery["IdentificationC
<cfset yourQuery["AdditionalNameI
<cfset yourQuery["AddressInformat
<cfset yourQuery["CityName"][row]
<cfset yourQuery["StateOrProvince
<cfset yourQuery["PostalCode"][ro
<cfset yourQuery["ContactName"][r
<cfset yourQuery["ContactFunction
<cfset yourQuery["CommunicationNu
<cfset yourQuery["CommunicationNu
</cfloop>
<!--- show the query --->
<cfdump var="#yourQuery#">
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.