Question Create an effective XML Schema for Data Load dilemma?

[mond007]

Hi I am writing a XML Based Visual Studio application, whereby I need to import a simple matrix of 6 columns by 400 rows of data. The scenario is that the user puts in question number and the application looks up the question and display the 5 columns of the answer in the below format from the source data. I am having to trouble deciding how to structure the XML ?
(Note I will also have various other screen to maintain the source data file).

General Question Format to be shown on Final Screen
2.5 Autism is a lifelong developmental disability that affects how a person communicates with, and relates to,
other people. It also affects how they make sense of the world around them. (The National Autistic Society | - | autism | Asperger syndrome |)

2.7 Positives:

? Emergencies i.e. 999/112, GPS
? Easier to keep in touch
? Long Range communication
? Entertainment i.e. apps, camera, internet​

Negatives:

? Distraction
? Cost
? Crime
? Cyber Bullying​

5.18 Answer: D. All of these:

? Insurance companies can expect to pay out more in disaster claims.
? The fishing industry will suffer from low fish stocks.
? Some wine regions will no longer sustain grape crops​

5.30 Answer: C: Recycling. (Recycling is the process through which waste materials are recovered and reprocessed for use in new products).

View attachment xml_table.bmp

XML: OPTION 1 - Flat Record
<Question_Answer_Line>
<Question No>
[INDENT]<Answer Ref>
<Answer Formatting>    ? Notice this field governs the way the screen will format the answer. i.e. buttlets
<Answer Description>
<Answer Ref Section>
<Answer Ref Colour>
<Answer Image>
<Answer Hyperlink>[/INDENT]</Question_Answer_Line>

XML: OPTION 2 - Master/Child Record Type Definition
<Question_Answer_MASTER_QUESTION>
[INDENT]<Question No>
<Answer Ref>
<Answer Description>
<Answer Ref Section>
<Answer Ref Colour>
<Answer Image>
<Answer Hyperlink>
<Question_Answer_Child>  ? Notice Master/Child governs the way the screen will format the answer. i.e. buttlets
[INDENT]<Question No>
<Answer Ref>
<Answer Description>
[/INDENT]
<Question_Answer_Child>[/INDENT]
</Question_Answer_MASTER_QUESTION>             (or something like that).

The above XML OPTION 1 - is how I might proceed for the Schema definition but the alternative is to Master/Child type record definition? For the very experienced I would image you would make the latter option work but by putting the ?Answer Formatting? tag I can control how the output should be displayed by the program.

Can anyone offer any advise ?
Thanks in advance. Kuldip.

 

[IanRyder]

Hi,

One of the great things about .NET is that you can create some really complex XML files without actually knowing anything about XML and how it?s structured. Your solution could easily be done in a Database but if you need to do this with an XML file then you can use the .NET Dataset object to define the Structure that you need and then create the XML File Structure from that. Thereafter, you just read the XML file into your application, interact with the data as you need and then save the contents of your work back to the XML file.

So, to create an XML File structure using Master/Detail principles to do what you want, create a new project and have a play with this code:-

Private Sub btnCreateXMLStructure_Click(sender As System.Object, e As System.EventArgs) Handles btnCreateXMLStructure.Click
  Dim myQuizData As New DataSet
 
  With myQuizData
    .DataSetName = "myQuizData"
    .Tables.Add("Questions")
    .Tables.Add("Answers")
  End With
 
  With myQuizData.Tables("Questions")
    .Columns.Add("QuestionID", GetType(Integer))
    .Columns.Add("QuestionNo", GetType(String))
    .Columns.Add("Question", GetType(String))
  End With
 
  With myQuizData.Tables("Answers")
    .Columns.Add("QuestionID", GetType(Integer))
    .Columns.Add("AnswerRef", GetType(String))
    .Columns.Add("AnswerDescription", GetType(String))
    .Columns.Add("AnswerSection", GetType(String))
    .Columns.Add("AnswerColor", GetType(String))
    .Columns.Add("AnswerImage", GetType(String))
    .Columns.Add("AnswerHyperlink", GetType(String))
  End With
 
  myQuizData.Relations.Add("Questions_To_Answers", myQuizData.Tables(0).Columns(0), myQuizData.Tables(1).Columns(0))
 
  myQuizData.WriteXml("c:\temp\MyQuizData.xml", XmlWriteMode.WriteSchema)
  MsgBox("XML Quiz File Written with Schema Information!")
End Sub

Expand or change the columns in the data tables as you need and then run this to create the XML file structure. If you then look at the file its looks quite complex but is really quite logical when you understand what you are seeing. More importantly, it has all the information you need to persist the use of this structure in other projects that you want to use the file with.

So, create a new project and add a couple of DataGridViews. You can then read the XML File into a new Dataset using it?s ReadXML Method and then set the DataSources of those DataGridViews to the tables in the Dataset:-

Dim myQuizData As New DataSet
Dim bsQuestions As New BindingSource
Dim bsAnswers As New BindingSource
 
Private Sub btnLoadQuizData_Click(sender As System.Object, e As System.EventArgs) Handles btnLoadQuizData.Click
  myQuizData = New DataSet
  myQuizData.ReadXml("c:\temp\MyQuizData.xml", XmlReadMode.ReadSchema)
 
  With myQuizData.Tables(0).Columns(0)
    .AutoIncrement = True
    .AutoIncrementStep = 1
    If myQuizData.Tables(0).Rows.Count > 0 Then
      .AutoIncrementSeed = myQuizData.Tables(0).Rows.Cast(Of DataRow).Max(Function(x) CInt(x(0))) + 1
    Else
      .AutoIncrementSeed = 1
    End If
  End With
 
  With bsQuestions
    .DataSource = myQuizData
    .DataMember = "Questions"
  End With
  DataGridView1.DataSource = bsQuestions
  DataGridView1.Columns(0).Visible = False
 
  With bsAnswers
    .DataSource = bsQuestions
    .DataMember = "Questions_To_Answers"
  End With
  DataGridView2.DataSource = bsAnswers
  DataGridView2.Columns(0).Visible = False
End Sub

You can now interact with your Data using the DataGridViews, utilising Master/Detail (Parent/Child) principles, adding questions and answers as you need. You can then save your data just by calling the WriteXML method of the Dataset again.

Private Sub btnSaveQuizData_Click(sender As System.Object, e As System.EventArgs) Handles btnSaveQuizData.Click
  myQuizData.WriteXml("c:\temp\MyQuizData.xml", XmlWriteMode.WriteSchema)
  MsgBox("XML Quiz File Written with Schema Information!")
End Sub

Where you go from here is up to you but that should give you a good start with the XML file structure.

Hope that helps.

Cheers,

Ian

 

[mond007]

Hi Ian, thanks indeed for the effort gone into your answer. I have to say on the face of it, it seems beyond my capabilities and feel a little apprehensive as you may understand. Also, possibly a bit of an overkill, I am not sure as I am looking at your suggestion now but thought I would explain further.

Incidentally, I am using Visual Studio 2012 by the way. I have also added a screen shot too.


I have already written the Visual Studio Application and works fine but I have hard-coded the questions and answers into the code. i.e the following lines of code loads the data into an array and is repeated several hundred times :

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call populate_array_items(0, "1.6", "-", "Parts of your body covered by underwear are private. No one should ask to see, or touch them. Sometimes doctors, nurses or family members might have to. But they should always explain why, and ask if it?s OK first. No one should ask you to touch or look at parts of their body that are covered by underwear.", "Category 1- Be Safe", "Red", "Image 1.6", "http://www.nspcc.org.uk/help-and-advice/for-parents/keeping-your-child-safe/the-underwear-rule/the-underwear-rule_wda97016.html")
        Call populate_array_items(1, "1.18", "-", "Young children are particularly at risk of catching and spreading infections. Wash your hands after using the toilet, before eating and especially after coughing or sneezing. http://www.bupa.co.uk", "Category 1- Be Safe", "Red", "Image 1.8", "http://www.getsurrey.co.uk/news/local-news/children-scrub-up-hand-washing-4721908")
        Call populate_array_items(2, "2.7", "-", "Positives: 0 Emergencies i.e. 999/112, GPS 0 Easier to keep in touch 0 Long Range communication 0 Entertainment i.e. apps, camera, internet Negatives: 0 Distraction 0 Cost 0 Crime 0 Cyber Bullying", "Category 2- Be Smart", "Purple", "Image 2.7", "")

Etc etc.. times several hundred rows?. (changing only the array number)?.

When a user types in the Quesion No my programme looks into the array for he answer and display however many lines match the Question No. If you see what I mean.

Everything past the this works. The issue is that these questions keep changing and hence I have an excel file with all the data in there. It was my intention to export the data from the Excel Data file to an XML format.

Then I would load the XML Data into the array using a loop at the Form_Load point and carry on as usual.
I have not got any XML file as yet this is the point at which I am stuck. i.e. How to create the XML from my Excel data File.

I was hoping to use the following without getting to involved with datasets, Datagrids etc, I also never need to write any data back, its only a display only from the questions.

<Question_Answer_Line>
[INDENT]<Question No>
<Answer Ref>
<Answer Formatting> 
<Answer Description>
<Answer Ref Section>
<Answer Ref Colour>
<Answer Image>
<Answer Hyperlink>[/INDENT]</Question_Answer_Line>

I was hoping to use a simple XML Schema as shown in (OPTION 10 above and create an XML file and then simply read in the data into the array. If the Data changes I would replace the XML Data file and no program changes required just different Data.

The <Answer Formatting> would be a indicator within my program to insert a buttlet point at the start of a paragraph.
I could almost use CSV file but the questions contain commas etc.

Let me know what you think.? or am I going about this in the wrong way?.

Thanks in Advance. Kuldip.

 

[IanRyder]

Hi,

First of all, my apologies if you feel that I may have overwhelmed you a little. If you take some time to consider what I posted you may find it useful after all since your first issue, which you have already recognised for yourself, is that you have hard coded all the questions and answers and that is never a good idea because of the issues you are facing now.

In essence you can use any file or database structure you want to maintain and store your data but you have to be able to successfully read that information into your .NET project and how you do that is up to you. What I showed above was a way of maintaining the integrity of an XML file without the need to know anything about XML file creation. This could be a hidden admin option in your project but again that’s up to you.

Regardless of where the data comes from, you can then use your data in any way that you want in your project and creating what you have shown from a Dataset would be very easy.

Not sure what other advice to give really other than if you do want to manually create an XML file then have a look around the internet since there are loads of examples out there.

Good luck.

Cheers,

Ian

 

[mond007]

Hi
Quite the contrary, in fact I have been a developer most of my life and don?t mind a challenge at all. It?s just that I am proficient at VBA and other languages create highly complex Dashboards/Financial summaries but only recently moved to Visual Studio and loving it but like most things just require some learning curve time. As long as I understand it I am not intimidated by it.

Hard coding Questions/Answers was great for the proto-type by making sure the entire VB application looks and feels as I need it. However, I still would have needed a method to manage the data w.r.t. a CMS system (Admin Option you mention). This is where I found your suggestion to be invaluable. Whether I use the flat/single record or Master/Detail record can be considered. Using the Master Detail Record means I have to redesign my main application :-(, which I am overly happy about spending quite a lot of time to redesign when I currently have it working. Anyway, that?s another story.

I have to say I have not had a lot to do with .NET Dataset object method but I am happy to try.
I know the area of Visual Studio is vast! and fun.!

Truthfully, when you say have a play with the code I am happy to create a project with DataGridViews subject to learning all about them. I would appreciate some basic steps if you have time to list them out in bullet point.

I presume it will go something like :

1) Create a Form and insert a DataGridView with a format such that it is 6 columns by however many rows for the data.
2) ?? I was puzzled by you said ?add a couple of DataGridViews? as there is only one basic table of data so one DataGridView.
3) Create the following buttons (with your code behind them) btnCreateXMLStructure, btnSaveQuizData, and btnLoadQuizData at the bottom of the form.
4) Click btnCreateXMLStructure which I presume create the XML Structure/Schema.
5) Enter some data
6) Click btnSaveQuizData to save the data just entered and I am sure there will be a good XML File.
7) Try to reload the data by clicking btnLoadQuizData hopefully ending up with what you saved.

I guess the way to proceed maybe to use my method (i.e. Flat file with simple XML Schema) to display the Question and Answers and use your method for the Questions and Answer CMS system.

Let?s see after I try and code up your suggested method.

Again look forward to your views as they are very much appreciated indeed.
Kuldip.

 

[IanRyder]

Hi,

I guess the way to proceed maybe to use my method (i.e. Flat file with simple XML Schema) to display the Question and Answers and use your method for the Questions and Answer CMS system.

OK, go with what you feel you need to do at the moment but when you are ready lets make things better.

When Ready:-

Quite the contrary, in fact I have been a developer most of my life and don?t mind a challenge at all. It?s just that I am proficient at VBA and other languages

Good for you and you are in exactly the same position I was a couple of years ago. However, if you are an experienced developer, of whatever sort, then you should know better than to hard code "changeable" information into an application but lets not get bogged down with that debate, lets more forward into the new age of development.

Firstly, you will find that reading and writing to databases has become a lot easier and more efficient with ADO.NET as apposed to using either DAO or ADODB that you may have used within VBA (in my view anyway) and so if you want to get a crash course on using ADO.NET then have a look at the Blog Link in the signature of jmcilhinney. Just look for one of his posts on this Forum and go from there.

The next thing to work through are your questions One Post at a time. So, the first question:-

1) Create a Form and insert a DataGridView with a format such that it is 6 columns by however many rows for the data.

No! This is exactly what a correctly designed XML file will do for you after you have created that XML file.

The first thing you need to do is use the First Block of code I posted to Design the Structure you want in your Data Tables, being both the Questions Table and the Answers Table, knowing that the QuestionID field is the Mandatory Relational Link between the theses two tables.

So, expand that first piece of code to your requirements, adding fields where you need, and run it. Once done post back to this thread and we will go to the next stage.

Cheers,

Ian

 

[mond007]

Hi,
Like most keen developers you know I am going to dive in .

I have done the creation of the XML. File.

I am pondering how you propose to deal with the following structure....

2.7 Positives:

• Emergencies i.e. 999/112, GPS
• Easier to keep in touch
• Long Range communication
• Entertainment i.e. apps, camera, internet

Negatives:

• Distraction
• Cost
• Crime
• Cyber Bullying

For the above should we not add the :
<Answer Formatting> so as to tell the program when to insert a "bullet" ? otherwise there is no way for the code to know when to insert a bullet.

I have to say that was a breeze... and stand in awe of your skills.... and look forward to completing this with joy... how thrilling.... talk about being passionate about project.

I am trying looking into how to create the DataGridView ....
Thanks Kuldip.

 

[mond007]

Hi,

I think I am a bit further than I thought I was going to be. I think I am getting the beauty of this solution. The fog is clearing. The XML knowledge seamlessly disappears into the mist and you almost don't need to worry about the XML code, you are indirectly shielded from this.

I managed to get this far now. I created the DataGridViews with the aid of a few youtube videos. I think even the bullet point problem seems to have resolved itself as it retains any input formatting.

I am not entirely sure of how to link the DataGridView to the back end XML but it works either way.

What's the next step, so that when the user enter 2.7 I need to search for that Question and pull out the Child RecordSet right ?

See Image.

 

[IanRyder]

The XML knowledge seamlessly disappears into the mist and you almost don't need to worry about the XML code, you are indirectly shielded from this.

You got it and it looks like you have also nearly completed your editing system too. Congratulations.

As to your final question regarding getting the Parent and Child information then the first thing to do is to identify the Parent row in the Questions table that you are working with. Once done, you can then call the GetChildRows Method of the Parent Data Row, passing the name of the Relationship to that method, to return a List of the Child Rows associated with the Parent row.

That's it, you can now do anything you want with the Data in the Structure that you have now created.

If you have any further questions then please do start another Thread with that new question since I think we can now say that we have covered off the XML portion of this question.

Hope that helps and good luck,

Cheers,

Ian

 

[mond007]

Hi Thank you, it was not as daunting as I thought it might be.

1) A couple of final quickies, I am unclear how, when I enter the Question No 2.7, I search the XML File for the correct Parent Answer so that I can display this along with getting the Child Records. (i.e. identify the Parent row). I think I could handle using the "GetChildRows" part. Or is the entire file loaded into the Application ?.

1a) The rest is essentially formatting the returned record to display in my "Answer box" right ? programming as usual.
I don't think I need to load the XML data into my array anymore, instead just search for the parent and get the child records and job done.

2) Can the XML deal with things like "Bullet", different font colour, or other formatting, if so how would one enter these in the source data within the DataGridView ?. All I did was cut/paste it from Word and it seemed to inhereit the source formmatting, seems too simple.

Thanks in Advance. Kuldip.
ps I now feel confident to redraft my entire solution based on my new found knowledge. A humble thank you to you sir.

 

[IanRyder]

Hi,

Lets deal with the easy questions first:-

1a) Yes, to both points

2) You must remember that an XML file is still just a flat text file, albeit a structured one at that, so it can only hold textual information as apposed to some complex type that you might use in your application. As an example, for a colour you could save the 32Bit Integer value of the Colour in the file and then convert that to a colour in your project using Color.FromArgb or you could save the Name of the colour in the file and then convert that to a colour in your project using Color.FromName.

1) The first thing to remember when searching for the Parent Question record is that you are NOT searching the XML file. The XML file is your data repository which you read into a Dataset at the start of your project and then save to whenever you make changes to that data.

So, it is the Questions Table in the Dataset that you need to be searching for the Parent Record. Here is a quick example of searching that Data Table for a parent record and the getting the Child records from the Answers table:-

Private Sub btnSearchForQuestion_Click(sender As Object, e As EventArgs) Handles btnSearchForQuestion.Click
  'Search the Questions Table for the one that you want to find
  Dim foundParentRow As DataRow = myQuizData.Tables("Questions").Select(String.Format("QuestionNo ='{0}'", TextBox1.Text)).FirstOrDefault
 
  'If the Question is found then get the Answers from the Child Table
  If Not foundParentRow Is Nothing Then
    Dim childRows As List(Of DataRow) = foundParentRow.GetChildRows("Questions_To_Answers").ToList
    For Each childRow In childRows
      MsgBox(childRow("AnswerDescription").ToString)
    Next
  Else
    MsgBox("Question Not Found!")
  End If
End Sub

Hope that helps.

Cheers,

Ian

 

[mond007]

Hi

Well I managed to get the Retrieve of the Question No working and I have to admit if you hadn't of shown me this way, I would have "really" struggled indeed.

Whilst being very happy with the new regime, this has thrown my most of my existing code to pot and I will have to rethink most of the code again. No problemo.

Previously I had a scrollable ListBox which I built up the stringed ANSWER and hence displayed it quite easy. I am now having to think of a way to display the ANSWER BOX in a bit more clever way.

I am currently looking for a way to display the ANSWER BOX by the use of a DataGridView, ListView or SplitContainer as per the image and load it up but not very pretty as it has the ugly asterisk and left hand column blocks.

View attachment new_screen.bmp

Finally, I plan to play with the structure because essentially there is only 1 image and hyperlink per question so this can go into the "Questions" table rather then be repeated in the Child Records.
Now that I understand the code, these are the optimisations I need to have a think about.

Plan to get this working over the week end. Even the Maintenance Screen needs to be redone using proper GUI rather than using DataGridView which is unrefined.

Cheers Kuldip.

 

[mond007]

Hi
I managed to get this whole application strcuted very well and working. A great big thank you to Ian Ryder for his assitance and pointers.

The final structure is as follows :

        With CareTrainQuestionAnswerData.Tables("Questions")
            .Columns.Add("QuestionID", GetType(Integer))
            .Columns.Add("QuestionNo", GetType(String))
            .Columns.Add("Question", GetType(String))
            .Columns.Add("AnswerSection", GetType(String))
            .Columns.Add("AnswerSectionColor", GetType(String))
            .Columns.Add("AnswerImage", GetType(String))
            .Columns.Add("AnswerHyperlink", GetType(String))
        End With

        With CareTrainQuestionAnswerData.Tables("Answers")
            .Columns.Add("QuestionID", GetType(Integer))
            .Columns.Add("AnswerRef", GetType(String))
            .Columns.Add("AnswerFormat", GetType(String))
            .Columns.Add("AnswerColour", GetType(String))
            .Columns.Add("AnswerDescription", GetType(String))
        End With

Only the Answers are repeated all the rest is linked to the Parent, hence no need to bein the child record.

I also ended up using a RichTextBox for all the formatting :
RichTextBox.SelectionBullet Property (System.Windows.Forms)

Once again thank you all. Regards Kuldip.