Skip to main content
Typo
Source Link
SteveC
  • 6k
  • 2
  • 12
  • 25

I think youyou're looking for this. There weren't any changes needed to the JSON

select j1.LookupServiceType, j2.id, j3.AssetCode, j3.Symbol, j3.Issue, j3.Issuer
from
  OPENJSON(@json) WITH (LookupServiceType   nvarchar(4000),
                        Items NVARCHAR(MAX) '$.Items' AS JSON) j1
  outer APPLY
  OPENJSON(Items) WITH (id INT 'strict $.id',
                        value NVARCHAR(MAX) '$.value' /*AS JSON*/) j2
  outer apply 
  OPENJSON([value]) WITH (AssetCode NVARCHAR(50) '$.AssetCode',
                          Symbol NVARCHAR(50) '$.Symbol',
                          Issue NVARCHAR(50) '$.Issue',
                          Issuer NVARCHAR(50) '$.Issuer') j3;

Results

LookupServiceType   id      AssetCode   Symbol  Issue           Issuer
GetAssetValues      19676   TDAACC      NULL    ACCOUNT #49     TD AMERITRADE
GetAssetValues      19677   RE100       NULL    APN: 057        SAN ANTONIO TX 78212
GetAssetValues      19908   NALIPO      NULL    POLICY # L0472  NATIONWIDE LIFE

I think you looking for this. There weren't any changes needed to the JSON

select j1.LookupServiceType, j2.id, j3.AssetCode, j3.Symbol, j3.Issue, j3.Issuer
from
  OPENJSON(@json) WITH (LookupServiceType   nvarchar(4000),
                        Items NVARCHAR(MAX) '$.Items' AS JSON) j1
  outer APPLY
  OPENJSON(Items) WITH (id INT 'strict $.id',
                        value NVARCHAR(MAX) '$.value' /*AS JSON*/) j2
  outer apply 
  OPENJSON([value]) WITH (AssetCode NVARCHAR(50) '$.AssetCode',
                          Symbol NVARCHAR(50) '$.Symbol',
                          Issue NVARCHAR(50) '$.Issue',
                          Issuer NVARCHAR(50) '$.Issuer') j3;

Results

LookupServiceType   id      AssetCode   Symbol  Issue           Issuer
GetAssetValues      19676   TDAACC      NULL    ACCOUNT #49     TD AMERITRADE
GetAssetValues      19677   RE100       NULL    APN: 057        SAN ANTONIO TX 78212
GetAssetValues      19908   NALIPO      NULL    POLICY # L0472  NATIONWIDE LIFE

I think you're looking for this. There weren't any changes needed to the JSON

select j1.LookupServiceType, j2.id, j3.AssetCode, j3.Symbol, j3.Issue, j3.Issuer
from
  OPENJSON(@json) WITH (LookupServiceType   nvarchar(4000),
                        Items NVARCHAR(MAX) '$.Items' AS JSON) j1
  outer APPLY
  OPENJSON(Items) WITH (id INT 'strict $.id',
                        value NVARCHAR(MAX) '$.value' /*AS JSON*/) j2
  outer apply 
  OPENJSON([value]) WITH (AssetCode NVARCHAR(50) '$.AssetCode',
                          Symbol NVARCHAR(50) '$.Symbol',
                          Issue NVARCHAR(50) '$.Issue',
                          Issuer NVARCHAR(50) '$.Issuer') j3;

Results

LookupServiceType   id      AssetCode   Symbol  Issue           Issuer
GetAssetValues      19676   TDAACC      NULL    ACCOUNT #49     TD AMERITRADE
GetAssetValues      19677   RE100       NULL    APN: 057        SAN ANTONIO TX 78212
GetAssetValues      19908   NALIPO      NULL    POLICY # L0472  NATIONWIDE LIFE
Source Link
SteveC
  • 6k
  • 2
  • 12
  • 25

I think you looking for this. There weren't any changes needed to the JSON

select j1.LookupServiceType, j2.id, j3.AssetCode, j3.Symbol, j3.Issue, j3.Issuer
from
  OPENJSON(@json) WITH (LookupServiceType   nvarchar(4000),
                        Items NVARCHAR(MAX) '$.Items' AS JSON) j1
  outer APPLY
  OPENJSON(Items) WITH (id INT 'strict $.id',
                        value NVARCHAR(MAX) '$.value' /*AS JSON*/) j2
  outer apply 
  OPENJSON([value]) WITH (AssetCode NVARCHAR(50) '$.AssetCode',
                          Symbol NVARCHAR(50) '$.Symbol',
                          Issue NVARCHAR(50) '$.Issue',
                          Issuer NVARCHAR(50) '$.Issuer') j3;

Results

LookupServiceType   id      AssetCode   Symbol  Issue           Issuer
GetAssetValues      19676   TDAACC      NULL    ACCOUNT #49     TD AMERITRADE
GetAssetValues      19677   RE100       NULL    APN: 057        SAN ANTONIO TX 78212
GetAssetValues      19908   NALIPO      NULL    POLICY # L0472  NATIONWIDE LIFE