Hi @LearnDB1234 ,

Are you sure that this column stores xml as a string? To me it looks more like json  string 🙂
If so, you can use new VARIANT data type through parse_json function:

%sql
WITH src AS (

  SELECT parse_json('{
    "Status": {
        "Code": "Ok",
        "Message": null,
        "Errors": null
    },
    "RequestReference": null,
    "Response": {
        "IdNumber": "295",
        "SearchKeys": {
            "Subject": {
                "FirstName": "John",
                "MiddleName": null,
                "Surname": "Ross",
                "DateOfBirth": "1900-01-31T00:00:00",
                "Gender": "M"
            },
            "Address": {
                "AddressLine1": "123 old street",
                "AddressLine2": "high street",
                "AddressLine3": "",
                "AddressLine4": null,
                "AddressLine5": null,
                "Postcode": "12345",
                "Country": null
            },
            "Phone": null,
            "DrivingLicence": null,
            "Bank": null,
            "ConsentFlag": true
        }
    }
}') as json_column
)
SELECT 
    json_column:Response.IdNumber::INTEGER as id_number,
    json_column:Response.SearchKeys.Subject.FirstName::STRING as first_name,
    json_column:Response.SearchKeys.Subject.MiddleName::STRING as middle_name,
    json_column:Response.SearchKeys.Subject.Surname::STRING as last_name,
    json_column:Response.SearchKeys.Subject.DateOfBirth::STRING as date_of_birth,
    json_column:Response.SearchKeys.Address.AddressLine1::STRING as address_line1,
    json_column:Response.SearchKeys.Phone::STRING as phone
FROM src