Solved

Convert XML to Query Object

Posted on 2007-11-14
9
268 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
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Standard user authenification for website plugin? 3 184
Spell check for online editor 7 83
PHP v5.4 and Class 'mysqli' not found  error 8 180
create a website 10 83
In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…
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…

752 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