Solved

Convert XML to Query Object (Continued)

Posted on 2007-11-14
1
282 Views
Last Modified: 2013-12-16
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#">
0
Comment
Question by:cottyengland
1 Comment
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 20282256
Not tested, but just use the node array (loopN1L) and the array position


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

  <cfset row = QueryAddRow(yourQuery, 1) />
   <cfset yourQuery["Name"][row]= loopN1L[i].Name.name.XmlText>
  <cfset yourQuery["EntityIdentifierCode"][row] = loopN1L[i]Name.entityIdentifierCode.XmlText>
  ...etc..
</cfloop>
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question