SQL Server XML query using xquery with repeating relational data

I have a questionnaire stored in a XML column that I want to store in a non-xml table format instead. The source table looks like this:

ID          VERSION content
----------- ------- ---------------------------------------------------------------------
1211        96      <QS mg_id="1" locale_id="0" logoimg="inbox/FormImages/15/180.gif"...
1211        97      <QS mg_id="1" locale_id="0" logoimg="inbox/FormImages/15/180.gif"...

content:

<QS mg_id="1" locale_id="0" logoimg="inbox/FormImages/15/180.gif">
  <PAGE id="1" topic="Demographics" progress="9" background="inbox/FormImages/15/176.gif" lastPage="false" flush_concepts="1">
    <QUESTION id="2" type="dropdown" style="font-family:arial;font-size:12px;color:#086BB5;font-weight:bold;" value="What is your age?" defaultvalue="" listid="" conceptid="">
      <OPTIONS>
        <OPTION score="0" concept_value="1" value="I am under 18" url="" id="0" />
        <OPTION score="0" concept_value="2" value="Between 18 and 30 years old" url="" id="1" />
        <OPTION score="0" concept_value="3" value="Between 30 and 50 years old" url="" id="2" />
        <OPTION score="0" concept_value="4" value="Between 50 and 70 years old" url="" id="3" />
        <OPTION score="0" concept_value="5" value="Between 70 and 90 years old" url="" id="4" />
        <OPTION score="0" concept_value="6" value="Over 90 years old" url="" id="5" />
      </OPTIONS>
    </QUESTION>
    <QUESTION id="4" type="dropdown" style="font-family:arial;font-size:12px;color:#086BB5;font-weight:bold;" value="Do you have children?" defaultvalue="" listid="" tooltip="" usability="5">
      <OPTIONS>
        <OPTION score="0" concept_value="1" value="Yes" url="" id="0" />
        <OPTION score="0" concept_value="0" value="No" ur开发者_StackOverflowl="" id="1" />
      </OPTIONS>
    </QUESTION>
</PAGE>
</QS>

And I want to get output of the form:

ID          VERSION QuestionID QuestionValue     OptionID  OptionValue
----------- ------- ---------- ----------------- --------- -------------------------------
1211        96        2        What is your age?        1   I am under 18
1211        96        2        What is your age?        2   Between 18 and 30 years old
....
1211        96        4        Do you have children     1   Yes
....
1211        97        2 ...

Is there a way to do that using a select statement with SQL Server 2008?

Try this query:

SELECT
    ID, 
    Version,
    Q.value('(@id)[1]', 'int') AS 'QuestionID',
    Q.value('(@value)[1]', 'varchar(50)') AS 'QuestionValue',
    OPT.value('(@id)[1]', 'int') AS 'OptionID',
    OPT.value('(@value)[1]', 'varchar(50)') AS 'OptionValue'
FROM
    dbo.YourTableNameHere
CROSS APPLY 
    XmlCol.nodes('QS/PAGE/QUESTION') AS QS(Q)
CROSS APPLY
    QS.Q.nodes('./OPTIONS/OPTION') AS OPTS(OPT)

You need to use two CROSS APPLY operators to

• get a list of all <QUESTION> nodes inside your XML
• get a sub-list of all <OPTION> nodes inside a given <QUESTION> to get the option information for each question