Dealing with nested JSON in SQL OPENJSON WITH command?

Question

I have JSON value that has some structure to it but I'm struggling to get to the 3rd level. I am using CROSS APPLY OPENJSON to get to the "Lines" data but I need to get the "Code" out of the TaxCode area... It seems to be it's own JSON array maybe?

Any help would be appreciated... This is what I have so far...

DECLARE @JSONText NVarChar(max) = '{
    "UID": "845bc256-6027-4a89-8c05-35e4bb8e6aba",
    "Number": "00013608",
    "Lines": [{
        "RowID": 1,
        "Total": 20.0,
        "TaxCode": "@{UID=f2cc83e5-0f7f-4831-9d88-dbe110e0683a; Code=S15}"
    },{
        "RowID": 2,
        "Total": 55.49,
        "TaxCode": "@{UID=a5cc34e5-0fr4-4325-9d67-bdh110e0683a; Code=S17}"
    }]
}';

SELECT J.[UID],J.[Number],LI.*
FROM OPENJSON (@JSONText)
WITH (
    [UID] nvarchar(512) '$."UID"',
    [Number] nvarchar(50) '$."Number"',
    [LineItems] NVarChar(max) '$."Lines"' AS JSON
) J
CROSS APPLY OPENJSON (J.[LineItems])
WITH (
    [RowID] INT '$."RowID"',
    [Total] Decimal(12,2) '$."Total"',
    [TaxCode] NVarChar(512) '$."TaxCode"',
    [TaxCodeTest] NVarChar(50) '$."TaxCode.Code"'
) LI;

Answer 1

As the TaxCode value being supplied wasn't a "true" JSON object I had to use some SQL to work with it as a string end get the required data. I ended up creating a function to do it using inspiration from this question...

A SQL Query to select a string between two known strings