Solved

Convert XML to Query Object (Continued)

Posted on 2007-11-14
1
268 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
COldFusion html Display 10 107
Spell check for online editor 7 75
you tube .com 2 201
How to capture emails for mailing list in WordPress 3 59
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…
Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. 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 : Go t…

813 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now