Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Storing XML in Database

    Hi,

    I'm trying to figure out the best way to store questionnaire data in a database.

    Since different questionnairs have different questions and formats i.e dropdown, radio, checkboxes etc building such a database model becomes highly complex.

    I've read that if data schema is complex and higly variable it may be better to use an xml document and store that in a databse. However I dont quite understand how you store xml to a database.

    Do you simply store the entire structure in something like a nvarchar column or is there some other way to store xml to a database.

    If you store the entire structure to the databse then how do you query the content to generate reports. Somebody please enlighten me on this topic.

    example xml:

    Code:
    <survey>
      <meta>
        <id>sample</id>
       </meta>
      <questions>
        <question id="1" type="singlechoice" page="1">
          <body>What is your gender?</body>
          <remarks>
          </remarks>
          <footer>
          </footer>
          <mandatory>true</mandatory>
          <otherenabled>false</otherenabled>
          <randomized>false</randomized>
          <dropdownlist>false</dropdownlist>
          <horizontal>false</horizontal>
          <choices>
            <choice>Male</choice>
            <choice>Female</choice>
          </choices>
        </question>
       </questions>
    </survey>

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    create table test1
     (col1 xml)
    
    insert into test1 (col1) 
    values (N'<root><item>blah</item></root>')
    Now that you know how, it is time to ask if you should.

    What sorts of reports are you likely to get requests for? Maybe how many questionnaires have such and such for a particular question? Think about how you would frame that query, and you may have an answer.
    Last edited by MCrowley; 07-20-12 at 14:55. Reason: fixed code tag

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by MCrowley View Post
    Code:
    create table test1
     (col1 xml)
    
    insert into test1 (col1) 
    values (N'<root><item>blah</item></root>')
    Now that you know how, it is time to ask if you should.

    What sorts of reports are you likely to get requests for? Maybe how many questionnaires have such and such for a particular question? Think about how you would frame that query, and you may have an answer.
    Report request would be very similar to what you've stated above i.e. how many people select option A from a dropdown list for question 1 etc.

    To query that in sql I could use a LIKE statement on the xml column.

    So what would be best design practice - to model the questionairs using tables and columns or xml?

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    LIKE on XML? You'll kill yourself and the server. Querying small XML documents, like configuration settings and the like is OK, but using XQuery (see Introduction to XQuery in SQL Server 2005 for reference).

    Your question about constructing Questionnaire - of course use conventional database objects. It sounds cute to use XML, and you may even get it to perform and scale, but writing complex reports would kill you too (mostly the guy after you).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •