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 4 years, 8 months ago by Mark Cowne. Reason: Simplified code
• This reply was modified 4 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...

To make this dynamic have a look here

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

If I understand correctly, this should work for you

SELECT Tenor,
       COUNT(CASE WHEN ABS(ColA) > 0 THEN ColA END) + COUNT(CASE WHEN...

This is the standard SQL 'IS DISTINCT FROM' operator, not current implemented in T-SQL (yet!)

Interesting article below mentions it is implemented in the query processor

https://www.sql.kiwi/2011/06/undocumented-query-plans-equality-comparisons.html

The json isn't valid around "Website": "www.test.com": {

declare @json nvarchar(max) = '
{
    "id": "9999",
    "position": 1,
    "visible": true,
...

I think this is what you're after

declare @json nvarchar(max) = '
{"data": [{"id": "123456", "first_name": "John", "last_name": "Doe", "email": "jdoe@gmail.com", "phone_number": "333-333-3333", "href": "http://jdoe.com", "custom_fields": {"1": "John Doe...

• This reply was modified 5 years, 4 months ago by Mark Cowne.
• This reply was modified 5 years, 4 months ago by Mark Cowne.

This should work for you

Select b.id, b.title, b.nickname, b.href
from openjson(@json)
with ([data] nvarchar(max) '$.data' as json) a
cross apply openjson(a.[data])
with (id   int   '$.id',
  ...

Try this

SELECT v.n2.value('(@*:ObjectName)[1]','varchar(30)') AS Name,
   p.n1.value('(@*:ObjectName)[1]','varchar(30)') AS Scope
FROM #SSISTest st
CROSS APPLY st.PackageXML.nodes('//*:Executable') AS p(n1)
CROSS APPLY p.n1.nodes('*:Variables/*:Variable') as v(n2);

This is as near as I can get. You'll have to add '<?xml version="1.0" encoding="UTF-8"?>' manually. Also you've defined the date as a varchar, it should be a date or...