• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1301
  • Last Modified:

How do I extract data from an XML File?

My experience with XML is limited.  I am trying to extract data from the sampling of the below XML file at end. The data use to be in a text file that was imported into Excel and processed via macros.  I need to get the XML data into the same spreadsheet format to continue using the extensive programming within the Excel file.

Using the XMLTextReader, I am able to see the data, but I am trying to execute specific code based on the various elements.  Example, the values of the SummaryValueData will be written in a specific area of an Excel Spreadsheet. The values of the DailyTestSummaryData will be written to another location as will the DailyCalibrationData values within the DailyTestSummaryData.  The same will be true for the HourlyOperatingData.  I am looking specifically for VB.Net coding (I am using 2003) to extract the data based on the major elements.  Using the Excel XML features do not accomplish what I need, hence the VB.NET approach.

================CODE SNIPPET

Sub emXml()
        'this section will process the EM xml file
        'strFileName is a global variable
        MsgBox("emXml : " & strFileName)

        Dim strTemp As String

        'Declare and Load file

        Dim xmlDoc As New XmlDocument
        Dim xNode As XmlNode
        xmlDoc.Load(strFileName)

        Dim reader As XmlTextReader = New XmlTextReader(strFileName)

        Do While (reader.Read())

            Select Case reader.NodeType
                Case XmlNodeType.Element 'Display beginning of element.
                    strTemp = (reader.Name)
                    If reader.HasAttributes Then 'If attributes exist
                        While reader.MoveToNextAttribute()
                            'Display attribute name and value.
                            strTemp = strTemp & (" {0}='{1}'" & reader.Name & reader.Value)
                        End While
                    End If
                    strTemp = strTemp

                    If strTemp = "SummaryValueData" Then
                     'this returns an integer...not what I wanted
                        MsgBox(XmlNodeType.Text & "  " & reader.Value)
                    End If

                    If strTemp = "DailyTestSummaryData" Then
                    'this returns an integer...not what I wanted
                        MsgBox("DTSD  " & XmlNodeType.Text & "  " & reader.Value)
                    End If

                Case XmlNodeType.Text 'Display the text in each element.

                    'this displays all of my values
                    lblFileName.Text = strTemp & ": " & reader.Value()
                    lblFileName.Refresh()
                    Dim x As Integer
                    For x = 1 To 100000000
                        'for next set just to allow the lblFileName.text
                        'to be read-not to be left as permanent part
                        'of program
                    Next

                    Select Case strTemp
                        'this is looking at various element names and gives appropriate value except it
                        'never hits the SummaryValueData Case
                        Case "ORISCode"
                            MsgBox("ORis is " & reader.Value)
                        Case "Year"
                            MsgBox("Year is " & reader.Value)
                        Case "SummaryValueData"
                            MsgBox("SummaryValueData is " & reader.Value)
                    End Select

            End Select


        Loop

        'Call UpdateExcel()

    End Sub

''''''''''XML FILE SAMPLING''''''''''''''''''''''


<?xml version="1.0" encoding="utf-8"?>
<Emissions>
   <ORISCode>1234</ORISCode>
   <Year>2007</Year>
   <Quarter>1</Quarter>
   <SubmissionComment/>
   <Version>1.2</Version>
   <SummaryValueData>
      <UnitID>001</UnitID>
      <ParameterCode>CO2M</ParameterCode>
      <CurrentReportingPeriodTotal>238270.800</CurrentReportingPeriodTotal>
      <OzoneSeasonToDateTotal/>
      <YearToDateTotal>238270.800</YearToDateTotal>
   </SummaryValueData>
   <DailyTestSummaryData>
         <UnitID>001</UnitID>
         <Date>2009-01-01</Date>
         <Hour>7</Hour>
         <Minute>24</Minute>
         <MonitoringSystemID/>
         <ComponentID>013</ComponentID>
         <TestTypeCode>DAYCAL</TestTypeCode>
         <TestResultCode>PASSED</TestResultCode>
         <SpanScaleCode/>
         <DailyCalibrationData>
            <OnLineOffLineIndicator>1</OnLineOffLineIndicator>
            <UpscaleGasCode>HIGH</UpscaleGasCode>
            <ZeroInjectionDate>2009-01-01</ZeroInjectionDate>
            <ZeroInjectionHour>7</ZeroInjectionHour>
            <ZeroInjectionMinute>22</ZeroInjectionMinute>
            <UpscaleInjectionDate>2009-01-01</UpscaleInjectionDate>
            <UpscaleInjectionHour>7</UpscaleInjectionHour>
            <UpscaleInjectionMinute>24</UpscaleInjectionMinute>
            <ZeroMeasuredValue>0.000</ZeroMeasuredValue>
            <UpscaleMeasuredValue>2.600</UpscaleMeasuredValue>
            <ZeroAPSIndicator>0</ZeroAPSIndicator>
            <UpscaleAPSIndicator>0</UpscaleAPSIndicator>
            <ZeroCalibrationError>0.00</ZeroCalibrationError>
            <UpscaleCalibrationError>2.50</UpscaleCalibrationError>
            <ZeroReferenceValue>0.000</ZeroReferenceValue>
            <UpscaleReferenceValue>2.500</UpscaleReferenceValue>
         </DailyCalibrationData>
   </DailyTestSummaryData>
   <DailyTestSummaryData>
         <UnitID>001</UnitID>
         <Date>2009-01-01</Date>
         <Hour>7</Hour>
         <Minute>25</Minute>
         <MonitoringSystemID/>
         <ComponentID>013</ComponentID>
         <TestTypeCode>INTCHK</TestTypeCode>
         <TestResultCode>PASSED</TestResultCode>
         <SpanScaleCode/>
   </DailyTestSummaryData>
    <HourlyOperatingData>
         <UnitID>001</UnitID>
         <Date>2009-03-20</Date>
         <Hour>15</Hour>
         <OperatingTime>1.00</OperatingTime>
         <HourLoad>90</HourLoad>
         <LoadUnitsOfMeasureCode>MW</LoadUnitsOfMeasureCode>
         <LoadRange>9</LoadRange>
         <CommonStackLoadRange/>
         <FcFactor>1840.0</FcFactor>
         <FdFactor/>
         <FwFactor/>
         <FuelCode/>
         <MonitorHourlyValueData>
            <ParameterCode>CO2C</ParameterCode>
            <UnadjustedHourlyValue>12.700</UnadjustedHourlyValue>
            <AdjustedHourlyValue/>
            <MODCCode>01</MODCCode>
            <MonitoringSystemID>104</MonitoringSystemID>
            <ComponentID>014</ComponentID>
            <PercentAvailable>86.8</PercentAvailable>
            <MoistureBasis/>
         </MonitorHourlyValueData>
         <MonitorHourlyValueData>
            <ParameterCode>FLOW</ParameterCode>
            <UnadjustedHourlyValue>15177000.000</UnadjustedHourlyValue>
            <AdjustedHourlyValue>15420000.000</AdjustedHourlyValue>
            <MODCCode>01</MODCCode>
            <MonitoringSystemID>103</MonitoringSystemID>
            <ComponentID>013</ComponentID>
            <PercentAvailable>99.9</PercentAvailable>
            <MoistureBasis/>
         </MonitorHourlyValueData>
         <MonitorHourlyValueData>
            <ParameterCode>NOXC</ParameterCode>
            <UnadjustedHourlyValue>107.900</UnadjustedHourlyValue>
            <AdjustedHourlyValue/>
            <MODCCode>01</MODCCode>
            <MonitoringSystemID/>
            <ComponentID>015</ComponentID>
            <PercentAvailable/>
            <MoistureBasis/>
         </MonitorHourlyValueData>
         <MonitorHourlyValueData>
            <ParameterCode>SO2C</ParameterCode>
            <UnadjustedHourlyValue>51.800</UnadjustedHourlyValue>
            <AdjustedHourlyValue>51.800</AdjustedHourlyValue>
            <MODCCode>01</MODCCode>
            <MonitoringSystemID>102</MonitoringSystemID>
            <ComponentID>012</ComponentID>
            <PercentAvailable>86.8</PercentAvailable>
            <MoistureBasis/>
         </MonitorHourlyValueData>
         <DerivedHourlyValueData>
            <ParameterCode>CO2</ParameterCode>
            <UnadjustedHourlyValue/>
            <AdjustedHourlyValue>111.6000</AdjustedHourlyValue>
            <MODCCode/>
            <MonitoringSystemID/>
            <FormulaIdentifier>401</FormulaIdentifier>
            <PercentAvailable/>
            <OperatingConditionCode/>
            <SegmentNumber/>
            <FuelCode/>
         </DerivedHourlyValueData>
         <DerivedHourlyValueData>
            <ParameterCode>HI</ParameterCode>
            <UnadjustedHourlyValue/>
            <AdjustedHourlyValue>1064.3000</AdjustedHourlyValue>
            <MODCCode/>
            <MonitoringSystemID>104</MonitoringSystemID>
            <FormulaIdentifier>801</FormulaIdentifier>
            <PercentAvailable/>
            <OperatingConditionCode/>
            <SegmentNumber/>
            <FuelCode/>
         </DerivedHourlyValueData>
         <DerivedHourlyValueData>
            <ParameterCode>NOXR</ParameterCode>
            <UnadjustedHourlyValue>0.187</UnadjustedHourlyValue>
            <AdjustedHourlyValue>0.1870</AdjustedHourlyValue>
            <MODCCode>01</MODCCode>
            <MonitoringSystemID>105</MonitoringSystemID>
            <FormulaIdentifier>501</FormulaIdentifier>
            <PercentAvailable>78.4</PercentAvailable>
            <OperatingConditionCode/>
            <SegmentNumber/>
            <FuelCode/>
         </DerivedHourlyValueData>
         <DerivedHourlyValueData>
            <ParameterCode>SO2</ParameterCode>
            <UnadjustedHourlyValue/>
            <AdjustedHourlyValue>132.6000</AdjustedHourlyValue>
            <MODCCode/>
            <MonitoringSystemID/>
            <FormulaIdentifier>201</FormulaIdentifier>
            <PercentAvailable/>
            <OperatingConditionCode/>
            <SegmentNumber/>
            <FuelCode/>
         </DerivedHourlyValueData>
   </HourlyOperatingData>
   <HourlyOperatingData>
         <UnitID>001</UnitID>
         <Date>2009-03-31</Date>
         <Hour>23</Hour>
         <OperatingTime>1.00</OperatingTime>
         <HourLoad>89</HourLoad>
         <LoadUnitsOfMeasureCode>MW</LoadUnitsOfMeasureCode>
         <LoadRange>9</LoadRange>
         <CommonStackLoadRange/>
         <FcFactor>1840.0</FcFactor>
         <FdFactor/>
         <FwFactor/>
         <FuelCode/>
         <MonitorHourlyValueData>
            <ParameterCode>NOXC</ParameterCode>
            <UnadjustedHourlyValue>108.800</UnadjustedHourlyValue>
            <AdjustedHourlyValue/>
            <MODCCode>01</MODCCode>
            <MonitoringSystemID/>
            <ComponentID>015</ComponentID>
            <PercentAvailable/>
            <MoistureBasis/>
          </MonitorHourlyValueData>
          <MonitorHourlyValueData>
            <ParameterCode>SO2C</ParameterCode>
            <UnadjustedHourlyValue>57.000</UnadjustedHourlyValue>
            <AdjustedHourlyValue>57.000</AdjustedHourlyValue>
            <MODCCode>01</MODCCode>
            <MonitoringSystemID>102</MonitoringSystemID>
            <ComponentID>012</ComponentID>
            <PercentAvailable>86.8</PercentAvailable>
            <MoistureBasis/>
          </MonitorHourlyValueData>
          <DerivedHourlyValueData>
            <ParameterCode>SO2</ParameterCode>
            <UnadjustedHourlyValue/>
            <AdjustedHourlyValue>144.2000</AdjustedHourlyValue>
            <MODCCode/>
            <MonitoringSystemID/>
            <FormulaIdentifier>201</FormulaIdentifier>
            <PercentAvailable/>
            <OperatingConditionCode/>
            <SegmentNumber/>
            <FuelCode/>
         </DerivedHourlyValueData>
      </HourlyOperatingData>
</Emissions>
0
abcserve
Asked:
abcserve
  • 6
  • 2
3 Solutions
 
aboredmanCommented:
0
 
abcserveAuthor Commented:
I will review the informatin and advise.  Thank you.
0
 
abcserveAuthor Commented:

I am unable to resolve the underlined code that was an example in the suggested link.  I am not able to get the XmlReaderSettings in the VB.Net 2003.  I have added the MSXML2 reference.  Any suggestions.

Dim myXmlSettings As New XmlReaderSettings
Using readMovieInfo As XmlReader = XmlReader.Create("test.xml", myXmlSettings)
While readMovieInfo.Read()
    ShowXmlNode(readMovieInfo)
    While readMovieInfo.MoveToNextAttribute()
          ShowXmlNode(readMovieInfo)
     End While
End While
End Using
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
abcserveAuthor Commented:
To anyone.  
I think problem with the XmlReaderSettings is the version of .Net Framework.  Even though the folder structure shows multiple Framework folders, the System.dll is showing version 1.xxxx

I am not finding an immediate solution to correct this.  Please advise.

Thanks.
0
 
abcserveAuthor Commented:
ruffone

Sorry, I cannot open the VB files as I do not have the newer version of Visual Studio (I am working that).  I will check out the video.  Thanks for your input.
0
 
abcserveAuthor Commented:
ruffone and aboredman

Just wanted to update you on this questions. I have the Express Edition of Visual Studio 2008.  I am in the process of determining which solution is going to work for me.  Thanks for your input.
0
 
abcserveAuthor Commented:
With the information from aboredman and ruffone, I was able to continue various avenues of research to come up with a solution as shown in the code snippet.   Even though your comments did not provide a direct solution, they were of value.  Thank you both for your assistances.
	'The following code snippet was placed in the click event for my testing purposes.
	'It provide the result of being able to extract the values that I needed.
	'Additional coding will be added to handle the data.  A sampling of the XML file
	'is shown in the question.
        
        'initialize variables
        Dim c As Integer = 0 ' array column
        Dim r As Integer = 0 ' array row
        Dim rowCount As Integer = 0
        Dim colCount As Integer = 0
        Dim tmpArr As String = ""
        Dim DTSDArrList(,) As String = New String(0, 0) {}
        Dim CC As Integer = 0
        Dim maxCC As Integer = 0
        'used to loop thru array for DailyTestSummaryData and HourlyOperatingData
        Dim tmp As String = ""
        Dim y As Integer = 0
        Dim z As Integer = 0
        
 
        'Select the file
        ofdGetFile.ShowDialog()
        strFileName = ofdGetFile.FileName
        lblFileName.Text = strFileName
        lblFileName.Refresh()
 
        'initialize and load xml for the DailyTestSummaryData
        Dim doc As XElement = XElement.Load(strFileName)
        Dim queryResults As List(Of XElement) = _
            (From x In doc.Descendants("DailyTestSummaryData") _
                     Select x).ToList()
 
        'determine rowcount for the array
        rowCount = (queryResults.Count) - 1
 
        'progress message
        lblFileName.Text = ("Done with List for DailyCalibrationData")
        lblFileName.Refresh()
 
        'Determine the column count for the array and for DailyCalibrationData
        'Not every DailyTestSummaryData has a DailyCalibrationData
        'Need to know max number of elements for DailyTestSummaryData to include 
        'DailyCalibrationData elements
        For Each element As XElement In queryResults
            For Each field As XElement In element.Descendants()
                CC = CC + 1
            Next
 
            If CC > maxCC Then
                maxCC = CC
            End If
            CC = 0
        Next
 
        'initialize the column count for the array
        colCount = maxCC - 1
 
        'reset the array dimensions
        ReDim DTSDArrList(rowCount, colCount)
 
        'load the array for the DailyTestSummaryData
        For Each element As XElement In queryResults
 
            'reset the column counter for the array
            c = 0
            For Each field As XElement In element.Descendants()
                
                DTSDArrList(r, c) = field.Value
                c = c + 1
             
            Next
 
            'increment the array row counter
            r = r + 1
 
        Next
 
        'progress message
        lblFileName.Text = ("Done adding to the array")
        lblFileName.Refresh()
        'MsgBox("Done adding to the array")
 
        ' loop thru the array
        For y = 0 To rowCount
            For z = 0 To colCount
                tmp = tmp & DTSDArrList(y, z) & "~"
            Next
            tmp = tmp & vbCrLf
        Next
        
        txtXMLInfo.Text = tmp
        txtXMLInfo.Refresh()
 
   
        'initialize and load xml for the HourlyOperatingData
        Dim doc2 As XElement = XElement.Load(strFileName)
        Dim queryResults2 As List(Of XElement) = _
            (From x In doc2.Descendants("HourlyOperatingData") _
                     Select x).ToList()
       
        'determine column count
        maxCC = 0
        CC = 0
        For Each element As XElement In queryResults2
            For Each field As XElement In element.Descendants()
                CC = CC + 1
            Next
            If CC > maxCC Then
                maxCC = CC
            End If
            CC = 0
        Next
 
        'initialize row and column count for resetting array dimensions
        rowCount = (queryResults2.Count) - 1
        colCount = maxCC - 1
 
        'resetting array dimensions
        ReDim DTSDArrList(rowCount, colCount)
 
        'progress message
        lblFileName.Text = ("Done with List for HourlyOperatingData")
        lblFileName.Refresh()
 
        'initialize row count
        r = 0
        
        For Each element As XElement In queryResults2
            'initialize column count
            c = 0
            For Each field As XElement In element.Descendants()
                strTemp = ""
                
                DTSDArrList(r, c) = field.Value
                c = c + 1
            Next
 
            'increment row count
            r = r + 1
 
        Next
 
 
        'progress message
        lblFileName.Text = ("Done adding to the array for HourlyOperatingData")
        lblFileName.Refresh()
 
        ' loop thru the array
        
        For y1 = 0 To 71
            'loops thru for 31 days * 24 hours in a day
            For z1 = 0 To maxCC - 1
                tmp1 = tmp1 & DTSDArrList(y1, z1) & "~"
            Next
            tmp1 = tmp1 & vbCrLf
 
        Next
        
        txtXMLInfo.Text = tmp1
        txtXMLInfo.Refresh()
 

Open in new window

0
 
ruffoneCommented:
It is posible to devide the points. Click the "Accept Multiple Solutions" button
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now