SELECT query to get child elements from xml datatype

Question

I have the following SQL script (and the XML structure at the bottom):

DECLARE @questions XML

SELECT 
    t.Col.value('QuestionId[1]', 'int') AS  QuestionId,
    t.Col.value('Options[1]/string[1]', 'varchar(MAX)') Options 
FROM 
    @questions.nodes ('//Question') t(Col) 
WHERE 
    t.Col.value('QuestionId[1]', 'int') = 5

The SELECT query is returning only first row for Options child string (Blue). How can I get all the values as 4 rows (Blue, Red, White, Black) by changing t.Col.value('Options[1]/string[1]', 'varchar(MAX)') ?

SET @questions = '<?xml version="1.0" encoding="UTF-8"?>
    <Questions>
       <Question>
          <RowType>Question</RowType>
          <Required>False</Required>
          <QuestionText>select color</QuestionText>
          <QuestionType>Radio Buttons</QuestionType>
          <QuestionId>5</QuestionId>
          <Options>
             <string>Blue</string>
             <string>Red</string>
             <string>White</string>
             <string>Black</string>
          </Options>
       </Question>
       <Question>
          <RowType>Question</RowType>
          <Required>False</Required>
          <QuestionText>select color</QuestionText>
          <QuestionType>Radio Buttons</QuestionType>
          <QuestionId>6</QuestionId>
          <Options />
       </Question>
    </Questions>'

Answer 1

You need apply :

SELECT t.col.value('(./QuestionId)[1]','int') AS QuestionId,
       t1.Col.value('(text())[1]', 'varchar(max)') AS Options
FROM @questions.nodes ('/Questions/Question') t(Col) OUTER APPLY 
     t.Col.nodes('Options/*') t1(Col);