You're welcome!

Maybe this?

SELECT o.value AS origin_address,
	   d.value AS destination_address,
       JSON_VALUE(e.value,N'$.distance.text') AS [dist_text],
       JSON_VALUE(e.value,N'$.distance.value') AS...

Here's a couple of queries that should help

SELECT JSON_VALUE(@json,N'$.destination_addresses[0]') as destination_addresses,
       JSON_VALUE(@json,N'$.origin_addresses[0]') as origin_addresses;


SELECT e.*
FROM OPENJSON(@json, '$.rows') r
CROSS APPLY OPENJSON(r.value, '$.elements')...

SELECT rn,dur,21-sum(dur) over(order by rn desc) as calculated_col
FROM sample_data
order by rn;

• This reply was modified 3 years, 7 months ago by  Mark Cowne.

Not totally clear what you want, but this gives you counts from your XML

declare @x xml = '
<Employees>
	<Person>
		<ID>1000</ID>
		<minor>yes</minor>
	</Person>
	<Person>
		<ID>1001</ID>
		<minor>yes</minor>
	</Person>
	<Person>
		<ID>1002</ID>
		<minor>yes</minor>
	</Person>
	<Person>
		<ID>1003</ID>
		<minor>no</minor>
	</Person>
</Employees>
';

select @x.query('
 for $value in distinct-values(/Employees/Person/minor)
  let $count :=...

• This reply was modified 3 years, 7 months ago by  Mark Cowne. Reason: Typo

You should be able to parse this using OPENJSON

declare @data table(instance varchar(20),array nvarchar(max));
insert into @data(instance,array)
values('server1.com',N'[[1613347200,"7"],[1613347205,"6"],[1613347210,"7"]]'),
      ('server2.com',N'[[1613347200,"5"],[1613347205,"8"],[1613347210,"7"]]');

select d.instance,
	   json_value(j.value, '$[0]') as...

• This reply was modified 3 years, 7 months ago by  Mark Cowne. Reason: Simplified code

This should work for you

select j.JobNumber,j.JobType,j.ProductType,j.Date,ca.DateOfCheckBeforeInstallation,ca.CheckDoneBy
from JobsTable j
cross apply(select top 1 p.DateOfCheck,p.CheckDoneBy
            from ProductChecks p
			where j.JobType...

See if this works

SELECT N.usernode.value('@id','varchar(10)') AS id
	,N.usernode.value('(add[@key="email"])[1]/@value', 'varchar(30)') AS email
FROM (values (@xml)) X(xmlcol)
	CROSS APPLY X.xmlcol.nodes(N'/Users/User') AS N (usernode);

No errors with SQL 2017 (14.0.3015.40)

See if this helps

SELECT
root.[key] AS [Order]
, resultval.[key] , resultval.[value]
FROM OPENJSON ( @jsondata ) AS root
CROSS APPLY OPENJSON ( root.value,  '$.result') AS resultval

• This reply was modified 3 years, 8 months ago by  Mark Cowne. Reason: Simplified code
• This reply was modified 3 years, 8 months ago by  Mark Cowne.

Maybe this?

    select ps.*,tc2.* from #partattributes ps 
    inner join #tradecodecontrol tc on ps.FeatureKey=tc.FeatureKey
    inner join #tradecodecontrol tc2...

Try this

SELECT
	X.Y.value('@ID', 'INT') AS ID,
	X.Y.value('./@*[local-name()=sql:variable("@ColumnName")][1]', 'VARCHAR(10)') AS Value
FROM @XML.nodes('/row') AS X(Y)

Maybe this, assumes a fixed number of generations

WITH CTE AS (

	SELECT p1.ID AS RootID, p1.PersonName AS RootName, p1.ID, p1.PersonName, p1.FemParent, p1.MaleParent,
		   CAST('' AS VARCHAR(1000)) AS...

Recursive cte method, won't be very efficient

with cte as (
	select top 1 Date1, cast(1 as bigint) as rn
	from #temp
	order by Date1

	union all

	select t.Date1, row_number() over(order by t.Date1)...

This should work for you

WITH CTE1 AS (
	SELECT [ROW_NUMBER],VALUE,
		   CASE WHEN LAG(VALUE) OVER(ORDER BY [ROW_NUMBER]) IS NULL THEN 1 ELSE 0 END AS ISSTART
	FROM T_REST
),
CTE2...