Convert XML to Query Object

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




            <name>DEFENSE SUPPLY CENTER COLUMBUS</name>





            <name>MARITIME SUPPLY CHAIN</name>



            <addressInformation>PO BOX 3990</addressInformation>













Question by:cottyengland
  • 5
  • 4
LVL 52

Expert Comment

ID: 20281055
Here is an example

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)>

Author Comment

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.
LVL 52

Accepted Solution

_agx_ earned 500 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] =>
<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#">
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.


Author Comment

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.

LVL 52

Expert Comment

ID: 20282080
You can use XmlChildren like in the livedocs example.

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.


Author Comment

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

Expert Comment

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

Author Comment

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

Expert Comment

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 ;-)

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Ecommerce options and opinions... 6 64
How to convert ASPX website to HTML? 3 56
HTTPS upload and download website building help 4 42
Problem to Popup 37 116
When deciding to adopt any help desk solutions many factors should be explored before taking decisions. This will change from business to another but in general there are some kind of rule of thumb. Here are some quick tips: Do we need only ticket…
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 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 Log…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…

786 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