Reading XML in

Posted on 2005-04-15
Last Modified: 2013-11-19
I have an XML doc like follows which is populated all day long by a client, about 5 records per minute. The client is responsible for populating the XML File with data and I'll read the data (this is a continual process all day long) and display some calculated results on an HTML page.

      <data field1="123" field2="456" field3="789"     .... about 60 attributes of type "double", could be up to 500
      <data field1="198" field2="745" field3="124"    
....... many records

I want to read the last e.g. 3 <data> records on the list and do an average of each value about every minute within the "page_load" method of the vb code. The page is set to refresh every 60 seconds in the <meta> tag. Some business logic is done on the values before they are display on the HTML page.

The XML file will get very large with all the readings - should the records be moved to another file for archiving as they'll need to be recalled in future?

Please advise the best method for my problem and give a code sample to read the last few rows in the list specific to my problem and do an average on them (and how to write the values to another file if this is what should be done)

Question by:rwallacej
    LVL 23

    Expert Comment

    1. Might it not be better to move this to a RDBMS like Microsoft SQL Server? Architecturally, there's nothing wrong in using Xml as a delivery mechanisam as well as a data-describing protocol.
    2. Since you are interested in say the last 3 rows, it would be advisable to move all unused rows to another file.

    I would strongly recommend against using Xml as your long-term storage platform unless you have purchased Tamino Server (Xml Database by Software AG).  Architecturally, what you have mentioned is still not enough and I am not convinced that you fully appreciate what you are asking for and the consequences.


    Author Comment

    my preference was for a RDBMS so maybe that is the better way to go. a guy I know suggested XML for storage but I'd never used it before - perhaps he just got caught up in the whole pro-XML thing...

    what more do you need to know architecturally?
    LVL 23

    Expert Comment

    1. Is this a WinForm or WebForm application?
    2. How does the client send the data to the server?
    3. What kind of logic is used to show archived data?

    Use xml for what it is best for:
    1. Delivery mechanism across heterogeneous environments.
    2. Data describing across heterogeneous environments.

    Now, there might not be any need for the client to post Xml in some situations. You just plug straight into ADO.NET.

    Author Comment

    1. must admit I don't know. I'm a student on placement and have been assigned to converting excel spreadsheets into webpages. the starting point is using exceleverywhere to convert the spreadsheet from Excel to exceleverywhere has limits in that some formulae aren't supported so these need to be coded in vb, and it cannot read values from a database (it just displays a form where the user types in there value and clicks submit to update results)
    2. the idea was that the client populated an xml data file with their measurements which we read using vb, carry out the calculations Excel can't do, and leave the other calculations to the source excel everywhere creates. previously the client populated our excel spreadsheet, we did the calculations (we don't show the client the business logic), and they read the calculated values out of excel to display on their own UI. the client wants to get rid of excel and make everything web-enabled.
    3. the archived data will be used to plot graphs comparing measurements over a period of e.g. week, month, year...for them to compare how equipment is performing over a period of time

    it seems after what you've said that we'll use RDBMS instead of XML (the idea with XML was that we wouldn't have to code the system for different client according to their database systems etc. and just tell each client to populate an xml file with the measurement fields needed)
    LVL 23

    Expert Comment

    1a. It seems that you have two distinct needs. The first is the need to convert excel spreadsheets to ASP.NET syntax. This need might be best met by a WinForm or WebForm application.
    1b. The second need where the ASP.NET page is to be display.
    1c. You would by now realise the difference between the actual data and the UI layer which is implemented in the ASP.NET page. So the question here is how are you actually converting Excel spreadsheets to ASP.NET pages?? If the data is inside the page, surely, you are as good as writing static HTML pages.
    1d. What you need is the ability to port data from Excel to an RDBMS Source from which you can query in a more optimised manner.
    1e. You can then leverage off Dundas Charts (commercial product) or use the free Reporting Services for Microsoft SQL Server (if you chose MS SQL Server). The report definition files incidentally are in XML and it is plugged into the VS.NET environment, so you can display charts from data from SQL Server.
    1f. Allows you to move away from Excel Spreadsheet.

    You can have the clients load the OWC (Office Web Components 2000 and above) and provide an Xml source from the web to this ActiveX Object. The OWC Excel spreadsheet is a subset of the spreadsheet available in Excel itself. In this case, you marry the need to move away from Excel and the need of the clients to have the "old" Excel look. Having said that, it will not be an overnight coding effort to pull this off. It will take more than that but it can be a satisfactory situation where the data is in MSSQL and the display is via Excel OWC.


    The OWC applies to version 2000 and above.


    Author Comment

    here's an example of what exceleverywhere produces for a simple spreadsheet. my initial idea was to work from this, populating the I/O from a RDBMS and add the extra functions into the file to populate the other fields (LINEST is a required feature that ExcelEverywhere doesn't support).  there is a lot of stuff happening in the measurements spreadsheets.   I am more and more feeling that Exceleverywhere is not going to do the job. file:  

    <%@ Page language="JScript" EnableViewState="false"  Inherits="ExpertExchange.ExcelEverywhere" Src="ExpertExchange.vb" CodeBehind="ExpertExchange.vb"%>
    <head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"></meta>
    <title>ExpertExchange.xls</title><meta name="GENERATOR" content="ExcelEverywhere for ASP and ASP.NET version 3.3.4 (build 4)"></meta>
    <!-- Parts of this page Copyright (C) 2002-2004 Framtidsforum I&M AB, Sweden --><script runat="server">
    // Run-time Library for server
      Some important restrictions from the EULA regarding the run-time library

    * Customers must keep the copyright notice
      'Parts of this page Copyright (C) 2002-2004 Framtidsforum I&M AB, Sweden'
      on all resulting source files.
    * You have a royalty-free right to reproduce and distribute the
      run-time library as an integral part of the source files partly
      constructed using the Software. You are not permitted to expose,
      either directly or indirectly, any API that allows programmatic
      access to the run-time libraries.
    * Your source files or software product(s) are targeted at end-users,
      and are not a development tool. */

    function myIsNaN(x){return(isNaN(x)||(typeof x=='number'&&!isFinite(x)));};function sum2(arr,rt,rb){var sum=0;for(var ii=rt;ii<=rb;ii++){sum+=arr[ii]};return sum};function sum3(arr,rt,ct,rb,cb){var sum=0;for(var ii=rt;ii<=rb;ii++){for(var jj=ct;jj<=cb;jj++){sum+=arr[ii][jj]}};return sum};function sumgeneral(cnt,vsum,vcnt,x){var sum=vsum;for(var ii=0;ii<x.length;ii++){sum+=sum3(x[ii][0],x[ii][1],x[ii][2],x[ii][3],x[ii][4]);};return sum;};function eeparseFloat(str){str=String(str).replace(eedecreg,".");var res=parseFloat(str);if(isNaN(res)){return 0;}else{return res;}};function eedisplayFloat(x){if(myIsNaN(x)){return Number.NaN;}else{return String(x).replace(/\./g,eedec);}};

    // initialization
    var x = new Object;
    var tmp;

    function setReset() : void {
      x.p1B1 = 10;
    x.p1B2 = 9;
    x.p1B3 = 8;
    // locale defs for server
    var eeisus=0;var eetrue="TRUE";var eefalse="FALSE";var eedec=".";var eeth=",";var eedecreg=new RegExp("[.]","g");var eethreg=new RegExp(",","g");
    // calc start

    var arr1xB1B3=new Array(3);var eecm1=new Array(new Array(arr1xB1B3,0,0,2,0));function calc(data){arr1xB1B3[0][0]=data.p1B1;arr1xB1B3[1][0]=data.p1B2;arr1xB1B3[2][0]=data.p1B3;var c1B4=(sumgeneral(3,0,0,eecm1));data.p1B4=c1B4;};

    function initArrays() : void {
    for (var ii=0; ii<3; ii++) { arr1xB1B3[ii] = new Array(1) ; for (var jj=0; jj<1; jj++) { arr1xB1B3[ii][jj]=0 } };;

    function fromForm() : void {


    function toForm() : void {

    function docalc() : void {

    function getObject() { return x; }

    <!-- ExcelEverywhere Header start -->

    <link rel="stylesheet" type="text/css" href="./styles.css"> </link>
    <link rel="stylesheet" type="text/css" href="./styles_screen.css" media="screen"> </link>
    <link rel="stylesheet" type="text/css" href="./styles_print.css" media="print"> </link>
    <script language="javascript">
    <!-- locale defs for client -->
    var eeisus=0;var eetrue="TRUE";var eefalse="FALSE";var eedec=".";var eeth=",";var eedecreg=new RegExp("[.]","g");var eethreg=new RegExp(",","g");
    <!-- library for client -->

    function myIsNaN(x){return(isNaN(x)||(typeof x=='number'&&!isFinite(x)));};function eeparseFloat(str){str=String(str).replace(eedecreg,".");var res=parseFloat(str);if(isNaN(res)){return 0;}else{return res;}};function eedisplayFloat(x){if(myIsNaN(x)){return Number.NaN;}else{return String(x).replace(/\./g,eedec);}};
    <!-- ExcelEverywhere Header end -->
    </head><body onload='<%= (panel_to_show == "panel1")?"document.formc.p1B1.focus();":"" %>'>

    <!-- ExcelEverywhere Body start -->

       <form id="formc" name="formc" method="post" action="" runat="server">

       <input name="xl_sheet_no" id="xl_sheet_no" type="hidden" value="<%= panel_to_show %>">
       <input name="xl_postback" id="xl_postback" type="hidden" value="1">


    <div class="eebuttonbar_top">

    <input class="eebuttons" type="submit" value="Update" name="xl_update_top">
    <input class="eebuttons" type="submit" value="Reset" name="xl_reset_top">
    <input class="eebuttons" style="margin-left:30px" type="submit" value="Submit" name="xl_submit_top"  >
    <input class="eebuttons" type="button" value="Print" name="xl_print_top" onclick="window.print();">


    <div id="panel1" style='display:<%= (panel_to_show == "panel1")?"block":"none" %>'>
         <table  style='border-collapse:collapse;width:96pt'  border="0" cellspacing="0" cellpadding="0" bgcolor="#FFFFFF" >
           <col width="48.00" />
           <col width="48.00" />
                <tr style='height:13pt'>
                      <td    class='ee100'    >
                                <asp:Label id="p1A1" runat="server">Value 1</asp:Label>
                      <td    class='ee103'    >
                        <input  name="p1B1" id="p1B1" type="text" runat="server"
                         onblur="this.value=eedisplayFloat(eeparseFloat(this.value));" tabindex="1"
                         style=' width:100% '
             class='ee105' >
                <tr style='height:13pt'>
                      <td    class='ee100'    >
                                <asp:Label id="p1A2" runat="server">Value 2</asp:Label>
                      <td    class='ee103'    >
                        <input  name="p1B2" id="p1B2" type="text" runat="server"
                         onblur="this.value=eedisplayFloat(eeparseFloat(this.value));" tabindex="2"
                         style=' width:100% '
             class='ee105' >
                <tr style='height:13pt'>
                      <td    class='ee100'    >
                                <asp:Label id="p1A3" runat="server">Value 3</asp:Label>
                      <td    class='ee103'    >
                        <input  name="p1B3" id="p1B3" type="text" runat="server"
                         onblur="this.value=eedisplayFloat(eeparseFloat(this.value));" tabindex="3"
                         style=' width:100% '
             class='ee105' >
                <tr style='height:13pt'>
                      <td    class='ee100'    >
                                <asp:Label id="p1A4" runat="server">Total</asp:Label>
                      <td    class='ee106'    >
                                <asp:Label id="p1B4" runat="server" />
    <div class="eebuttonbar_bottom">

    <input class="eebuttons" type="submit" value="Update" name="xl_update_bottom">
    <input class="eebuttons" type="submit" value="Reset" name="xl_reset_bottom">
    <input class="eebuttons" style="margin-left:30px" type="submit" value="Submit" name="xl_submit_bottom"  >
    <input class="eebuttons" type="button" value="Print" name="xl_print_bottom" onclick="window.print();">

    <span style="margin-left:60px;"> <a href='' target='_blank' tabindex='-1'>
    <img style='vertical-align: bottom;' border='0' src='powered_by_exceleverywhere.png' width='73' height='26' alt='Powered By ExcelEverywhere'></a>


    <script language="javascript">
    function reset_onclick(x){document.formc.reset();postcode();};function postcode(){};function eequerystring(){var;if(querystring.length>0){variables=(querystring.substring(1)).split("&");var variable;var key;var value;for(var ii=0;ii<variables.length;ii++){variable=variables[ii].split("=");key=unescape(variable[0]);value=unescape(variable[1]);if(document.formc[key]!=null){document.formc[key].value=value;}}}}function initial_update(){postcode('');eequerystring();}

    <!-- ExcelEverywhere Body end -->

    <noscript>The browser does not support JavaScript. The calculations created using  <a href='' target='_blank'>ExcelEverywhere</a>  will not work. Please access the web page using another browser.<p></p></noscript>
    </html> file

    ' This file will not be overwritten!
    ' This is the recommended location for backend integrations

    Imports System
    Imports System.Web.UI
    Imports System.Web.UI.WebControls
    Imports System.Web.UI.HtmlControls

    Namespace  ExpertExchange
        Public MustInherit class ExcelEverywhere : Inherits System.Web.UI.Page

            public Overridable Sub initArrays()
            end sub
            public Overridable Sub setReset()
            end sub
            public Overridable Sub fromForm()
            end sub
            public Overridable Sub docalc()
            end sub
            public Overridable Sub toForm()
            end sub

            ' You can either access the values as presented to the user
            ' by accessing then ASPX-fields or you can get the unformatted
            ' values by using getObject.
            ' see http:' on
            ' how to access the values in the Object
            public Overridable function getObject() as Object
            end function
            'All the ASPX-fields as of when this file was first generated.
         'The list is not updated, since this file is not overwritten.
         'In order to get the new list. Delete this file.

            'protected p1B1 as HtmlInputText
            'protected p1B2 as HtmlInputText
            'protected p1B3 as HtmlInputText
            'protected p1B4 as Label

            Protected Sub server_recalc(ByVal objSender As System.Object, ByVal objArgs As System.EventArgs )
               'no code here since Page_load handles the recalculation
            End Sub

            Protected panel_to_show as string
            Public Sub Page_Load(ByVal objSender As System.Object, ByVal objArgs As System.EventArgs )

                panel_to_show = "panel1"

                ' TIP: Add code here to set attributes of getObject to override default values
                if IsPostBack then
                  panel_to_show = Request.Params("xl_sheet_no")
               if Request.Params("xl_update_top") <> "" or Request.Params("xl_update_bottom") <> "" or Request.Params("xl_submit_top") <> "" or Request.Params("xl_submit_bottom") <> "" then
                 'it wasn't a reset
                       'TIP: Add code here to read attributes from getObject if you want to save the
                       'values entered by the user
                     end if
                end if

             if Request.Params("xl_submit_top") <> "" or Request.Params("xl_submit_bottom") <> "" then
                'submit was pressed: save and redirect to confirmation page

                   'TIP: Add code here to read attributes from x if you want to save the
                   'values entered by the user and the calculated values.
                end if


            End Sub
        End Class
    End Namespace


    clients say they don't want to install excel. excel is good in the sense that non-programmers (measurement engineers) can create the spreadsheets.
    is it possible to use an application to read/write from RDBMS to an excel spreadsheets without the client needing to have excel on their systems?  the clients populate a database just now with their measurements. what I need is to write these measurements to Excel, let excel do its calculations, and get the solutions onto a webpage.


    unfortunately the OWC is a no-no, or I'd have gone with this. Client's don't want activex object used. the client is only interested in viewing a few of the simplers sheets in our spreadsheet (the ones that display the end results of all the engineering calculations)

    I've looked at various charting products - and Dundas is our choice!  

    thanks for help thusfar
    LVL 23

    Accepted Solution

    The solution for you depends on whether you can port the client data from excel spreadsheets to an rdbms (no MS Access pls!!!). If you can, there;s nothing difficult with displaying data using DataGrids. The source could be views or stored procedures and gives you flexibility in implementing calculations etc. The issue is also about time, resources and skillset. If there is enough of this, you can go with what has beem aforementioned. If not, a third party tool like ExcelEverywhere may well be your solution for the immediate term.


    Author Comment

    thanks for your advice. I'll be reviewing things completely!
    LVL 23

    Expert Comment

    most welcome =)

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Most of the sites are being standardized with W3C Web Standards. W3C provides lot of web standard services to the web. They have the web specification, process and documentation for all the web standards. You can apply HTML, CSS and Accessibility st…
    Preface In the first article: A Better Website Login System ( I introduced the EE Collaborative Login System and its intended purpose. In this article I will discuss some of the design consideratio…
    Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
    The viewer will learn how to dynamically set the form action using jQuery.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now