'Extract number nested in {} (JSON/XML)

I have the following (one) column of values, declared as nvarchar(255) in SQL Server:

my_values
'{"id":"42501","type":"control_context"}'
'{"id":"42501","type":"control_context"}'
'{"id":"2808","type":"risk"}'

I would like to implement a function that does the following: if a value contains the word 'risk', then return the id, else null. The output for the above 3 values would be as below:

my_values_from_function
null
null
2808

I was trying to play with XML and the nodes method, like at this link, but I am not really making any progress. Any help will be much appreciated.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source