Link to home
Start Free TrialLog in
Avatar of cottyengland
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/PurchaseOrder/header/loopN1") />

<cfset columnNames = "name">
<cfset columnNames = listAppend(columnNames, "entityIdentifierCode")>
<cfset columnNames = listAppend(columnNames, "identificationCode")>
<cfset columnNames = listAppend(columnNames, "identificationCodeQualifier")>
<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, "CommunicationNumberQualifier")>

<cfset yourQuery = QueryNew(columnNames)>
<!--- <cfset row = queryAddRow(yourQuery, 1)> --->
<!--- <cfset xmlDoc = XMLParse(theXMLString, false)> --->



<cfset xmlDoc = XMLParse(xmlObject, false) />
<cfset loopN1L = XMLSearch(xmlDoc,"Envelope/PurchaseOrder/header/loopN1") />

<!--- <cfdump var="#xmlDoc#"> --->

<cfloop index="i" from="1" to="#ArrayLen(loopN1L)#">

<cfset row = QueryAddRow(yourQuery) />



 <cfset yourQuery["Name"][row]= xmlDoc.Envelope.PurchaseOrder.header.loopN1.Name.name.XmlText>
<cfset yourQuery["EntityIdentifierCode"][row] = xmlDoc.Envelope.PurchaseOrder.header.loopN1.Name.entityIdentifierCode.XmlText>
<cfset yourQuery["IdentificationCode"][row] = xmlDoc.Envelope.PurchaseOrder.header.loopN1.Name.identificationCode.XmlText>
<cfset yourQuery["IdentificationCodeQualifier"][row] = xmlDoc.Envelope.PurchaseOrder.header.loopN1.Name.identificationCodeQualifier.XmlText>
<cfset yourQuery["AdditionalNameInformation"][row] = xmlDoc.Envelope.PurchaseOrder.header.loopN1.AdditionalNameInformation.Name.XmlText>
<cfset yourQuery["AddressInformation"][row] = xmlDoc.Envelope.PurchaseOrder.header.loopN1.AddressInformation.AddressInformation.XmlText>
<cfset yourQuery["CityName"][row] = xmlDoc.Envelope.PurchaseOrder.header.loopN1.GeographicLocation.CityName.XmlText>
<cfset yourQuery["StateOrProvinceCode"][row] = xmlDoc.Envelope.PurchaseOrder.header.loopN1.GeographicLocation.StateOrProvinceCode.XmlText>
<cfset yourQuery["PostalCode"][row] = xmlDoc.Envelope.PurchaseOrder.header.loopN1.GeographicLocation.PostalCode.XmlText>
<cfset yourQuery["ContactName"][row] = xmlDoc.Envelope.PurchaseOrder.header.loopN1.AdministrativeCommunicationsContact.name.XmlText>
<cfset yourQuery["ContactFunctionCode"][row] = xmlDoc.Envelope.PurchaseOrder.header.loopN1.AdministrativeCommunicationsContact.contactFunctionCode.XmlText>
<cfset yourQuery["CommunicationNumberQualifier"][row] = xmlDoc.Envelope.PurchaseOrder.header.loopN1.AdministrativeCommunicationsContact.CommunicationNumberQualifier.XmlText>
<cfset yourQuery["CommunicationNumber"][row] = xmlDoc.Envelope.PurchaseOrder.header.loopN1.AdministrativeCommunicationsContact.CommunicationNumber.XmlText>

</cfloop>

<!--- show the query --->
<cfdump var="#yourQuery#">
ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial