SELECT查询从xml数据类型获取子元素

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

我有以下SQL脚本（以及底部的XML结构）：

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)') ?

SELECT查询仅返回Options子字符串（蓝色）的第一行。如何通过更改t.Col.value（'Options [1] / string [1]'，'varchar（MAX）'）将所有值设为4行（蓝色，红色，白色，黑色）？

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>'

2 个解决方案

#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);

#2

You can go down to reach <string> tag level with Questions/Question/Options/string and then go back one level to get QuestionId:

您可以使用Questions / Question / Options / string转到标签级别，然后返回一级获取QuestionId：

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

Results:

结果：

As pointed out in comments the above solution will not work when another <question> tag appears before the one that must be selected.

正如在评论中指出的那样，当另一个标签出现在必须选择的标签之前时，上述解决方案将不起作用。

This is the new input scenario with 4 <question> tags:

这是带有4个标记的新输入方案：

<?xml version="1.0" encoding="UTF-8"?>
<Questions>
    <Question>
        <RowType>Question</RowType>
        <Required>False</Required>
        <QuestionText>select color</QuestionText>
        <QuestionType>Radio Buttons</QuestionType>
        <QuestionId>6</QuestionId>
        <Options />
    </Question>
    <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>7</QuestionId>
        <Options />
    </Question>
    <Question>
        <RowType>Question</RowType>
        <Required>False</Required>
        <QuestionText>select color</QuestionText>
        <QuestionType>Radio Buttons</QuestionType>
        <QuestionId>8</QuestionId>
        <Options>
            <string>Blue</string>
            <string>Red</string>
            <string>White</string>
            <string>Black</string>
        </Options>
    </Question>
</Questions>

Using the following query:

使用以下查询：

SELECT 
    t.col.value('((.)/QuestionId)[1]','int') AS QuestionId,
    u.Col.value('(.)[1]' ,'varchar(50)')     AS Options 
FROM @questions.nodes ('Questions/*')    t(Col) 
    OUTER APPLY t.Col.nodes('Options/*') u(Col)

these are the results:

这些是结果：

applying the where clause leads to the desired results:

应用where子句会产生预期的结果：

#1