XMLSearch Issue

[fiju]

Hi ,

My XML contains a node (I have given it below) which contains an xml text that is 'Please complete the "Enquiry Type" field' , as given below. And I am using a coldfusion function XMLSearch to search for an XML document Object. And I am using an XPath language (/langtexts/langtext[originaltext='Please complete the "Enquiry Type" field']) also, as given below. But the problem is I am getting an EMPTY ARRAY while serching the nodes using this function.

It would be much helpful and appreciated , if any one can solve this isuue.

Here is the code that I used:

<cfset XMLString = '<?xml version="1.0" encoding="UTF-8"?><langtexts><langtext langtextid="946" lang="JP"><originaltext>Please complete the  &amp;quot;Enquiry Type&quot; field</originaltext><content>（）を完成してください；問い合わせのタイプ（分野）</content></langtext></langtexts>'>
<cfset ParsedXML = xmlParse(XMLString)>
<cfset SearchResult = xmlSearch(ParsedXML, "/langtexts/langtext[originaltext='Please complete the  &amp;quot;Enquiry Type&quot; field']")>
<cfdump var = "#SearchResult#">

[arrgh]

Haven't figured out the magic string yet. But supposedly xpath gets ugly when quotes are involved.
http://kushalm.com/the-perils-of-xpath-expressions-specifically-escaping-quotes

[arrgh]

"Enquiry Type"

On second glance, that xml looks malformed. If the forum didn't mangle it, that literally translates to:

Please complete the "Enquiry Type" field

.. so the expression to match it would be:

<cfset SearchResult = xmlSearch(ParsedXML, 
		"/langtexts/langtext[originaltext='Please complete the &quot;Enquiry Type"" field']")>

[fiju]

Thanks for your response. But I am getting the same problem (Ie EMPTY ARRAY) while trying with your solution. Could any one solve this please.....

And i didnt get the meaning of xml malformation. In ECLIPSE (the editor that i am using) all CFMLs are getting highlighted correctly.
Could you please run this on your CFML editor.

[arrgh]

I am getting the same problem (Ie EMPTY ARRAY) while trying with your solution.

It works with a copy/paste of the xml you posted. If you're still getting an empty array, something about your actual data must be different.

And i didnt get the meaning of xml malformation.

I'm wondering if your first post got mangled by the forum ... and maybe that's why your search isn't working. In XML double quotes are escaped, so the phrase:

"Enquiry Type"

Would become

"Enquiry Type"

That's different than what you posted. The first post contains an extra & which is wrong.

&"Enquiry Type"

To find the right search string we need to have the _correct_ xml. Can you attach it a file or post a link to it?

[fiju]

Hi arrgh ,

Are you getting an array with elements in it? The posted XML is itself the original xml string that you are asking.

I followed the steps below ie like you suggested.
1.Replaced the string 'Please complete the "Enquiry Type" field' in the node 'originaltext' with 'Please complete the "Enquiry Type" field'.
2.Changed the expression to be mathed ie from "/langtexts/langtext[originaltext='Please complete the "Enquiry Type" field']"
to "/langtexts/langtext[originaltext='Please complete the "Enquiry Type"field']".

But still I am getting the same issue. Are you getting a non empty array while following the above steps (like you suggested). Please advice me in this.

[conork]

Hi,

This is my first post to this forum although I have looked through the questions and answers in the past. I am relatively new to using coldfusion and the problem I am having is:

I receive an XML file containing a whole lot of values.
I must search a database table for entries that correspond to particular values in the XML file.
I then must update the particular values in the database table with these new values from the xml file. what I do is:
I parse an XML file to get a particular value eg. account_id.
Then I execute an SQL query to get information from a database.
I parse the output of the query searching for the row that contains the particular account_id from above.
Then, I create the sql cmd to update a specific field contained in this row with a value that comes from the XML file.
I export the sql cmds to be run, to an excel file.
It all works so far, but now I need to execute the sql cmds in the excel spreadsheet on the server. But I am unsure of what to do here? Can anyone offer advice on what way I should look into doing this?
I am testing this code on a dummy db table before I put it live and my code so far is below. I would appreciate and help, thanks
Conor

My code:

<cfoutput>
 <!--- set currencyXmlFile = to the currencies.xml file to be searched ---->
    <cfset currencyXmlFile = "http://xxxxxx.com/conor/training/currencies/accounts.xml">
   <!--- take currencyXmlFile and turn it into a navigable xml tree structure and call it currencyDoc --->
 <cfset currencyDoc = xmlParse(currencyXmlFile)>

 <cfset queryAccounts = xmlSearch(currencyDoc, "userAccounts/userAccount/spotRate")>

    <!-- -->
 <!-- Start of table containing a List of all entries in this xml file -->    
    <!-- -->
 <table summary="This table contains a list of all information in the queryCurrency xml file. The information displayed for each account is: name, address, email and account_id.">
  <!---<cfdump var="#queryAccounts#"> --->

        <cfsavecontent variable="excel_output">
         <!--- loop through the spotRate(s) returned by the queryCurrency variable, index holds the counter --->
         <cfloop from="1" to="#arraylen(queryAccounts)#" index="cnt">
       <!--- set currencyXML = to the current queryAccounts[spotRate]  --->
    <cfset accountXML = xmlparse(queryAccounts[cnt])> 
             <cfset xml_name = #accountXML.spotRate.name.xmlText#> <!--- set xml_name for use further down --->

             <!--- Create the component instance. ---> 
    <cfobject component="user_accounts" name="user_accounts_obj"> 

    <cfinvoke component="#user_accounts_obj#"
        method="getUserAccounts"
              returnVariable="users">

       <!--- <cfdump var="#users#"> --->
       <cfloop query="users">
              <cfset db_name = #name#>
              <cfif xml_name eq db_name> <!--- test these 2 variables for equality --->
                    <!--- If they are equal, dynamically build the sql queries --->
      <tr>
                         <td>
        UPDATE conor_users SET account_id =
                         </td>
                         <td>
                             '#accountXML.spotRate.account_id.xmlText#'
       </td>
                         <td>
                             WHERE name = '#xml_name#'
                         </td>
            </tr>
                 <cfelse>
        <!---   <tr>
       <td colspan="4">Last Names are not equal</td>
      </tr> --->
                 </cfif>
       </cfloop> <!--- end of <cfloop query="users"> --->
   </cfloop> <!--- end of <cfloop from="1" to="#arraylen(queryAccounts)#" index="cnt"> --->
  </table>            
    </cfsavecontent>

    <!--- Create the file name --->                     
 <cfset f_name = "sql_update_users.xls">
    <cfset fullFilePath = "D:\sites\xxxxxx.com\conor\training\currencies\" & f_name>

 <cffile charset="ISO-8859-1" action="write" file="#fullFilePath#" output="#excel_output#" nameconflict="overwrite">
    <cfset attributes.suppresslayout = "TRUE">

 <cfheader name="Content-Disposition"  value="attachment;filename=#getFileFromPath(f_name)#">
 #excel_output#
</cfoutput>