its the latest version 18.1  of SSMS im running and this was installed on my local windows ...on my hard drive and not in docker

Now I did look at the...

Ok adapting Steve's code, I have managed to solve this:

My code as follows:

INSERT INTO [dbo].[ReturnItemsCalibration_Points] ([ReturnItemsCalibration_ChannelsID],[Points])

SELECT

RICC.ReturnItemsCalibration_ChannelsID,

jkvp.[Value] as Points

FROM

-- RETURNS

OPENJSON(@json)

WITH

(

-- Return

id INT '$.return.d3r_id'

) [returns]

CROSS APPLY

-- ITEMS

OPENJSON(@json, '$.return.items')

WITH

(

[name] NVARCHAR(30) ,

part_number NVARCHAR(30) ,

serial_number...

• This reply was modified 2 years, 8 months ago by  Weegee71.

Hi Steve

thanks for that .

i tried to adapt your code as follows but it didnt work

SELECT

ri.ReturnItemsID

, ROW_NUMBER() OVER (

PARTITION BY [returns].id,cal_channels.serial_number

ORDER BY [returns].id,cal_channels.serial_number

) RowNo

-- CALLIBRATION CHANNELS

,calc_chan_detail.channel

,calc_chan_detail.name

,calc_chan_detail.UNIT

,[calc_chan_ponts].*

--INTO [ReturnItemsCalibration_Channels]

FROM

-- RETURNS

OPENJSON(@json)

WITH

(

-- Return

id INT...

Hi Phil

Briliiant thank you very much that helps me dig into the hierarchy

Now from what I understand, you declare each section of the JSON  you want to access

like this:

items NVARCHAR(MAX)...

Hi Brian

OK ...here is my sample JSON

DECLARE @json NVARCHAR(MAX);

SET @json = N'

{

"return": {

"d3r_id": "4723",

"reference":"ccRET28092107",

"po": "4500478023",

"customer_reference": "",

"type": "Calibration",

"notes": "",

"status": "In Transit",

"eori": false,

"sent_to_salesforce": "2021-09-28 18:03:01",

"items": [

{

"name": "TK-0014",

"part_number": null,

"serial_number":...

attached is the source JSON file

• This reply was modified 2 years, 9 months ago by  Weegee71.

Hi Ratpak

Yes I tried those commands and both return results correctly as shown in the screenshot

Hi Eddie

I tried adding the schema dbo in . My defaut schema is set to dbo.

However it didnt recognise that . Please see attached screeenshot

Thanks Phil. I will download SSDT and give that a try

Hi jeff

Thats  a very elegant solution .. thank you very much for that

Hi Ratnbak

no there shouldnt be a space after the comma...that was a typo!

Thanks Kaj...that worked very well for me

Hi Phil

Brilliant. That works very well. Thank you very much for your help

Hey Phil

Ok these are attached

Hi Jeff

Apoolgies for delaying in replying. Im back at work after feeling poorly

Now i ran the code you supplied to create the test table. Attached are the screen shots from...