I want to extract the component parts from the JSON string to produce individual fields

[Sally Walters-Thomas]

I have an SQL table which contains a JSON string in one of it's fields. I want to extract the component parts from the JSON string to produce individual fields using JSON_VALUE in the SQL statement as you can see below:

JSON_VALUE(ol.ProductOptions,'$.Options[0].QID1') as Question1

,JSON_VALUE(ol.ProductOptions,'$.Options[0].AID1') as Answer1

,JSON_VALUE(ol.ProductOptions,'$.Options[1].QID2') as Question2

,JSON_VALUE(ol.ProductOptions,'$.Options[1].AID2') as Answer2

,JSON_VALUE(ol.ProductOptions,'$.Options[2].QID3') as Question3

,JSON_VALUE(ol.ProductOptions,'$.Options[2].AID3') as Answer3

,JSON_VALUE(ol.ProductOptions,'$.Options[3].QID4') as Question4

,JSON_VALUE(ol.ProductOptions,'$.Options[3].AID4') as Answer4

,JSON_VALUE(ol.ProductOptions,'$.Options[4].QID5') as Question5

,JSON_VALUE(ol.ProductOptions,'$.Options[4].AID5') as Answer5

I can no longer use this due to the way that TCI works as I am producing individual XML files per order containing all the lines for an order.