像这样改变你的查询:

SELECT STUFF(
(
    SELECT '!' + STUFF(p.query(N'for $n in .//*[local-name()!="dailyfrequency"]
                           return <a>{concat("$",($n/text())[1])}</a>'
                        ).value(N'.',N'nvarchar(max)'),1,1,'')
    FROM p.nodes(N'prescription') AS A(p)
    FOR XML PATH(''),TYPE).value(N'.',N'nvarchar(max)'),1,1,'')
FROM @xml.nodes(N'/plan/prescriptions') AS A(p);

首先我们为不同的处方创建一个派生表，其次我们对每个处方分别使用以前的代码.

First we create a derived table for the different prescriptions, Secondly we use the former code for each prescription separately.