I'm working with the set of data at this endpoint: <https://db.ygoprodeck.com/api/v7/cardinfo.php>

I need to take this JSON and import it into 2 tables: `Cards` and `Sets`. Below is the schema for each table.

**Cards**

```
id
name
type
desc
race
archetype
atk
def
ban_tcg
ban_ocg
ban_goat
level
attribute
linkval
image_url
image_url_small
pendulum_scale
linkmarker_topleft --deeper than single level
linkmarker_top --deeper than single level
linkmarker_topright --deeper than single level
linkmarker_left --deeper than single level
linkmarker_right --deeper than single level
linkmarker_bottomleft --deeper than single level
linkmarker_bottom --deeper than single level
linkmarker_bottomright --deeper than single level

```

For the `Cards` table, I can insert any single level data into a table using the code below, but how do I go one level deeper into the `linkmarkers` node and return the value found into the correct column? For example, for row 8614 in the dataset, the `linkmarkers` node has only 1 key and its value is "Top", so I would need the script to insert "Top" into the `linkmarker_top` column and null for all the other `linkmarker` columns.

```
Declare @JSON varchar(max)
Declare @JSON2 varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\Users\User\Desktop\YGOCards.json', SINGLE_CLOB) import
SELECT @JSON2 = value 
FROM OPENJSON (@JSON)
select * into #cards
from openjson(@JSON2)
with (
    [id] nvarchar(4000)
    , [name] nvarchar(1000)
    , [type] nvarchar(1000)
    , [desc] nvarchar(1000)
    , [race] nvarchar(1000)
    , [archetype] nvarchar(1000)
    , [atk] nvarchar(1000)
    , [def] nvarchar(1000)
    , [ban_tcg] nvarchar(1000)
    , [ban_ocg] nvarchar(1000)
    , [ban_goat] nvarchar(1000)
    , [level] nvarchar(1000)
    , [attribute] nvarchar(1000)
    , [linkval] nvarchar(1000)
)

```

*Example output row:*

```
id ...   linkmarker_topleft  linkmarker_top  linkmarker_topright  linkmarker_left...
94259633 null                Top             null                 null 

```

**Sets**

```
id
set_name
set_code
set_rarity
set_rarity_code

```

For the `Sets` table, I want to basically create a table where I relate each `id` to the `card_set` where each card came from, but I'm not sure how to do this. So using the first row of the dataset as an example:

*Example output row:*

```
id        set_name              set_code    set_rarity...
34541863  Force of the Breaker  FOTB-EN043  Common

```