选择查询以从xml数据类型中获取子元素
选择查询以从xml数据类型中获取子元素
提问于 2018-06-16 03:05:59
我有以下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') = 5SELECT查询仅返回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
Stack Overflow用户
回答已采纳
发布于 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);Stack Overflow用户
发布于 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子句会产生所需的结果：
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50881397
复制相关文章
相似问题
腾讯云开发者
