Получение нескольких ответов из XML-строки SQL

Question

У меня есть XML-документ, сохраненный в столбце как VARCHAR (Макс). Текст, который я хочу, окружен <Text> Words I Want</Text>но эти текстовые теги повторяются иногда 4 или 5 раз.

Как мне просмотреть один и тот же документ x раз, в зависимости от количества текстовых тегов?

В настоящее время я использую это, чтобы вытащить первый бит текста

 DECLARE @first_char nvarchar(10)
DECLARE @second_char nvarchar(10)

SET @first_char = 'xt>';
SET @second_char = '</text>';

SELECT[TestId]
      ,[SectionId],
SUBSTRING
(
-- column
 settings
-- start position
,CHARINDEX(@first_char, Settings , 1) + 3
-- length
,CASE
WHEN (CHARINDEX(@second_char, Settings , 0) - CHARINDEX(@first_char, Settings, 0)) > 0
THEN CHARINDEX(@second_char, Settings, 0) - CHARINDEX(@first_char, Settings, 0) - 3
ELSE 0
END
) AS Response
FROM [B2IK-TestBuilder].[dbo].[Questions] 
group by [TestId]
      ,[SectionId], settings

и я знаю, сколько раз появляется текстовый тег.

Это пример документа xml, в котором сохранен varchar(max):

<Settings>
  <ShowNotes>true</ShowNotes>
  <ShowComment>false</ShowComment>
  <TextBefore>From the six safety essentials, a </TextBefore>
  <TextAfter>is essential before any work is carried out?</TextAfter>
  <Items>
    <Item>
      <Text>Answer 1</Text>
    </Item>
    <Item>
      <Text>Answer 2</Text>
    </Item>
    <Item>
      <Text>Answer 3</Text>
    </Item>
    <Item>
      <Text>Answer 4</Text>
    </Item>
    <Item>
      <Text>Answer 5</Text>
    </Item>
    <Item>
      <Text>Answer 6</Text>
    </Item>

Заранее спасибо.

Answer 1

Хорошо, начиная с SQL 2005, вы можете использовать XPath для запроса данных. Я бы рекомендовал использовать тип столбца XML для столбца Настройки. Затем вы можете использовать CROSS APPLY для получения узлов Item.

SELECT q.TestId,q.SectionId,x.XmlCol.value('(Text)[1]','VARCHAR(MAX)') 
    FROM Questions q
CROSS APPLY q.settings.nodes('/Settings/Items/Item') x(XmlCol);

Если вы по какой-либо причине не можете изменить тип столбца настроек, вы можете привести его в своем заявлении.

SELECT q.TestId,q.SectionId,x.XmlCol.value('(Text)[1]','VARCHAR(MAX)') 
    FROM (SELECT TestId,SectionId, cast([settings] as xml) as Data FROM Questions) q
CROSS APPLY q.settings.nodes('/Settings/Items/Item') x(XmlCol);