Not totally clear to me what you're asking for, maybe this?

with cte as (
select REF_NO,
       n.x.value('Country[1]','varchar(30)') as Country,
   ...

This should work pre 2016

select stuff((select ',' + n.x.value('Country[1]','varchar(30)') as "text()"
from MyView t
cross apply t.GlobalCountryRegionXML.nodes('/GlobalCountryRegion/CountryRegion') n(x) 
order by row_number() over(order by n.x)
for xml path('')),1,1,'') as Country,
stuff((select ','...

with cte as (
select n.x.value('Country[1]','varchar(30)') as Country,
       n.x.value('Region[1]','varchar(30)') as Region,
   row_number() over(order by n.x) as rn
from MyView t
cross apply...

with cte as (
select TestType, TestDate, case when lag(TestType) over(order by TestDate) = TestType then 0 else 1 end as IsStart
from #T
)
select TestType, TestDate,sum(IsStart) over(order by TestDate)...

Try this

declare @MyTable table(Personcode int, XMLBody XML);

insert into @MyTable(Personcode, XMLBody)
values(99999,'<content>
<fieldgroup>
<field id="Comment" type="textbox" title="Comment:" rows="6" cols="50" cssclass="textbox" mandatory="1">Extract the data string located here.</field>
<field id="Action" type="textbox" title="Action:" rows="6" cols="50"...

select t.Id, t.FileName, t.FolderName,
       kp.LicenceNo, kp.IDNumber, kp.Class, kp.CardNumber, kp.ExpiryDate
from MyFiles t
outer apply openjson(t.KeyPairs) 
with (LicenceNo int '$."Licence No "',
  ...

WITH CTE1 AS (
SELECT *,
      CASE WHEN LAG(t_type) OVER(PARTITION BY t_id ORDER BY t_date) = t_type THEN 0 ELSE 1 END AS...

declare @t table(codetype varchar(10), code int, Value int);
insert into @t(codetype, code, Value)
values('Test1',1,2),
('Test1',2,3),
('Test2',4,5),
('Test2',6,7);


SELECT 
   N'{' + 
   STUFF(
      (
...

WITH Ranges AS (
SELECT (RecordId - NumDays + 1) As Start, RecordId
FROM #test 
WHERE NumDays > 0)
UPDATE #test SET AmountAll = B.AmountAll
FROM #test A
JOIN (SELECT r.RecordId, SUM(t.Amount)...

This should work for you

select x.r.value('Index[1]','int') as ChannelID,
       x.r.value('(SensorScaling/OutUnits)[1]','varchar(20)') as [SI Units]
from @DataSheetXML.nodes('Datasheet/ChannelList/ChannelDefinition[@IsEnableable="True"]') x(r);

See if this helps

select pd.a.value('let $a := . return 1 + count(../ProbeDefinition[. << $a])', 'int') AS MasterRecordNumber,
       cl.b.value('.','int') as ChannelListValue
from @DataSheetXML.nodes('Datasheet/ProbeList/ProbeDefinition')...

with cte as (
select ID, TS, FLAG,
       case when lag(flag) over(partition by id order by ID,TS) = flag then 0 else...

SELECT m.displayid, STRING_AGG(m.promo_id,';') AS Concat_Promo_Id, STRING_AGG(h.promogroup,';') AS Concat_promogroup
FROM Product_Main m
LEFT OUTER JOIN Product_Helper h ON h.displayid = m.displayid AND h.promo_id = m.promo_id
GROUP BY m.displayid;

Replace

Data.Col.value('(./PartyID)[1]', 'int') As Party_ID

with

Data.Col.value('(../PartyID)[1]', 'int') As Party_ID

select t1.PartFamilyId, t1.FamilyStatus + isnull('|' + ca.FamilyStatus,'') as FamilyStatus
from #partsFamily t1
outer apply (select 'NULL' from #partsFamily t2 where t2.PartFamilyId = t1.PartFamilyId and t2.FamilyStatus is null) ca(FamilyStatus)
where t1.FamilyStatus...