Solved

XML Soap Call Formatting Data with Coldfusion

Posted on 2012-04-11
12
391 Views
Last Modified: 2012-04-18
I am pulling data from an outsite database via an xml soap call.

The current data that I am working with is a calendar of events (venues). Each venue has user defined attributes that that are associated with them.  These will be output in a ordered list.

I have the code setup to list the venue in a query using queryNew, but I can't figure out to manage the attributes and associate them with the correct venue.

There are multiple attributes for each venue. Some values are string values some are Boolean (I provided the code with a dump - sorry it is slow because of the dump).  For example Visa and Mastercard are Boolean values.  If true (1), they will get listed.  Start Date, End Date, file Logo are string values.

To me the data for the attributes needs to be a separate query where the venue ID is associated with the attribute, but I can't figure it out.

The code below shows what I have done so far and will provide dumps of the data:
<cfsavecontent variable="GetVenuesByVenueDefBody">
	<cfoutput>
		<?xml version="1.0" encoding="utf-8"?>
          <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
            <soap:Header>
              <AuthorizeHeader xmlns="http://ws.idssasp.com/venue.asmx">
                <UserName>burnsville</UserName>
                <Password>618b86b9-6ea0-421d-b4e4-a755619a5f53</Password>
              </AuthorizeHeader>
            </soap:Header>
            <soap:Body>
              <GetVenuesByVenueDef xmlns="http://ws.idssasp.com/venue.asmx">
                <venueDefID>43</venueDefID>
                <includeAddresses>false</includeAddresses>
                <includeSpaces>false</includeSpaces>
                <includeConfigs>false</includeConfigs>
                <includeAttributes>true</includeAttributes>
                <includeSpaceAttributes>false</includeSpaceAttributes>
              </GetVenuesByVenueDef>
            </soap:Body>
          </soap:Envelope>
	</cfoutput>
</cfsavecontent>

<cfhttp
	url="http://ws.idssasp.com/Venue.asmx"
	method="post"
	result="httpResponse">
    <cfhttpparam
        type="header"
        name="SOAPAction"
        value="http://ws.idssasp.com/Venue.asmx/GetVenuesByVenueDef"
        />
    <cfhttpparam
        type="header"
        name="accept-encoding"
        value="no-compression"
        />
    <cfhttpparam
		type="xml"
		value="#trim( GetVenuesByVenueDefBody )#"
		/>
</cfhttp>

<!--- Parse the XML SOAP response. --->
<cfset GetVenuesByVenueDefParsed = xmlParse( httpResponse.fileContent ) />

<cfset events = GetVenuesByVenueDefParsed["soap:Envelope"]["soap:Body"]["GetVenuesByVenueDefResponse"]["GetVenuesByVenueDefResult"].XmlChildren>

<cfdump var="#events#">


<cfset getEvents = QueryNew("VenueID, VenueName, VenueComments, AccountID, AccountName, Phone, SecondaryPhone")>

<cfloop FROM="1" TO="#arrayLen(events)#" index="i">
	<cfset venueValues = events[i].XmlChildren>
	<cfset accountValues = events[i]["VenueAccount"].XmlChildren>
    <cfset attributeValues = events[i]["Attributes"]["Attribute"].XmlChildren>

	<cfset queryAddRow(getEvents)>
	<cfset querySetCell(getEvents, "VenueID", venueValues[1]["XmlText"])>
	<cfset querySetCell(getEvents, "VenueName", venueValues[3]["XmlText"])>
	<cfset querySetCell(getEvents, "VenueComments", venueValues[3]["XmlText"])>
	<cfset querySetCell(getEvents, "AccountID", accountValues[1]["XmlText"])>
	<cfset querySetCell(getEvents, "AccountName", accountValues[3]["XmlText"])>
	<cfset querySetCell(getEvents, "Phone", accountValues[7]["XmlText"])>
	<cfset querySetCell(getEvents, "SecondaryPhone", accountValues[8]["XmlText"])>
</cfloop>

<cfdump var="#getEvents#">

Open in new window

0
Comment
Question by:lonnyo
[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
  • 6
  • 6
12 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 37835031
What's your version, 8 or 9?
0
 

Author Comment

by:lonnyo
ID: 37835076
Coldfusion 9
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37835124
CF9 should support a slightly more friendly syntax for accessing child elements. Start with something like the code below and we can refine it.

If true (1), they will get listed.  Start Date, End Date, file Logo are string values.

EDIT: So you want to skip the attributes whe  type/boolean = 0 and type/string=(empty string)?

 ... pre parsing ...
<cfset getEvents = QueryNew("VenueID, VenueName, VenueComments, AccountID, AccountName, Phone, SecondaryPhone")>
<cfset getAttributes = queryNew("VenueID,DisplayName,Type,Value")>

<cfloop array="#events#" index="venueNode">
	<cfset accountValues = venueNode["VenueAccount"]>
	<cfset attributesArray = venueNode["Attributes"].xmlChildren>
	<cfset CurrentVenueID =  venueNode['VenueID'].XmlText>
	
	<cfset row = queryAddRow(getEvents, 1)>
	<cfset getEvents.VenueID[row] = CurrentVenueID>
	<cfset getEvents.VenueName[row] = venueNode['VenueName'].XmlText >
	<cfset getEvents.VenueComments[row] =  venueNode['VenueComments'].XmlText >
	<cfset getEvents.AccountID[row] = accountValues['AccountID'].XmlText >
	<cfset getEvents.AccountName[row] = accountValues['AccountName'].XmlText >
	<cfset getEvents.Phone[row] = accountValues['Phone'].XmlText >
	<cfset getEvents.SecondaryPhone[row] = accountValues['SecondaryPhone'].XmlText >
	
	<cfloop array="#attributesArray#" index="AttributeNode">
		<cfset type       = AttributeNode["Type"].xmlText>
		<cfset value      = trim(AttributeNode["Value"].xmlText)>

		<cfif type eq "boolean" and value eq 1>
			<cfset includeAttribute = true>
		<cfelseif type eq "string" and len(value)>
			<cfset includeAttribute = true>
		<cfelse>
			<cfset includeAttribute = false>
		</cfif>		
		
		<cfif includeAttribute>			
			<cfset row = queryAddRow(getAttributes, 1)>
			<cfset getAttributes.VenueID[row] = CurrentVenueID>
			<cfset getAttributes.DisplayName[row] = AttributeNode["DisplayName"].xmlText>
			<cfset getAttributes.Type[row] = AttributeNode["Type"].xmlText>
			<cfset getAttributes.Value[row] = AttributeNode["Value"].xmlText>
		</cfif>
		
	</cfloop>
</cfloop>

<!--- only display the first few results so it doesn't take so long --->
<cfdump var="#getEvents#" top="5">
<cfdump var="#getAttributes#" top="100">

Open in new window

0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:lonnyo
ID: 37852529
Thank you so much.  I apologize for the delay.  This looks like it is working.  I just want to make sure I don't have any questions before I close it out.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37852770
No worries, it's good to be extra sure when it comes to XML .
0
 

Author Comment

by:lonnyo
ID: 37853917
Now I am trying to get the "Start Date" and "End Date" as part of the Events Query.  I have provided the updated code below.  It is only providing the date for the first row and End Date only - otherwise an empty string.

I wrapped these two fields with CreateODBCDate() so I can order them by the Start Date.

I wrapped these cfsets with try catch because the database I am pulling from does not validate these fields as dates (one of them has April spelled wrong and then produces an error without the try catch).

Also, how do I order these in order of the start date with?  Do I need to do a query of query?

Thanks
0
 

Author Comment

by:lonnyo
ID: 37853921
Forgot the code:

<cfset getEvents = QueryNew("VenueID, VenueName, VenueComments, AccountID, AccountName, Phone, SecondaryPhone, StartDate, EndDate")>
<cfset getAttributes = queryNew("VenueID,DisplayName,Type,Value")>

<cfloop array="#events#" index="venueNode">
	<cfset accountValues = venueNode["VenueAccount"]>
	<cfset attributesArray = venueNode["Attributes"].xmlChildren>
	<cfset CurrentVenueID =  venueNode['VenueID'].XmlText>
	
	<cfset row = queryAddRow(getEvents, 1)>
	<cfset getEvents.VenueID[row] = CurrentVenueID>
	<cfset getEvents.VenueName[row] = venueNode['VenueName'].XmlText >
	<cfset getEvents.VenueComments[row] =  venueNode['VenueComments'].XmlText >
	<cfset getEvents.AccountID[row] = accountValues['AccountID'].XmlText >
	<cfset getEvents.AccountName[row] = accountValues['AccountName'].XmlText >
	<cfset getEvents.Phone[row] = accountValues['Phone'].XmlText >
	<cfset getEvents.SecondaryPhone[row] = accountValues['SecondaryPhone'].XmlText >
    
	
	<cfloop array="#attributesArray#" index="AttributeNode">
		<cfset type       = AttributeNode["Type"].xmlText>
		<cfset value      = trim(AttributeNode["Value"].xmlText)>

		<cfif type eq "boolean" and value eq 1>
			<cfset includeAttribute = true>
		<cfelseif type eq "string" and len(value)>
			<cfset includeAttribute = true>
		<cfelse>
			<cfset includeAttribute = false>
		</cfif>		
		
		<cfif includeAttribute>			
                <cfif AttributeNode["DisplayName"].xmlText EQ "Start Date">
                    <cftry>
                    <cfset getEvents.StartDate[row] = CreateODBCDate(AttributeNode["Value"].xmlText)>
                    <cfcatch></cfcatch>
                    </cftry>
                <cfelseif AttributeNode["DisplayName"].xmlText EQ "End Date">
                    <cftry>
                    <cfset getEvents.EndDate[row] = CreateODBCDate(AttributeNode["Value"].xmlText)>
                    <cfcatch></cfcatch>
                    </cftry>
                <cfelse>
                	<cfset row = queryAddRow(getAttributes, 1)>
					<cfset getAttributes.VenueID[row] = CurrentVenueID>
                    <cfset getAttributes.DisplayName[row] = AttributeNode["DisplayName"].xmlText>
                    <cfset getAttributes.Value[row] = AttributeNode["Value"].xmlText>
                    <cfset getAttributes.Type[row] = AttributeNode["Type"].xmlText>
                </cfif>
		</cfif>
	</cfloop>
</cfloop>

<!--- only display the first few results so it doesn't take so long --->
<cfdump var="#getEvents#" top="5">
<cfdump var="#getAttributes#" top="100">

Open in new window

0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 37853997
It is only providing the date for the first row and End Date only -

It's because both loops share the same row number variable. You'll need to use separate variable names.  I can't test this right now, but change the inner attributes loop to something like

<cfloop array="#attributesArray#" index="AttributeNode">
      <cfset type       = AttributeNode["Type"].xmlText>
      <cfset value      = trim(AttributeNode["Value"].xmlText)>
       <cfset name     = AttributeNode["DisplayName"].xmlText>

       <cfif type eq "boolean" and value eq 1>
            <cfset includeAttribute = true>
       <cfelseif type eq "string" and len(value)>
             <cfset includeAttribute = true>
       <cfelse>
            <cfset includeAttribute = false>
       </cfif>            
            
      <cfif includeAttribute>                  
                  <!--- if it's one of the dates --->
                 <cfif name eq "Start Date" AND IsDate(value)>
                        <cfset getEvents.StartDate[row] = CreateODBCDate( value )>
                 <cfelseif name EQ "End Date" AND IsDate(value)>
                       <cfset getEvents.EndDate[row] = CreateODBCDate( value )>
                <cfelse>
                      <cfset attributeRow = queryAddRow(getAttributes, 1)>
                  <cfset getAttributes.VenueID[attributeRow ] = CurrentVenueID>
                        <cfset getAttributes.DisplayName[attributeRow ] = name>
                        <cfset getAttributes.Value[attributeRow ] = value >
                       <cfset getAttributes.Type[attributeRow ] = type>
                </cfif>
      </cfif>
</cfloop>

     Do I need to do a query of query?

Yes, but keep in mind the empty dates will probably sort first.
0
 

Author Comment

by:lonnyo
ID: 37854128
I have the dates associated with the the first query now. I need to evaluate this again tomorrow with a fresh brain.  I think I am close now.  I really appreciate your help.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37854204
Yeah, my brain is shot for the day :) I'll check back tomorrow and see how it goes.
0
 

Author Comment

by:lonnyo
ID: 37857630
I think this is working good.  May need more help on some other things, but I will close this out so you can get your points.  Again, thanks for the help.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37863187
You're welcome!
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

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