我有以下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
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>'
发布于 2018-06-19 23:12:19
您需要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);
发布于 2018-06-17 03:55:10
您可以使用Questions/Question/Options/string
到达<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
结果:
正如注释中所指出的,当另一个<question>
标记出现在必须选择的标记之前时,上述解决方案将不起作用。
这是具有4个<question>
标签的新输入场景:
<?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>
使用以下查询:
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)
结果如下:
应用where子句会产生所需的结果:
https://stackoverflow.com/questions/50881397
复制相似问题