3 Replies Latest reply on Nov 25, 2008 4:50 AM by Newsgroup_User

    How To Process an XML File

    Level 7
      Hi All

      Clasic ASP
      MySQL
      Win 2K Server

      I have an XML file that is uploaded from clients via a browser which
      contains job data. Once uploaded I need to process it into a MySQL DB using
      classic ASP.
      The file contains 3 lots of elements under the main job element; these are:-
      <job_detail>
      <engineers>
      <materials>
      The upload and save aspect I can do no problem what I need to do is read the
      file and process each job detail record, each engineer detail record and
      each materials record into a database, something on the lines of:-

      Upload file from client
      Save file to disk
      Open XML file
      Select all job records
      Loop through and process job records
      Select all engineer records
      Loop through and process engineer records
      Select all material records
      Loop through and process material records

      How do I select the job detail, engineer and materials records, into say an
      array or a recordset for example. (if someone feels there is a better way
      then please feel free to suggest.)?

      An example of my XML file is below
      TIA

      Bren

      <?xml version="1.0" ?>
      - <jobs>
      - <job_detail>
      <j_recid>4041</j_recid>
      <j_numofvisits>1</j_numofvisits>
      <j_client>800</j_client>
      <j_site>864</j_site>
      <j_workdone />
      <j_suppliervisit>N</j_suppliervisit>
      <j_furtherwork>N</j_furtherwork>
      <j_returnvisit>N</j_returnvisit>
      <j_furtherworksdesc />
      <j_tobequoted>N</j_tobequoted>
      <j_status>6</j_status>
      <j_enteredby>Yolanda Baker</j_enteredby>
      <j_e_signature>53,29,-1,-1,53,29,53,30,53,30,58,33,58,33,75,45,75,45,110,58,110,58,149,61 ,149,61,176,54,176,54,186,42,186,42,191,33,191,33,192,30,192,30,192,29,192,29,189,29,189,2 9,184,28,184,28,174,29,174,29,159,32,159,32,124,39,124,39,82,46,82,46,54,50,54,50,43,50,43 ,50,38,50,38,50,37,49,37,49,38,49,38,49,39,49,39,49,57,49,57,49,109,54,109,54,180,56,180,5 6,239,54,239,54,277,46,277,46,281,45,281,45,281,44</j_e_signature>
      <j_e_name>Test Engineer</j_e_name>
      <j_e_position>Engineer</j_e_position>
      <j_e_comments />
      <j_e_sigdate>20/11/2008</j_e_sigdate>
      <j_c_signature>54,7,-1,-1,54,7,54,8,54,8,54,10,54,10,57,19,57,19,65,30,65,30,81,46,81,46, 100,58,100,58,121,62,121,62,144,63,144,63,163,57,163,57,181,47,181,47,190,41,190,41,200,36 ,200,36,207,33,207,33,207,31,207,31,201,29,201,29,197,27,197,27,193,27,193,27,184,28,184,2 8,172,31,172,31,160,34,160,34,146,40,146,40,140,43,140,43,138,45,138,45,137,45,137,45,136, 45,136,45,135,46,135,46,131,50,131,50,127,56,127,56,124,58,124,58,124,57,124,57,124,56,124 ,56,132,54,132,54,151,50,151,50,178,44,178,44,204,39,204,39,220,39,220,39,228,40,228,40,23 0,41,230,41,233,44,233,44,237,49,237,49,246,57,246,57,254,60,254,60,263,63,263,63,264,63</ j_c_signature>
      <j_c_name>Mr Blobby</j_c_name>
      <j_c_position>Fat Chap</j_c_position>
      <j_c_comments />
      <j_c_sigdate>20/11/2008</j_c_sigdate>
      <j_qos>3</j_qos>
      <j_notified>N</j_notified>
      <j_processed>N</j_processed>
      <j_active>Y</j_active>
      </job_detail>
      - <job_detail>
      <j_recid>4042</j_recid>
      <j_numofvisits>1</j_numofvisits>
      <j_client>798</j_client>
      <j_site>865</j_site>
      <j_workdone>As per job sheet</j_workdone>
      <j_suppliervisit>N</j_suppliervisit>
      <j_furtherwork>N</j_furtherwork>
      <j_returnvisit>N</j_returnvisit>
      <j_furtherworksdesc />
      <j_tobequoted>N</j_tobequoted>
      <j_status>6</j_status>
      <j_enteredby>Yolanda Baker</j_enteredby>
      <j_e_signature>44,20,-1,-1,44,20,43,20,43,20,42,20,42,20,39,21,39,21,34,25,34,25,28,29,28 ,29,25,32,25,32,22,38,22,38,22,41,22,41,22,43,22,43,22,44,22,44,26,44,26,44,38,45,38,45,64 ,42,64,42,104,36,104,36,135,33,135,33,165,31,165,31,178,31,178,31,185,33,185,33,189,35,189 ,35,192,38,192,38,197,41,197,41,203,43,203,43,211,49,211,49,220,51,220,51,228,52,228,52,23 1,53,231,53,232,49,232,49,233,43,233,43,230,34,230,34,229,31,229,31,224,27,224,27,213,24,2 13,24,195,23,195,23,171,27,171,27,152,32,152,32,141,36,141,36,138,36,138,36,137,37,137,37, 140,38,140,38,157,43,157,43,191,46,191,46,230,48,230,48,242,48</j_e_signature>
      <j_e_name>Test Engineer</j_e_name>
      <j_e_position>Engineer</j_e_position>
      <j_e_comments>Great job</j_e_comments>
      <j_e_sigdate>20/11/2008</j_e_sigdate>
      <j_c_signature>31,13,-1,-1,31,13,31,14,31,14,31,16,31,16,34,23,34,23,36,32,36,32,38,42,38 ,42,42,53,42,53,43,59,43,59,44,60,44,60,44,59,44,59,44,57,44,57,45,52,45,52,50,44,50,44,56 ,38,56,38,64,33,64,33,74,28,74,28,86,23,86,23,98,21,98,21,111,20,111,20,121,20,121,20,123, 20,123,20,123,21,123,21,126,24,126,24,126,27,126,27,130,33,130,33,133,37,133,37,135,42,135 ,42,139,47,139,47,142,51,142,51,144,54,144,54,151,57,151,57,157,57,157,57,164,52,164,52,17 1,46,171,46,180,42,180,42,193,38,193,38,203,38,203,38,215,38,215,38,228,38,228,38,238,39,2 38,39,245,39,245,39,247,39,247,39,248,38</j_c_signature>
      <j_c_name>Mr Blobby 2</j_c_name>
      <j_c_position>Fat Chap 2</j_c_position>
      <j_c_comments>Very well done</j_c_comments>
      <j_c_sigdate>20/11/2008</j_c_sigdate>
      <j_qos>1</j_qos>
      <j_notified>N</j_notified>
      <j_processed>N</j_processed>
      <j_active>Y</j_active>
      </job_detail>
      - <engineers>
      <m_recid>1</m_recid>
      <m_jid>4041</m_jid>
      <m_operative>18</m_operative>
      <m_starttime>07:00</m_starttime>
      <m_finishtime>17:00</m_finishtime>
      <m_traveltime>1</m_traveltime>
      <m_jobdate>20/11/2008</m_jobdate>
      </engineers>
      - <engineers>
      <m_recid>2</m_recid>
      <m_jid>4041</m_jid>
      <m_operative>3</m_operative>
      <m_starttime>07:00</m_starttime>
      <m_finishtime>17:00</m_finishtime>
      <m_traveltime>1</m_traveltime>
      <m_jobdate>20/11/2008</m_jobdate>
      </engineers>
      - <engineers>
      <m_recid>3</m_recid>
      <m_jid>4042</m_jid>
      <m_operative>3</m_operative>
      <m_starttime>07:00</m_starttime>
      <m_finishtime>17:00</m_finishtime>
      <m_traveltime>1</m_traveltime>
      <m_jobdate>20/11/2008</m_jobdate>
      </engineers>
      - <engineers>
      <m_recid>4</m_recid>
      <m_jid>4042</m_jid>
      <m_operative>25</m_operative>
      <m_starttime>09:00</m_starttime>
      <m_finishtime>17:00</m_finishtime>
      <m_traveltime>1</m_traveltime>
      <m_jobdate>20/11/2008</m_jobdate>
      </engineers>
      - <engineers>
      <m_recid>5</m_recid>
      <m_jid>4042</m_jid>
      <m_operative>8</m_operative>
      <m_starttime>07:00</m_starttime>
      <m_finishtime>17:00</m_finishtime>
      <m_traveltime>1</m_traveltime>
      <m_jobdate>20/11/2008</m_jobdate>
      </engineers>
      - <materials>
      <mu_recid>1</mu_recid>
      <mu_jid>4041</mu_jid>
      <mu_qty>1</mu_qty>
      <mu_description>Widget</mu_description>
      <mu_location>Van</mu_location>
      </materials>
      - <materials>
      <mu_recid>2</mu_recid>
      <mu_jid>4042</mu_jid>
      <mu_qty>20</mu_qty>
      <mu_description>2.5 T & E</mu_description>
      <mu_location>Van</mu_location>
      </materials>
      </jobs>


        • 1. Re: How To Process an XML File
          DEPearson Level 1
          Can you use C# asp.net 2.0? If you can; set up a xmldatasource for a grid view that would allow you to select a detail record.

          Here is a link to get you started:
          http://devpinoy.org/blogs/keithrull/archive/2008/03/31/how-to-use-the-xmldatasource-object -load-xml-data-to-a-gridview.aspx.

          I did something similar to this, but with less elements in the xml file. The grid view took care of any edits I made to the data.

          If you do this in classic asp, be prepare to do a lot of hand coding.

          • 2. Re: How To Process an XML File
            Level 7
            Hi DEPearson

            Thanks for your suggestion, unfortunately I don't know C# and my knowledge
            of ASP.NET is still very limited, a situation I am working on :-).
            I am aware of the xmldatasource you speak of, having done a little ASP.NET
            VB, but I don't feel that will resolve my situation.
            I have no issue with hand coding with classic ASP, in fact I very much enjoy
            hand coding.
            The inserting into the database is not a problem, what I really need answers
            to is the classic ASP way of opening the XML file then the accessing of the
            individual elements.

            For example:
            XML file is put in a fixed pickup directory -(This can be taken as a given
            fact)
            Open the XML file for reading using Classic ASP VBScript - (I am specualting
            FSO or XMLDOM open method)
            Read all <job_detail> elements - (Into an array or a recordset of some kind)
            Insert/Update DB with job detail information - (This is not an issue and
            would just be basic proceedure as with any other Insert/Update)
            Read all <engineer> elements - (Again, into an array or recordset)
            Process engineer details into DB - (Again no issue)
            Read all <materials> elements - (Again, into an array or recordset)
            Process materialsdetails into DB - (Again no issue)
            Close XML File

            TIA
            Bren

            "DEPearson" <webforumsuser@macromedia.com> wrote in message
            news:ggf7qe$18p$1@forums.macromedia.com...
            > Can you use C# asp.net 2.0? If you can; set up a xmldatasource for a grid
            > view
            > that would allow you to select a detail record.
            >
            > Here is a link to get you started:
            >
            > http://devpinoy.org/blogs/keithrull/archive/2008/03/31/how-to-use-the-xmldatasou
            > rce-object-load-xml-data-to-a-gridview.aspx.
            >
            > I did something similar to this, but with less elements in the xml file.
            > The
            > grid view took care of any edits I made to the data.
            >
            > If you do this in classic asp, be prepare to do a lot of hand coding.
            >
            >
            >


            • 3. Re: How To Process an XML File
              Level 7
              And there was much rejoiceing Yayyy! :-)
              I have sorted it, if anyone is interested then see my code below. The code
              below writes to the screen for testing purposes. To insert into a DB then
              just build your SQL statement and Insert or Update DB as you would normally
              instead of writing to screen.

              Code
              <%
              Dim objXML
              Set objXML = Server.CreateObject("Microsoft.XMLDOM")
              objXML.async = False
              objXML.load (Server.MapPath("/mwsclient/files/upload/Test_Engineer.xml"))
              If objXML.parseerror.errorCode <> 0 Then
              Response.Write(objXML.parseError.reason)
              Else
              Set objNodeList = objXML.documentElement.selectNodes("job_detail")
              For i = 0 To (objNodeList.length - 1)
              Response.Write("JOB RECORD<br>")
              For x = 0 To (objNodeList.Item(i).childNodes.length - 1)
              Response.Write(objNodeList.Item(i).childNodes(x).nodeName & " - ")
              Response.Write(objNodeList.Item(i).childNodes(x).xml & "<br>")
              Next
              Response.Write("<br>")
              Next
              End If

              Set objXML = Nothing
              %>

              Output:
              JOB RECORD
              j_recid - 4041
              j_numofvisits - 1
              j_client - 800
              j_site - 864
              j_workdone -
              j_suppliervisit - N
              j_furtherwork - N
              j_returnvisit - N
              j_furtherworksdesc -
              j_tobequoted - N
              j_status - 6
              j_enteredby - Yolanda Baker
              j_e_signature - 44
              j_e_name - Test Engineer
              j_e_position - Engineer
              j_e_comments -
              j_e_sigdate - 20/11/2008
              j_c_signature - 63
              j_c_name - Mr Blobby
              j_c_position - Fat Chap
              j_c_comments -
              j_c_sigdate - 20/11/2008
              j_qos - 3
              j_notified - N
              j_processed - N
              j_active - Y

              JOB RECORD
              j_recid - 4042
              j_numofvisits - 1
              j_client - 798
              j_site - 865
              j_workdone - As per job sheet
              j_suppliervisit - N
              j_furtherwork - N
              j_returnvisit - N
              j_furtherworksdesc -
              j_tobequoted - N
              j_status - 6
              j_enteredby - Yolanda Baker
              j_e_signature - 48
              j_e_name - Test Engineer
              j_e_position - Engineer
              j_e_comments - Great job
              j_e_sigdate - 20/11/2008
              j_c_signature - 38
              j_c_name - Mr Blobby 2
              j_c_position - Fat Chap 2
              j_c_comments - Very well done
              j_c_sigdate - 20/11/2008
              j_qos - 1
              j_notified - N
              j_processed - N
              j_active - Y