Solved

Convert XML to Query Object

Posted on 2007-11-14
9
269 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
[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
  • 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 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] = 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 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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 automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
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…

622 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