Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  Transact-SQL (2008)  Best XML schema to use

Author  Topic  meenxip Starting Member 4 Posts Posted - 2011-08-31 : 08:22:02 Dear All, I have requirement as below. I want the table structure dynamic. E.G. For person's personal data table the questions (fields) should be dynamice as the admin wants. I am thinking of making one table where the set of questions (fields) shoud be stored in XML. And there would be one table where the answers i.e. users response would be stored in relation to the XML i have stored in Questions table. XML example '<Education> <Question id="1" QuestionType="SimpleText" IsRequired="True" QstText="School Name" Options="" ><Answer>test</Answer></Question> <Question id="2" QuestionType="SimpleText" IsRequired="True" QstText="Degree" Options="" ><Answer>test1</Answer></Question> <Question id="3" QuestionType="SingleSelection" IsRequired="True" QstText="PassingYear" Options="2001,2002,2003,2004,2006,2006,2007,2008" ><Answer>test2</Answer></Question> <Question id="4" QuestionType="SingleSelection" IsRequired="False" QstText="Verify" Options="True,False" ><Answer>test3</Answer></Question> <Question id="5" QuestionType="MultiText" IsRequired="False" QstText="Description" Options="" ><Answer>test4</Answer></Question> </Education>' as you can note, that the xml stores which type of question is, what are the options and all. my requirement is, What should be the best XML schema so that a. it can be easy for me to get the question types and its attributes so that i can easily build controls dynamically on asp.net page. b. in Question table, if the xml gets updated, then there would be new record inserted, updating the current record as inactive. what i requrie is when xml gets updated, all the data entered (answer) by the user should be updated as it is in the answer xml with the newly updated xml structure so that user doesnt loose the entered data. Let me know what schema structure would be best. Thanks Meenxip Thanks Meenxip