Solved

Convert XML to Query Object (Continued)

Posted on 2007-11-14
1
290 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
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 Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…

726 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