Skip to main content
J
jlig
Inspiring
February 14, 2012
Question

Retrieving & Saving XML attachments to MySQL Database

  • February 14, 2012
  • 1 reply
  • 2745 views

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.31745311200482734#comments_38922

    This topic has been closed for replies.

    1 reply

    J
    jligAuthor
    Inspiring
    February 15, 2012

    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)>

    J
    JR__Bob__Dobbs
    Inspiring
    February 15, 2012

    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>

    J
    jligAuthor
    Inspiring
    February 29, 2012

    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>

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices