Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 553
  • Last Modified:

Using XML Data as a Query

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
lonnyo
Asked:
lonnyo
  • 4
  • 3
1 Solution
 
SidFishesCommented:
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
 
micropc1Commented:
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
 
lonnyoAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
micropc1Commented:
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
 
lonnyoAuthor Commented:
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
 
micropc1Commented:
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
 
lonnyoAuthor Commented:
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
 
lonnyoAuthor Commented:
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

Independent Software Vendors: 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!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now