Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Convert XML to Query Object

Posted on 2007-11-14
9
Medium Priority
?
274 Views
Last Modified: 2013-12-16
How do I convert the XML below to a Clodfusion query object?

<loopN1>

      <Name>

            <entityIdentifierCode>BY</entityIdentifierCode>



            <name>DEFENSE SUPPLY CENTER COLUMBUS</name>

            <identificationCodeQualifier>10</identificationCodeQualifier>

            <identificationCode>SPM7M1</identificationCode>

      </Name>

      <AdditionalNameInformation>

            <name>MARITIME SUPPLY CHAIN</name>

      </AdditionalNameInformation>

      <AddressInformation>

            <addressInformation>PO BOX 3990</addressInformation>

      </AddressInformation>

      <GeographicLocation>

            <cityName>COLUMBUS</cityName>

            <stateOrProvinceCode>OH</stateOrProvinceCode>

            <postalCode>432183990</postalCode>

      </GeographicLocation>

      <AdministrativeCommunicationsContact>

            <contactFunctionCode>BD</contactFunctionCode>

            <name>Searfoss</name>

            <communicationNumberQualifier>TE</communicationNumberQualifier>

            <communicationNumber>6146922049</communicationNumber>

      </AdministrativeCommunicationsContact>

</loopN1>
0
Comment
Question by:cottyengland
  • 5
  • 4
9 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 20281055
Here is an example
http://livedocs.adobe.com/coldfusion/7/htmldocs/00001518.htm

But do you really need to convert it to a query? You can extract the values by just parsing xml string.  For example

<cfset xmlDoc = XMLParse(yourXMLString)>
<cfoutput>
#xmlDoc.loopN1.Name.Name.XmlText#<br>
#xmlDoc.loopN1.Name.identificationCode.XmlText#<br>
</cfoutput>
0
 

Author Comment

by:cottyengland
ID: 20281256
Yes I do...

I need sample code related to this structure... I have many other structures to convert so this will be my template.

Thank you for you help.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 20281568
Note, you can use whatever column names and data types you wish when you define the query using QueryNew("").  

<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 yourQuery["Name"][row] = xmlDoc.loopN1.Name.Name.XmlText>
<cfset yourQuery["EntityIdentifierCode"][row] = xmlDoc.loopN1.Name.entityIdentifierCode.XmlText>
<cfset yourQuery["IdentificationCode"][row] = xmlDoc.loopN1.Name.identificationCode.XmlText>
<cfset yourQuery["IdentificationCodeQualifier"][row] = xmlDoc.loopN1.Name.identificationCodeQualifier.XmlText>
<cfset yourQuery["AdditionalNameInformation"][row] = xmlDoc.loopN1.AdditionalNameInformation.Name.XmlText>
<cfset yourQuery["AddressInformation"][row] = xmlDoc.loopN1.AddressInformation.AddressInformation.XmlText>
<cfset yourQuery["CityName"][row] = xmlDoc.loopN1.GeographicLocation.CityName.XmlText>
<cfset yourQuery["StateOrProvinceCode"][row] = xmlDoc.loopN1.GeographicLocation.StateOrProvinceCode.XmlText>
<cfset yourQuery["PostalCode"][row] = xmlDoc.loopN1.GeographicLocation.PostalCode.XmlText>
<cfset yourQuery["ContactName"][row] = xmlDoc.loopN1.AdministrativeCommunicationsContact.name.XmlText>
<cfset yourQuery["ContactFunctionCode"][row] = xmlDoc.loopN1.AdministrativeCommunicationsContact.contactFunctionCode.XmlText>
<cfset yourQuery["CommunicationNumberQualifier"][row] = xmlDoc.loopN1.AdministrativeCommunicationsContact.CommunicationNumberQualifier.XmlText>
<cfset yourQuery["CommunicationNumber"][row] = xmlDoc.loopN1.AdministrativeCommunicationsContact.CommunicationNumber.XmlText>

<!--- show the query --->
<cfdump var="#yourQuery#">
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:cottyengland
ID: 20281927
Thank you very much... this is perfect. I have one additional question that I did not realize until I saw your code work

If I have multiple instances of this node on in the xml how would I loop throuth them? For example build the query with mode than 1 column?

I will be awarding the points now.

 
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20282080
You can use XmlChildren like in the livedocs example.
   http://livedocs.adobe.com/coldfusion/7/htmldocs/00001518.htm

All they're doing is parsing the xml string and using ArrayLen() to count the number of nodes.
    <cfset xmlDoc = XMLParse(theXMLString, false)>
    <cfset numberOfNodes = arrayLen(xmlDoc.yourElementName.XmlChildren)>

Then using cfloop to add one row to the queryfor each node, and using the array index to extract the values for each node.


0
 

Author Comment

by:cottyengland
ID: 20282165
I am going to open up a new question...
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20282177
Okay.  But you'll find its pretty easy :)
0
 

Author Comment

by:cottyengland
ID: 20282255
nothing ever seems to be easy for me ;)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20282298
> nothing ever seems to be easy for me ;)

Yeah well, its always easier when it someone else's code. ie  something you haven't been staring/cursing at for 2 hours ;-)
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

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…
Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…
Suggested Courses

916 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