Solved

Using XML Data as a Query

Posted on 2012-03-13
8
523 Views
Last Modified: 2012-03-16
I have a soap call that returns xml data in a single array. I need to figure out how to convert this data to a query format, so I can work with the individual columns and rows of data.  

Here is a link to see the dump of the data:
http://burnsville.ensemblecreative.com/GetCategoryList-Members.cfm
Below the dump is an output of the dump using cfoutput.

Here is the code that is used after the soap call (used the tutorial from Ben Nadel for the soap call - http://www.bennadel.com/blog/1809-Making-SOAP-Web-Service-Requests-With-ColdFusion-And-CFHTTP.htm):

<!---
      Now that we have our SOAP body defined, we need to post it as
      a SOAP request to the Campaign Monitor website. Notice that
      when I POST the SOAP request, I am NOT required to append the
      "WSDL" flag to the target URL (this is only required when you
      actually want to get the web service definition).
--->
<cfhttp
      url="http://ws.idssasp.com/Members.asmx"
      method="post"
      result="httpResponse">
 
      <!---
            Most SOAP action require some sort of SOAP Action header
            to be used.
      --->
      <cfhttpparam
            type="header"
            name="SOAPAction"
            value="http://ws.idssasp.com/Members.asmx/GetCategoryList"
            />
 
      <!---
            I typically use this header because CHTTP cannot handle
            GZIP encoding. This "no-compression" directive tells the
            server not to pass back GZIPed content.
      --->
      <cfhttpparam
            type="header"
            name="accept-encoding"
            value="no-compression"
            />
 
      <!---
            When posting the SOAP body, I use the CFHTTPParam type of
            XML. This does two things: it posts the XML as a the BODY
            and sets the mime-type to be XML.
 
            NOTE: Be sure to Trim() your XML since XML data cannot be
            parsed with leading whitespace.
      --->
      <cfhttpparam
            type="xml"
            value="#trim( soapBody )#"
            />
 
</cfhttp>
 
 
<!---
      When the HTTP response comes back, our SOAP response will be
      in the FileContent atribute. SOAP always returns valid XML,
      even if there was an error (assuming the error was NOT in the
      communication, but rather in the data).
--->
<cfif find( "200", httpResponse.statusCode )>
 
      <!--- Parse the XML SOAP response. --->
      <cfset soapResponse = xmlParse( httpResponse.fileContent ) />
 
      <!---
            Query for the response nodes using XPath. Because the
            SOAP XML document has name spaces, querying the document
            becomes a little funky. Rather than accessing the node
            name directly, we have to use its local-name().
      --->
      <cfset responseNodes = xmlSearch(
            soapResponse,
            "//*[ local-name() = 'GetCategoryListResult' ]"
            ) />
       
   
 
      <!---
            Once we have the response node, we can use our typical
            ColdFusion struct-style XML node access.
      --->
      <cfoutput>
          <!---<cfdump var="#variables#">--->
        <cfdump var="#responseNodes#">
             #soapResponse#
      </cfoutput>
</cfif>
0
Comment
Question by:lonnyo
  • 4
  • 3
8 Comments
 
LVL 36

Expert Comment

by:SidFishes
ID: 37720229
It's pretty simple using xmlparse (and in the docs ;p )

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=XML_13.html
0
 
LVL 7

Expert Comment

by:micropc1
ID: 37720325
Create a new query using QueryNew then loop through the XML nodes to build it up. XmlChildren can be accessed like arrays so you can loop through them with cfloop. Retrieve the node names and values from XmlName and XmlText.
0
 

Author Comment

by:lonnyo
ID: 37721792
I am having trouble understanding how to reference the nodes.  In the livedocs example they show the following code:

<cffile action="read" file="C:\CFusion\wwwroot\myexamples\employees.xml" variable="myxml">
<cfset mydoc = XmlParse(myxml)>

<!--- get an array of employees --->
<cfset emp = mydoc.employee.XmlChildren>
<cfset size = ArrayLen(emp)>

My code is:

<cfset soapResponse = xmlParse( httpResponse.fileContent ) />
(equivalent to "mydoc" variable above - I think)

This is the query I want to create:
    <cfset myquery = QueryNew("ID, ParentID, Name, Description, SortOrder")>

In the example code they have <cfset emp = mydoc.employee.XmlChildren>. I can't figure out how to do the dot notation to get this value.

When I try to do ArrayLen(soapResponse), it tells me the length is 1.  I need to get the length of the total rows of data and then loop through that array (I think it would be an array).

How do I access the xml data to treat it as an array with individual rows?  Here is the link again to see the dump of the soapResponse:

http://burnsville.ensemblecreative.com/GetCategoryList-Members.cfm

NOTE: there are two dumps.  The first one is the soapResponse and the second one is the responseNodes (see original code above).  I have tried working with both variables.  Not sure which one I should use.  The dumps are different.


Thanks for the help,
Lonny Olson
0
 
LVL 7

Expert Comment

by:micropc1
ID: 37721945
So yes, you start out with reading and parsing the XML...

<cfset myquery = QueryNew("ID, ParentID, Name, Description, SortOrder")>

<cffile action="read" file="C:\CFusion\wwwroot\myexamples\employees.xml" variable="myxml">
<cfset mydoc = XmlParse(myxml)>

Open in new window


then iterate through the nodes... (i haven't tested any of this since I don't have the xml data)

<cfset categories = myDoc["XmlRoot"].XmlChildren.XmlChildren.XmlChildren.XmlChildren>

<cfoutput>
   <cfloop from="1" to="#arrayLen(categories)#" index="i">
      <cfset categoryValues = categories[i].XmlChildren>
      <cfset queryAddRow(myQuery)>
      <cfset querySetCell(myQuery, "ID", "#categoryValues[1]["XmlValue"]#">
      <cfset querySetCell(myQuery, "ParentID", "#categoryValues[2]["XmlValue"]#">
      <cfset querySetCell(myQuery, "Name", "#categoryValues[3]["XmlValue"]#">
      <cfset querySetCell(myQuery, "Description", "#categoryValues[4]["XmlValue"]#">
      <cfset querySetCell(myQuery, "SortOrder", "#categoryValues[5]["XmlValue"]#">
   </cfloop>
</cfoutput>

Open in new window

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:lonnyo
ID: 37722572
The problem is that I don't have a file (the example in the live docs uses a file).  I have a soap call. The soap call uses a username and password to access it, so I have emailed the provider to see if they have any test data that can be used.

Unfortunately I don't have the example file that live docs is using, so I can't dump their data to see how it looks.

 I tried replacing the variable from the soap call with the xml parse that I have and got the following error:

Element XMLCHILDREN.XMLCHILDREN.XMLCHILDREN.XMLCHILDREN is undefined in a Java object of type class coldfusion.xml.XmlNodeMap referenced as ''

<cfset categories = mydoc["XmlRoot"].XmlChildren.XmlChildren.XmlChildren.XmlChildren>

Here is the code leading up to my dump (without the real username and password).  The comments are from the Ben Nadel post that I used to create the soap call.  There was more in the tutorial, which is above in my original post.

<cfsavecontent variable="soapBody">
      <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/Members.asmx">
                  <UserName>login</UserName>
                  <Password>password</Password>
                </AuthorizeHeader>
              </soap:Header>
              <soap:Body>
                <GetCategoryList xmlns="http://ws.idssasp.com/Members.asmx" />
                    </soap:Body>
            </soap:Envelope>
 
      </cfoutput>
</cfsavecontent>
 
 
<!---
      Now that we have our SOAP body defined, we need to post it as
      a SOAP request to the Campaign Monitor website. Notice that
      when I POST the SOAP request, I am NOT required to append the
      "WSDL" flag to the target URL (this is only required when you
      actually want to get the web service definition).
--->
<cfhttp
      url="http://ws.idssasp.com/Members.asmx"
      method="post"
      result="httpResponse">
 
      <!---
            Most SOAP action require some sort of SOAP Action header
            to be used.
      --->
      <cfhttpparam
            type="header"
            name="SOAPAction"
            value="http://ws.idssasp.com/Members.asmx/GetCategoryList"
            />
 
      <!---
            I typically use this header because CHTTP cannot handle
            GZIP encoding. This "no-compression" directive tells the
            server not to pass back GZIPed content.
      --->
      <cfhttpparam
            type="header"
            name="accept-encoding"
            value="no-compression"
            />
 
      <!---
            When posting the SOAP body, I use the CFHTTPParam type of
            XML. This does two things: it posts the XML as a the BODY
            and sets the mime-type to be XML.
 
            NOTE: Be sure to Trim() your XML since XML data cannot be
            parsed with leading whitespace.
      --->
      <cfhttpparam
            type="xml"
            value="#trim( soapBody )#"
            />
 
</cfhttp>
 
 
<!---
      When the HTTP response comes back, our SOAP response will be
      in the FileContent atribute. SOAP always returns valid XML,
      even if there was an error (assuming the error was NOT in the
      communication, but rather in the data).
--->

<cfif find( "200", httpResponse.statusCode )>
 
      <!--- Parse the XML SOAP response. --->
      <cfset mydoc = xmlParse( httpResponse.fileContent ) />
0
 
LVL 7

Accepted Solution

by:
micropc1 earned 500 total points
ID: 37723325
I was able to simulate your data using cfxml. I believe this should be close to what you want...

<cfxml variable="myDoc">
	<?xml version="1.0" encoding="UTF-8"?>
	<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
				xmlns:xsd="http://www.w3.org/2001/XMLSchema"
				xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
		<soap:Body>
			<GetCategoryResponse>
				<GetCategoryResult>
					<Category>
						<ID>3473</ID>
						<ParentID>3468</ParentID>
						<Name>Activities</Name>
						<Description></Description>
						<SortOrder>0</SortOrder>
					</Category>
					<Category>
						<ID>3494</ID>
						<ParentID>3469</ParentID>
						<Name>American</Name>
						<Description></Description>
						<SortOrder>0</SortOrder>
					</Category>
					<Category>
						<ID>3481</ID>
						<ParentID>3472</ParentID>
						<Name>Antiques</Name>
						<Description></Description>
						<SortOrder>0</SortOrder>
					</Category>
					<Category>
						<ID>3482</ID>
						<ParentID>3472</ParentID>
						<Name>Apparel</Name>
						<Description></Description>
						<SortOrder>0</SortOrder>
					</Category>
				</GetCategoryResult>
			</GetCategoryResponse>
		</soap:Body>
	</soap:Envelope>

</cfxml>

<cfset categories = myDoc["soap:Envelope"]["soap:Body"]["GetCategoryResponse"]["GetCategoryResult"].XmlChildren>

<cfset myquery = QueryNew("ID, ParentID, Name, Description, SortOrder")>

<cfloop from="1" to="#arrayLen(categories)#" index="i">
	<cfset categoryValues = categories[i].XmlChildren>
	<cfset queryAddRow(myQuery)>
	<cfset querySetCell(myQuery, "ID", categoryValues[1]["XmlText"])>
	<cfset querySetCell(myQuery, "ParentID", categoryValues[2]["XmlText"])>
	<cfset querySetCell(myQuery, "Name", categoryValues[3]["XmlText"])>
	<cfset querySetCell(myQuery, "Description", categoryValues[4]["XmlText"])>
	<cfset querySetCell(myQuery, "SortOrder", categoryValues[5]["XmlText"])>
</cfloop>

<cfdump var="#myquery#">

Open in new window


built query
0
 

Author Comment

by:lonnyo
ID: 37725917
I tried replacing the soap body with:

<soap:Body>
     <GetCategoryList xmlns="http://ws.idssasp.com/Members.asmx" />
</soap:Body>

I get the following error:

<soap:Body>
      <GetCategoryList xmlns="http://ws.idssasp.com/Members.asmx" />
</soap:Body>

I get the following error:

Element GetCategoryResponse is undefined in a Java object of type class coldfusion.xml.XmlNodeList.

It also produces an empty dump.

They are ok with me giving the username and password to access the data.  Is it possible I can email that to you?
0
 

Author Closing Comment

by:lonnyo
ID: 37729077
I did a dump of your data and a dump of mine. Somewhere along the way I must have grabbed a different soap call. Once I matched up the correct variables it worked.  Thank you so much for the help.  I wish I could give more points on this one!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
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 …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now