Solved

Using XML Data as a Query

Posted on 2012-03-13
8
529 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
[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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
XML Document XPath with Namespaces 3 40
ASP and Extracting XML 7 46
CRM 2011 Advanced Find Producing Error 3 42
SpreadsheetAddRow Sytax Error 4 13
Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

739 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