Retrieving & Saving XML attachments to MySQL Database

[jlig]

I've taken Ben Nadel's XML to MySQL Database Method Three and combined it with CFPOP to retrieve and save (JULIE digging/locating XML attachments sent by email) to a MySQL database table.

- The code works great when run, and the "static" XML data file is saved as a new record in my table. (see pdf link below)

- But how can I alter the code to work with dynamic XML file name(s)

- Currently I have hard-coded the <cffile action="read" file="C:\locates\xmlLocateAttachments\JULIEA_JULIEV_20120213133643312.xml" because I'm not sure how to tell it to just look at the folder, rather than specific files!

- I need to make this a dynamic process, where the CFPOP code/section/page runs every 30 min, and if a new email has arrived, then save the XML attachment(s) to the folder, and finally have Ben's code run that copies each XML attachment record to the database table?

ps: In each email from JULIE, there is always just one attachment, always in XML, with identical fields. I plan to have CFPOP delete the email once it has been processed..

Thanks, jlig

Here is my code: http://cerberus.clearwave.com/jerry/Untitled-2.pdf

Here is Ben's link: http://www.bennadel.com/blog/1636-How-To-Move-XML-Data-Into-A-Database-Using-ColdFusion.htm?&_=0.317...

[jlig]

I have simplified this to the following..

• Scheduled this code below to run every 2 minutes: (saveXMLattachments.cfm)

-----------------------------------------------------------------

<!---

          Call cfpop to get any new JULIE emails, save the XML attachments to file folder

          on the server

--->

<cfscript>

filepath = "C:\locates\xmlLocateAttachments";

eserver = "mail.zw.com";

euser = "lts@zw.com";

epass = "1234";

</cfscript>

<cfpop

action="getAll"

attachmentPath="#filepath#"

server="#eserver#"

username="#euser#"

password="#epass#"

name="qryMail"

generateUniqueFilenames ="Yes"

/>

<!---

          Call cfpop and delete the emails from the server since they have now been saved to file

--->

<cfpop

action="delete"

server="#eserver#"

username="#euser#"

password="#epass#"

/>

-----------------------------------------------------------------------

Now that I have the XML Attachments saved to a folder.. How do I use CFDIRECTORY to loop thru the files &

save each record to my database?

This code works to process one file at a time.. but I need to process the folder instead? (using CFDIRECTORY?)

------------------------------------------------------------------------

<cffile action="read" file="C:\locates\xmlLocateAttachments\JULIEA_JULIEV_20120213133643312.xml" variable="myxml">

29 <cfset data = XmlParse(myxml)>

[JR__Bob__Dobbs]

See sample below.  Note that this has not been tested.

<!--- create a query result object named directoryContent to contain listing of XML files --->

<cfdirectory directory="C:\locates\xmlLocateAttachments" action="list" filter="*.xml" name="directoryContent">

<!--- iterate over files --->

<cfloop query="directoryContent">

    <!--- create file name string --->

    <cfset fileFullPath="#directoryContent.directory#/#directoryContent.name#">

    <!--- read file --->

    <cfset fileContent=FileRead(fileFullPath)>

    <!--- assumes that you are inserting XML as a string into a MySQL database table's LONGTEXT column --->

    <cfquery name="insertXmlData">

        INSERT INTO some_table ( entry_date, xml_text )

        VALUES ( <cfqueryparam value="#Now()#" cfsqltype="cf_sql_date">, <cfqueryparam value="#fileContent#" cfsqltype="cf_sql_longvarchar"> );

    </cfquery>

</cfloop>

[jlig]

JR, thanks for the post..

Looking at my code below, what I was doing before was actually importing each XML node data value into specific MySQL columns..

Question: Looking at the code below, what changes would I make to your code to get it to work with each node rather than a string dump?

Thanks,

jlig

<!---

          Method 3: ColdFusion Pseudo XML Collections.

          As a convenience, ColdFusion provides pseudo collections of

          XML nodes such that they can be referenced by name. In this

          method, we are going to walk over these collections.

--->

<!---

          We know the structure of the XML document so we know that

          the record nodes are the direct children of the records node.

          Therefore, we can use the named path to find the target

          record nodes.

--->

<cfloop

          index="recordIndex"

          from="1"

          to="#arrayLen( data.NewDataSet.tickets )#"

          step="1">

          <!---

                    Since we can't loop over these ColdFusion psuedo XML

                    node collection using an array-loop, we have to use an

                    index loop. As such, I generally like to get a short-

                    hand reference to the node at the current index.

          --->

          <cfset recordNode = data.NewDataSet.tickets[ recordIndex ] />

          <!---

                    Now that we have the current record node, I am going to

                    grab the value of the given value nodes.

                    NOTE: Even when working with the pseudo collections, we

                    STILL HAVE TO use the xmlText attribute (otherwise

                    ColdFusion will convert the node to a string, giving it

                    a doctype).

                    NOTE: We can exclude the index of the value node (ex.

                    recordNode.name[ 1 ]) because we only want the first one

                    which is what not include the index defaults to. You can

                    use the [ 1 ], if you want..

          --->

          <cfset ticketNum = recordNode.ticket.xmlText />

          <cfset revisionNum = recordNode.revision[ 1 ].xmlText />

    <cfset startedDate = recordNode.started[ 1 ].xmlText />

    <cfset acctNum = recordNode.account[ 1 ].xmlText />

          <!--- Insert the values into the database. --->

          <cfquery name="insert" datasource="loc">

                    INSERT INTO tickets

                    (

                              ticket,

                              revision,

            started,

            account

                    ) VALUES (

                              <cfqueryparam value="#ticketNum#" cfsqltype="cf_sql_varchar" />,

                              <cfqueryparam value="#revisionNum#" cfsqltype="cf_sql_varchar" />,

            <cfqueryparam value="#startedDate#" cfsqltype="cf_sql_varchar" />,

            <cfqueryparam value="#acctNum#" cfsqltype="cf_sql_varchar" />

                    );

          </cfquery>

</cfloop>