parsing flat xml file using asp.net 2.0

mikecarmody · February 6th, 2008, 10:54 AM

I'm trying to read in a flat xml file into an asp.net application,
i then want to export the contents of the file into a database table in sql server but i'm having problems reading in the xml file.
The xml file has only one level, it looks like the following with data in it:
<?xml version="1.0" encoding="UTF-8" ?>
- <form1>
  <ProjectTitle>mike' s project title</ProjectTitle>
  <ProjectOutline>this is a brief project outline</ProjectOutline>
  <ProjectNo>124321</ProjectNo>
  <ProjectLeader>joe bloggs</ProjectLeader>
  <ProjectDuration>12.00000000</ProjectDuration>
  <EndDate>2008-04-10</EndDate>
  <StartDate>2008-02-04</StartDate>
  <drpOrganisation>Dunmore Ltd</drpOrganisation>
  <drpPartner>Fishermans Co-op</drpPartner>
  <FDDteam>Environment Group 9</FDDteam>
</form1>

In my .net application I'm using te following code:

Dim objXMLReader As XmlTextReader = New XmlTextReader(sDir & "\" & File.Name) 'open up the xml file for reading

            Do While objXMLReader.Read
                strNode = ""
                For intI = 1 To objXMLReader.Depth
                    strNode &= "-"
                Next
                strNode &= "Name:" & objXMLReader.Name & " "
                strNode &= "Nodetype:" & objXMLReader.NodeType.ToString & " "
                If objXMLReader.HasValue Then
                    strNode &= "Value:" & objXMLReader.Value
                End If
                Response.Write(strNode & "<br/>")
            Loop
            objXMLReader.Close()

The problem is it doesn't traverse properly through the fields.
I think the problem is because my xml file doesn't have 'levels' in it.
thanks

samjudson · February 6th, 2008, 11:40 AM

I don't see a problem with the code.

What exactly are you expecting, and what are you seeing?

/- Sam Judson : Wrox Technical Editor -/

Martin Honnen · February 6th, 2008, 12:09 PM

Well Xml(Text)Reader used with only the Read method is kind of a low level API that gives you a lot of different nodes, you will get element nodes, text nodes, and end element nodes the way you have set up your code.
Below I show some VB.NET 2005 sample that reads out the text contents of all elements at Depth 1, that might more likely what you want to achieve:

Code:

        Using reader As XmlReader = XmlReader.Create("..\..\XMLFile1.xml")
            While reader.Read()
                If reader.NodeType = XmlNodeType.Element And reader.Depth = 1 Then
                    Console.WriteLine("Element with name ""{0}"" has contents ""{1}""", reader.Name, reader.ReadString())
                End If
            End While
        End Using

Output with your sample XML is as follows:

Code:

Element with name "ProjectTitle" has contents "mike' s project title"
Element with name "ProjectOutline" has contents "this is a brief project outline"
Element with name "ProjectNo" has contents "124321"
Element with name "ProjectLeader" has contents "joe bloggs"
Element with name "ProjectDuration" has contents "12.00000000"
Element with name "EndDate" has contents "2008-04-10"
Element with name "StartDate" has contents "2008-02-04"
Element with name "drpOrganisation" has contents "Dunmore Ltd"
Element with name "drpPartner" has contents "Fishermans Co-op"
Element with name "FDDteam" has contents "Environment Group 9"

My sample writes to the console, in an ASP.NET application you might want to output the data in a control or send it directly to the Response.

mikecarmody · February 11th, 2008, 06:51 AM

martin,
that's exactly what I was looking for.
thanks for help.

tamal · April 27th, 2008, 10:28 PM

Hi, I need to import data from a xml file to sql database using asp.net. Have you figured out a way to do this? Please let me know.

Quote:

quote:Originally posted by mikecarmody
I'm trying to read in a flat xml file into an asp.net application,
i then want to export the contents of the file into a database table in sql server but i'm having problems reading in the xml file.
The xml file has only one level, it looks like the following with data in it:
<?xml version="1.0" encoding="UTF-8" ?>
- <form1>
  <ProjectTitle>mike' s project title</ProjectTitle>
  <ProjectOutline>this is a brief project outline</ProjectOutline>
  <ProjectNo>124321</ProjectNo>
  <ProjectLeader>joe bloggs</ProjectLeader>
  <ProjectDuration>12.00000000</ProjectDuration>
  <EndDate>2008-04-10</EndDate>
  <StartDate>2008-02-04</StartDate>
  <drpOrganisation>Dunmore Ltd</drpOrganisation>
  <drpPartner>Fishermans Co-op</drpPartner>
  <FDDteam>Environment Group 9</FDDteam>
</form1>

In my .net application I'm using te following code:

Dim objXMLReader As XmlTextReader = New XmlTextReader(sDir & "\" & File.Name) 'open up the xml file for reading

            Do While objXMLReader.Read
                strNode = ""
                For intI = 1 To objXMLReader.Depth
                    strNode &= "-"
                Next
                strNode &= "Name:" & objXMLReader.Name & " "
                strNode &= "Nodetype:" & objXMLReader.NodeType.ToString & " "
                If objXMLReader.HasValue Then
                    strNode &= "Value:" & objXMLReader.Value
                End If
                Response.Write(strNode & "<br/>")
            Loop
            objXMLReader.Close()

The problem is it doesn't traverse properly through the fields.
I think the problem is because my xml file doesn't have 'levels' in it.
thanks

joefawcett · April 28th, 2008, 02:55 AM

Which part of this are you struggling with. You have an example above of reading a file, alternatively you could use XmlDocument if the size is not large. You can then use SQL Server's XML features to import or just create standard SQL by parsing the XML. Can you describe the scenario in more detail?

--

Joe (Microsoft MVP - XML)