Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert XML to Query Object

Posted on 2007-11-14
9
Medium Priority
?
272 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 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
Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

 

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
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 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 insert an Iframe into 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 : Open Page or Post…

721 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