asked on

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>

SOLUTION

aboredman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

abcserve

ASKER

I will review the informatin and advise. Thank you.

abcserve

ASKER

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

abcserve

ASKER

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.

SOLUTION

Ruffone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

abcserve

ASKER

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.

abcserve

ASKER

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.

ASKER CERTIFIED SOLUTION

abcserve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ruffone

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