How to generate dataset form XML file 1

[taree]

What I would like to do is to copy the xml file and generate dataset and store it to the database.
1- Do I need to have a storage place for the customers to store their XML file or can I read the xml file and generate the dataset?
2- I am thinking to write insert statement to copy the values from the dataset to the database
3- How a do a validation to check the xml file can is in the right format.

I really appreciate if you can provide me some kind of example especailly 1

thank you

 

[ca8msm]

If you mean you want the user to upload the file, then yes just create a FileUpload control and then read it into your DataSet using the ReadXML method e.g.

        Dim ds As New DataSet        
        ds.ReadXml(FileUpload1.PostedFile.InputStream)

Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[jbenson001]

To verify that the XML is in the correct format, you can use an XSD file for that.

 

[taree]

Thank you guys for the reply. my next question is how can you compare to xml schemas. I would like to do the comparision between the two before I insert the values to the database.

 

[taree]

Here is what I have done so far. My intention is to The schema is given by the third party. The user will upload the xml file and I will validate XML document with XSD schema. I have a file upload for the user to upload the xml file. my question is how can I read this xml and validate it with the xsd. once the validation is passed then i will create dataset and save the file in the database. if it fails the error message will be displayed for the user to correct their xml file.

Here is what I have done so far.

Imports System.IO
Imports System.Xml
Imports System.Data
Imports System.Xml.Schema
Imports System.Collections
Imports System.Data.OracleClient
Imports System.Configuration.ConfigurationManager
Partial Class Pyroll
    Inherits System.Web.UI.Page

    Private XSDPath As String
    Private Reader As XmlTextReader
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        If (FileUpload1.HasFile) Then

            XSDPath = "c:\address.xsd"
            ValidatingProcess("c:\address.xml", XSDPath)

            Me.Reader = New XmlTextReader(FileUpload1.PostedFile.InputStream)

           ' Dim fileName As String = FileUpload1.FileName
            'Dim ds As New DataSet
            'ds.ReadXml(FileUpload1.PostedFile.InputStream)
            'ds.ReadXmlSchema("c:\address.xsd")

        Else

        End If

    End Sub

    Private Sub ValidatingProcess(ByVal XMLPath As String, ByVal XSDPath As String)

        Try

            Me.Reader = New XmlTextReader(XMLPath)
            Dim SR As New StreamReader(Me.XSDPath)

            Dim Schema As New XmlSchema()
            Schema = XmlSchema.Read(SR, New ValidationEventHandler(AddressOf ReaderSettings_ValidationEventHandler))

            Dim ReaderSettings As New XmlReaderSettings()
            ReaderSettings.ValidationType = ValidationType.Schema
            ReaderSettings.Schemas.Add(Schema)
            AddHandler ReaderSettings.ValidationEventHandler, New ValidationEventHandler(AddressOf ReaderSettings_ValidationEventHandler)
            Dim objXmlReader As XmlReader = XmlReader.Create(Reader, ReaderSettings)

            While objXmlReader.Read()

            End While

        Catch AccessEx As UnauthorizedAccessException
            Throw AccessEx

        Catch Ex As Exception
            Throw Ex
        End Try

    End Sub
    Private Sub ReaderSettings_ValidationEventHandler(ByVal sender As Object, ByVal args As ValidationEventArgs)

        Dim strTemp As String
        strTemp = "Line: " & Me.Reader.LineNumber & " - Position: " & Me.Reader.LinePosition & " - " & args.Message

    End Sub
    
End Class

 

[jbenson001]

Have you tried Google?

http://www.codeguru.com/csharp/csharp/cs_data/xml/article.php/c6737

 

[taree]

The code works. My question is I do not know from where the user is going to upload the file during the validation proces.

I tried this

Dim XmlTextReader As reader
           Me.reader = New XmlTextReader(FileUpload1.PostedFile.InputStream)




 Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        If (FileUpload1.HasFile) Then

                       Me.Reader = New XmlTextReader(FileUpload1.PostedFile.InputStream)


            XSDPath = "c:\address.xsd"
            ValidatingProcess("c:\address.xml", XSDPath)


           ' Dim fileName As String = FileUpload1.FileName
           ' Dim ds As New DataSet
           ' ds.ReadXml(FileUpload1.PostedFile.InputStream)
           ' ds.ReadXmlSchema("c:\address.xsd")

        Else

        End If

    End Sub

 

[taree]

Hi all,

Everything works fine except when there more than one error it only shows the first one. can someone point to me how I can fix this. Thank you

Imports System.IO
Imports System.Xml
Imports System.Data
Imports System.Xml.Schema
Imports System.Collections
Imports System.Data.OracleClient
Imports System.Configuration.ConfigurationManager
Partial Class Pyroll
    Inherits System.Web.UI.Page
    Dim XMLPath As String
    Dim XSDPath As String
    Dim Reader As XmlTextReader
    Dim Results As New ArrayList()
    

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        If (FileUpload1.HasFile) Then

            XSDPath = "F:\XSDFile\address.xsd"
            ValidatingProcess()

        Else
            lblError.Text = "Please upload a valid xml file"
        End If

    End Sub
    Public Function ValidateIt() As ArrayList
        ValidatingProcess()
        Return Me.Results
    End Function

    Private Function ValidatingProcess() As Boolean

        Try

            Dim readFile As StreamReader = New StreamReader(FileUpload1.PostedFile.InputStream)


            Reader = New XmlTextReader(readFile)
            Dim SR As StreamReader = New StreamReader(Me.XSDPath)

            Dim Schema As New XmlSchema()
            Schema = XmlSchema.Read(SR, New ValidationEventHandler(AddressOf ReaderSettings_ValidationEventHandler))

            Dim ReaderSettings As New XmlReaderSettings()
            ReaderSettings.ValidationType = ValidationType.Schema
            ReaderSettings.IgnoreWhitespace = True
            ReaderSettings.Schemas.Add(Schema)
            AddHandler ReaderSettings.ValidationEventHandler, New ValidationEventHandler(AddressOf ReaderSettings_ValidationEventHandler)
            Dim objXmlReader As XmlReader = XmlReader.Create(Reader, ReaderSettings)


            While objXmlReader.Read()

            End While

        Catch AccessEx As UnauthorizedAccessException
            Throw AccessEx

        Catch Ex As Exception

            Throw Ex

        End Try

        Return True

    End Function
    Private Sub ReaderSettings_ValidationEventHandler(ByVal sender As Object, ByVal args As ValidationEventArgs)
        Dim strTemp As String
        strTemp = "Line: " & Convert.ToString(Me.Reader.LineNumber) & " - Position: " & Convert.ToString(Me.Reader.LinePosition) & " - " & Convert.ToString(args.Message)
        Me.Results.Add(strTemp)
    End Sub

  
End Class

 

[jmeckley]

first remove the try/catch block, you are just re-throwing the errors so this is useless.
2nd in the validation handler collect the error's in a list a simple object to hold the line number, position and message would do.
3rd. after validation completes check the list of errors. if any exists, display warnings to the user. otherwise parse the xml and save. here is some pseudo code

private XmlReader reader;
private readonly List<XmlReadError > errors = new List<XmlReadError >();

private void ImportXml(...)
{
   var dataset = new DataSet();
   using(var schema = new XmlSchema(path to schema))
   try
   {
      schema.Validate += CollectErrors;
      using(reader = new XmlReader(Schema, uploaded file))
      {
          transform xml to dataset
      }
   }
   finally
   {
       schema.Validate -= CollectErrors;
   }

   if(errors.Count > 0)
   {
       display errors
       return;
   }
   save dataset;
   
}

private void CollectErrors(...)
{
   errors.Add(new XmlReadError {
                  Line = reader.LineNumber,
                  Position = reader.Position,
                  Message = e.Message
              });
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[taree]

Jason, thank you for the pseudo code. I used your suggestion and try to modify the code and I am still getting only one error for validation. I know for sure I have more than one validation problem.

<address xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="address.xsd">
<name>John Smith</name>
<street>109 Abbey Close</street>
<county> Dakota </county>
<city>Hayes</city>
<country> UK</country>
<zipcode> UK</zipcode>
</address>


<xs:schema xmlns:xs="

http://www.w3.org/2001/XMLSchema">

<xs:element name="address">
<xs:complexType>
<xs:sequence>
<xs:element name="name" type="xs:string"/>
<xs:element name="street" type="xs:string"/>
<xs:element name="city" type="xs:string"/>
<xs:element name="country" type="xs:string" />

</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Imports System.IO
Imports System.Xml
Imports System.Data
Imports System.Xml.Schema
Imports System.Collections
Imports System.Data.OracleClient
Imports System.Configuration.ConfigurationManager
Partial Class Pyroll
    Inherits System.Web.UI.Page
    Dim XMLPath As String
    Dim XSDPath As String
    Dim Reader As XmlTextReader
    Dim ErrResult As New ArrayList()
    Dim readFile As StreamReader
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        If (FileUpload1.HasFile) Then
            XSDPath = "F:\XSDFile\address.xsd"
            ValidatingProcess()
        Else
            lblError.Text = "Please upload a valid xml file"
        End If
    End Sub
    Public Function ValidateIt() As ArrayList
        Return Me.ErrResult
    End Function
    Private Function ValidatingProcess() As Boolean
     
        Dim ds As DataSet = New DataSet

        Dim readFile As StreamReader = New StreamReader(FileUpload1.PostedFile.InputStream)
        Reader = New XmlTextReader(readFile)
        Dim SR As StreamReader = New StreamReader(Me.XSDPath)
        Dim ReaderSettings As New XmlReaderSettings()
        Try
            Dim Schema As New XmlSchema()
            Schema = XmlSchema.Read(SR, New ValidationEventHandler(AddressOf ReaderSettings_ValidationEventHandler))
            ReaderSettings.ValidationType = ValidationType.Schema
            ReaderSettings.IgnoreWhitespace = True
            ReaderSettings.Schemas.Add(Schema)

            AddHandler ReaderSettings.ValidationEventHandler, New ValidationEventHandler(AddressOf ReaderSettings_ValidationEventHandler)
            Dim objXmlReader As XmlReader = XmlReader.Create(Reader, ReaderSettings)
            While objXmlReader.Read()

            End While
        Finally
            AddHandler ReaderSettings.ValidationEventHandler, New ValidationEventHandler(AddressOf ReaderSettings_ValidationEventHandler)
        End Try

        If ValidateIt.Count > 0 Then
            Return True
        End If
        ds.ReadXml(Reader)
    End Function
    Private Sub ReaderSettings_ValidationEventHandler(ByVal sender As Object, ByVal args As ValidationEventArgs)
        Dim strTemp As String
        strTemp = "Line: " & Convert.ToString(Me.Reader.LineNumber) & " - Position: " & Convert.ToString(Me.Reader.LinePosition) & " -  Error Message: " & Convert.ToString(args.Message)
        Me.ErrResult.Add(strTemp)
    End Sub
   
End Class

 

[jmeckley]

it may be that the reader will fail on the first error and stop processing, in which case you will need to use another method to validate the data.

in looking at your code you are adding the validation handler twice. once in the try block and again in the finally block. the finally block should remove the handler.

I also don't see value in reading the xml document, if you are not going to do anything with it. I would replace [tt]While objXmlReader.Read()...End While[/tt] with [tt]ds.ReadXml(Reader)[/tt]. this way you only read the xml document once. if there are errors, don't process the dataset. if there are no errors, persist changes to the dataset.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[taree]

Jason I can not thank you enough for your help on this. Hopefully, I will figure it out soon. the reason that I have
this line is to do the validation. I am not sure how the validation is going to take place unless I run this code. It is my understanding that the validation is taking place at the same time your read the file. please correct me If I am mistaken. I really appreciate if you show me how this can be corrected.


While objXmlReader.Read()

End While

 

[jmeckley]

yes validation is done while the file it being read. what do you happens when you call [tt]ds.ReadXml(Reader)[/tt]?

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[taree]

what do you happens when you call ds.ReadXml(Reader)?
It just create dataset. It does not validat the xml file

 

[jmeckley]

It just create dataset

correct, but how is the dataset created if it doesn't read the file? if validation is preformed while the file is read, then calling ds.ReadXml(Reader) will validate the xml document as the dataset is created.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[taree]

thank you Jason for being patient with me.

I replace the code like this and run it. I donot see any error message.

            ds.ReadXml(Reader)
            'While objXmlReader.Read()

            'End While

 

[jmeckley]

are there any errors in the document? If there are and it's no validating than maybe (and this is total conjecture) the dataset is overriding the schema validation you configured previously. if that's the case see if there are any options on the dataset to validate the schema. i don't think there would be, but it's worth a shot. if not, then you're original approach (reader.Reader()) would be the way to go.

one final thought... xml is hierarchical while datasets are tabular. the xml can be structured so that a dataset can read it, but it doesn't have to be. if the xml data is not compatible with a dataset you will need to build your own conversion/mapping to move the data from xml to a dataset.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[taree]

Thank you Jason for all the info. you help is really appreciated. I think I am going to go with my original approach (reader.Reader())

one more question. do you see anything in my code that you might think is wrong or be corrected to display the entire error message. thank you again.

 Private Function ValidatingProcess() As Boolean
     
        Dim ds As DataSet = New DataSet

        Dim readFile As StreamReader = New StreamReader(FileUpload1.PostedFile.InputStream)
        Reader = New XmlTextReader(readFile)
        Dim SR As StreamReader = New StreamReader(Me.XSDPath)
        Dim ReaderSettings As New XmlReaderSettings()
        Try
            Dim Schema As New XmlSchema()
            Schema = XmlSchema.Read(SR, New ValidationEventHandler(AddressOf ReaderSettings_ValidationEventHandler))
            ReaderSettings.ValidationType = ValidationType.Schema
            ReaderSettings.IgnoreWhitespace = True
            ReaderSettings.Schemas.Add(Schema)

            AddHandler ReaderSettings.ValidationEventHandler, New ValidationEventHandler(AddressOf ReaderSettings_ValidationEventHandler)
            Dim objXmlReader As XmlReader = XmlReader.Create(Reader, ReaderSettings)
            While objXmlReader.Read()

            End While
        Finally
            AddHandler ReaderSettings.ValidationEventHandler, New ValidationEventHandler(AddressOf ReaderSettings_ValidationEventHandler)
        End Try

        If ValidateIt.Count > 0 Then
            Return True
        End If
        ds.ReadXml(Reader)
    End Function

 

[jmeckley]

2 things I would consider:

1. it looks like you are registering the ReaderSettings_ValidationEventHandler 3 times. once in the constructor, again in the try block and third time in the finally block. I would keep the constructor argument, drop AddHandler in the try block and change AddHandler to RemoveHandler in the finally block. I'm a C# dev, the VB syntax may be different than RemoveHandler.

2. The function is named ValidatingProcess which returns whether the xml is valid or not. in the function you validate the xml, but you also populate the dataset. I would separate these into unqiue functions. ValidateXml and PopulateDataSet. This touches on the concepts of command and query (not databases) separation and creating side effect free functions.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259