I am developing a process where somewhat arbitrary questions can be entered into a database table. These questions have attributes such as datatype, control type, etc; to enables a UI to automotically build the appropriate screen for the questions so that a user may provide answers. All of this data is managed in a middle XML process. An admin can assign questions to documents/pages in a somewhat arbitrary manner and sequence.

Once I have a answers to the questions, I want to be able to get the data back into a relational DB model.

Has anyone done any mapping to entities or is there any sample code for something liek this.

In my question table I would like to also be able to identify the Entity and Field and any other data needed to write back to the database. In some cases there may be many questions (and answers) that needed to map back to one table row.

In some other cases some of the mapping with point back to tables that are designed for many records.

Thanks

Do you mean you want to dynamically modify the schema of the database? (Create tables for each question answers)

Your case would be much clearer if you provide a sample database schema of questions and answers.

The schema and data for the questions reside in several tables that are converted to the following XML file example (see below because it is a bit long).

I want to include the mapping instructions in this XML file as well so a business object can write the data collected for each question back to the target existing tables in the DB. In many cases the data collected will not be enough and some additional default data will need to be introduced. Some of the data records will have a single primary key (one row per instance) while others will have a composite key (zero to many rows).

This schema will likely need to be enhanced. The minimal mapping instructions I currently have are:

MapTable MapField MapTableType

The question items are:

Question Answer - (which is not showing up because I have not data in this sample) DataType - the type of data to convert the string Answer into. UiControlType - The UI presentation control. EnumList - if a TypedList control type is used then this is the source of an enumerated list.

Thanks

===============================

The sample XML with question data is here:

<?xml version="1.0" encoding="utf-8"?> <Documents xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" LoanId="1" DocumentSetName="Loan Interview" DocumentSetId="2" xmlns="http://tempuri.org/FlexQuestions.xsd"> <Document DocumentName="URLA User Name and Password" DocumentListId="78"> <Question DocumentListQuestionId="181" Name="" Question="Thank you for choosing LenderName:. What would you like to do today?" CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="78" SequenceNumber="1" ParentDocumentListQuestionId="0" ControlType="30" ControlTypeName="TypedList" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="182" Name="" Question="For security purposes, we have masked your personal, sensitive data such as your Social Security Number and Date of Birth. You will recognize these fields because they are filled with asterisks instead of the data you originally entered. There is no need to re-enter this data prior to submitting your application unless you need to update it, or if you are not sure that the data you previously entered is accurate." CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="78" SequenceNumber="4" ParentDocumentListQuestionId="0" ControlType="1" ControlTypeName="TextBox" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="183" Name="" Question="Create a User ID. (Use 7-16 letters and/or numbers but no spaces, dashes, or symbols.)" CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="78" SequenceNumber="5" ParentDocumentListQuestionId="0" ControlType="1" ControlTypeName="TextBox" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="184" Name="" Question="Enter your User ID.(Use 7-16 letters and/or numbers but no spaces, dashes, or symbols)" CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="78" SequenceNumber="6" ParentDocumentListQuestionId="0" ControlType="1" ControlTypeName="TextBox" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="185" Name="" Question="Create a password. (Use 6-16 characters that are a combination of letters and numbers but no spaces, dashes, or symbols.)" CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="78" SequenceNumber="7" ParentDocumentListQuestionId="0" ControlType="1" ControlTypeName="TextBox" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="186" Name="" Question="Re-enter your password." CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="78" SequenceNumber="8" ParentDocumentListQuestionId="0" ControlType="1" ControlTypeName="TextBox" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="187" Name="" Question="Enter your password.(Use 6-16 characters that are a combination of letters and numbers but no spaces, dashes, or symbols.)" CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="78" SequenceNumber="9" ParentDocumentListQuestionId="0" ControlType="1" ControlTypeName="TextBox" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="188" Name="" Question="If you have forgotten your password, please contact us at Lender800Phone:." CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="78" SequenceNumber="10" ParentDocumentListQuestionId="0" ControlType="11" ControlTypeName="Label" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="189" Name="" Question="IMPORTANT INFORMATION ABOUT PROCEDURES FOR OPENING A NEW ACCOUNTP:To help the government fight the funding of terrorism and money laundering activities, federal law requires all financial institutions to obtain, verify, and record information that identifies each person who opens an account./P:P:What this means for you: When you open an account, we will ask for your name, address, date of birth, and other information that will allow us to identify you. We may also ask to see your driver\'s license or other identifying documents./P:" CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="78" SequenceNumber="11" ParentDocumentListQuestionId="0" ControlType="11" ControlTypeName="Label" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> </Document> <Document DocumentName="URLA Marketing Data Capture" DocumentListId="79"> <Question DocumentListQuestionId="190" Name="" Question="Please provide your email address." CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="1" ParentDocumentListQuestionId="0" ControlType="1" ControlTypeName="TextBox" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="191" Name="" Question="What can we help you with?" CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="2" ParentDocumentListQuestionId="0" ControlType="30" ControlTypeName="TypedList" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="192" Name="" Question="How did you hear about us?" CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="10" ParentDocumentListQuestionId="0" ControlType="30" ControlTypeName="TypedList" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="193" Name="" Question="Please provide the name of the Newspaper you read." CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="16" ParentDocumentListQuestionId="0" ControlType="30" ControlTypeName="TypedList" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="194" Name="" Question="Who were you referred by?" CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="29" ParentDocumentListQuestionId="0" ControlType="30" ControlTypeName="TypedList" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="195" Name="" Question="Please provide information about your Real Estate Agent." CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="37" ParentDocumentListQuestionId="0" ControlType="11" ControlTypeName="Label" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable=""> <Question DocumentListQuestionId="196" Name="" Question="Agent's Name" CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="38" ParentDocumentListQuestionId="195" ControlType="1" ControlTypeName="TextBox" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="197" Name="" Question="Agent's Company" CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="39" ParentDocumentListQuestionId="195" ControlType="1" ControlTypeName="TextBox" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> </Question> <Question DocumentListQuestionId="198" Name="" Question="Please provide the name of your Home Builder." CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="40" ParentDocumentListQuestionId="0" ControlType="1" ControlTypeName="TextBox" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="199" Name="" Question="Please provide information about your Financial Advisor." CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="41" ParentDocumentListQuestionId="0" ControlType="11" ControlTypeName="Label" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable=""> <Question DocumentListQuestionId="200" Name="" Question="Financial Advisor Name" CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="42" ParentDocumentListQuestionId="199" ControlType="1" ControlTypeName="TextBox" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="201" Name="" Question="Financial Advisor Company" CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="42" ParentDocumentListQuestionId="199" ControlType="1" ControlTypeName="TextBox" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> </Question> <Question DocumentListQuestionId="202" Name="" Question="Please provide the name of the Community Bank that referred you." CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="42" ParentDocumentListQuestionId="0" ControlType="1" ControlTypeName="TextBox" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="203" Name="" Question="Please provide the name of your company." CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="44" ParentDocumentListQuestionId="0" ControlType="1" ControlTypeName="TextBox" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="204" Name="" Question="Please provide the name of your referrer." CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="45" ParentDocumentListQuestionId="0" ControlType="1" ControlTypeName="TextBox" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="205" Name="" Question="Which Internet Site?" CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="46" ParentDocumentListQuestionId="0" ControlType="30" ControlTypeName="TypedList" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="206" Name="" Question="Please provide the name of the Internet Site." CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="72" ParentDocumentListQuestionId="0" ControlType="30" ControlTypeName="TypedList" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="207" Name="" Question="Please provide the name of the newspaper you read about us in." CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="73" ParentDocumentListQuestionId="0" ControlType="30" ControlTypeName="TypedList" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="208" Name="" Question="Since you haven't found the perfect home yet, we'll ask general questions about the kind of home you are looking for today. This is commonly referred to as a pre-approval. If your request is approved, we\'ll prepare an online pre-approval letter for you. You can share the pre-approval letter with real estate brokers or sellers to show that you\'re approved and ready to buy!p:After you have an accepted contract to purchase a home, contact us atbr:8884114246 and we'll put the finishing touches on your application." CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="79" SequenceNumber="74" ParentDocumentListQuestionId="0" ControlType="11" ControlTypeName="Label" DataType="1" DataTypeName="Text" EnumList="" MapTable="" MapField="" MapTypeTable="" /> </Document> <Document DocumentName="URLA Other Income Sources" DocumentListId="90"> <Question DocumentListQuestionId="419" Name="" Question="What is your income from retirement or pension?" CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="90" SequenceNumber="1" ParentDocumentListQuestionId="0" ControlType="18" ControlTypeName="Currency" DataType="0" DataTypeName="NA" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="420" Name="" Question="What is your alimony, child support, or separate maintenance income?" CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="90" SequenceNumber="2" ParentDocumentListQuestionId="0" ControlType="18" ControlTypeName="Currency" DataType="0" DataTypeName="NA" EnumList="" MapTable="" MapField="" MapTypeTable="" /> <Question DocumentListQuestionId="421" Name="" Question="What is your interest and/or dividend income?" CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="90" SequenceNumber="3" ParentDocumentListQuestionId="0" ControlType="18" ControlTypeName="Currency" DataType="0" DataTypeName="NA" EnumList="" MapTable="" MapField="" MapTypeTable="" /> </Document> <Document DocumentName="URLA Other Income not already Provided" DocumentListId="91"> <Question DocumentListQuestionId="422" Name="" Question="You indicated that you had income from other sources. If you haven't yet provided it, please enter the total amount for all other income below." CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="91" SequenceNumber="1" ParentDocumentListQuestionId="0" ControlType="31" ControlTypeName="Income" DataType="0" DataTypeName="NA" EnumList="" MapTable="" MapField="" MapTypeTable=""> <Income /> </Question> </Document> <Document DocumentName="URLA Assets" DocumentListId="92"> <Question DocumentListQuestionId="423" Name="" Question="Provide us with the name of the institution, the type of account and the approximate balance for your deposit accounts." CreatedDate="2008-11-26T00:00:00-08:00" ModifiedDate="2008-11-26T00:00:00-08:00" CreatedByUserId="1" ModifiedUserId="1" DocumentListId="92" SequenceNumber="1" ParentDocumentListQuestionId="0" ControlType="33" ControlTypeName="Asset" DataType="0" DataTypeName="NA" EnumList="" MapTable="" MapField="" MapTypeTable=""> <Asset /> </Question> </Document> </Documents>