如何连接存储在图像字段中的XML中的多个节点值 - 有一个转折

Question

我有XML，它存储在SQL的图像字段中。 我可以使用 CAST 提取 xml，然后将 xpath 提取到我想要的节点。 问题是节点是可重复的，我不知道会有多少个，所以我也必须连接这些值。 就像最后的添加一样，我还需要拆分替换每个字符中的某些字符......

所以我可以接近我想用这个做什么：

SELECT c.ID, REPLACE((CAST(CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX), ISNULL(zd.ValidContent, x.Content))) AS XML).value('(//preamble, //preamblehang)[1]', N'nvarchar(max)')), ' – ','|') FROM docs c INNER JOIN zones zd on c.docid = zd.docid ...

(这实际上是在许多其他实体和案例陈述中查看的，但为了简单起见，我将其剥离(

这将导致： 关键字结果

但是我从 CAST 获得的 XML 实际上看起来像这样：

....
<Arule>&#160;</Arule>
<preamble>[1]&#160;&#160;Merger &#8211; Large merger &#8211; Approval of merger &#8211; Subject to employment conditions.</preamble>
<preamble>[2]&#160;&#160;Merger &#8211; Large merger &#8211; Effect on competition &#8211; Lessening or preventing competition.</preamble>
<preamble>[3]&#160;&#160;Merger &#8211; Large merger &#8211; Efficiency defence &#8211; Section 12A &#8211; Pro-competitive gains outweighing loss to local consumers and production.</preamble>
<preamble>[4]&#160;&#160;Merger &#8211; Large merger &#8211; Public interest &#8211; Merger effects on employment.</preamble>
<preamble>[5]&#160;&#160;Merger &#8211; Large merger &#8211; Relevant product and geographic markets defined on a broad and narrow basis.</preamble>
<preamble>[6]&#160;&#160;Merger &#8211; Large merger &#8211; Market shares and concentration.</preamble>
<EdSummary>Editor&#8217;s Summary</EdSummary>
...

有多个"前导码"节点，我的选择只返回第一个节点，然后对字符串执行替换。 我需要获取所有节点的字符串，然后进行字符串替换。或替换，然后连接。

我假设我需要在某个地方插入一个 query(( 语句，里面有一个 For 语句，但到目前为止没有任何效果。 任何帮助都会很棒。

注意：XML 中的–是我用管道替换的破折号 (–( 字符。 然后，还需要按管道字符分隔节点。

Answer 1

玩了一会儿后，我设法得到了我想要的东西。 我现在将看到可能对其进行调整以将其放入函数中，或者在存储过程中预构建一个可用于在视图中连接的表。

这是得到我想要的结果的 SQL 语句：

Declare @xml xml, @ItemID int
Select @xml = CAST('<xml>' + CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX), z.ValidContent)) + '</xml>' AS XML)
FROM docs c WITH(NOLOCK)
INNER JOIN zone z ON c.ID = z.ID
WHERE c.ItemID = @ItemID
CREATE TABLE #KeywordData (nodes nvarchar(max))
INSERT INTO #KeywordData
select replace (B.x.value('.', N'nvarchar(max)'), ' – ','|')
from @xml.nodes('/xml') as A(x)
outer apply A.x.nodes('./preamble') as B(x)
SELECT SUBSTRING(
(
SELECT '|' + nodes as 'data()'
FROM #KeywordData FOR XML PATH('')
), 2, 9999) as keywords
DROP TABLE #KeywordData