getting null value from parsed xml file

Question

I have a column where each cell has the same type of data. Where I needed a value at "EXILITY", which is "1",I tried using get(column_name, 6) but the position is not fixed , So it tried using a name like this: get(column_name, "EXILITY") but got a null value.

[
      {
        "$": 5,
        "@": "BASE",
        "@source": "ice"
      },
      {
        "$": 3.6,
        "@": "TEAL"
      },
      {
        "$": "C:C",
        "@": "VER_STG"
      },
      {
        "$": [
          {
            "$": 3,
            "@": "VEOR"
          },
          {
            "$": 1,
            "@": "COTY"
          }
        ],
        "@": "ACS",
        "CITY": 1,
        "VR": 0
      },
      {
        "$": [
          {
            "$": 2,
            "@": "CONFLITY"
          },
          {
            "$": 1,
            "@": "INGRITY"
          },
          {
            "$": 1,
            "@": "AVITY"
          }
        ],
        "@": "IT",
        "AVAILATY": 2,
        "CONFIDY": 0,
        "IY": 1
      },
      {
        "$": 1,
        "@": "AION"
      },
      {
        "$": 1,
        "@": "EXILITY"
      },
      {
        "$": 3,
        "@": "RL"
      },
      {
        "$": 1,
        "@": "RE"
      }
    ]

Trying to get the value at "@": "EXILITY" but able to get it simply I was trying to use the get function but didn't find any luck.

Answer 1

Sharing the XML could be more useful. Anyway based on the JSON you provided, you can fetch the value using FLATTEN and GET functions:

with json as (
select parse_json ('[
      {
        "$": 5,
        "@": "BASE",
        "@source": "ice"
      },
      {
        "$": 3.6,
        "@": "TEAL"
      },
      {
        "$": "C:C",
        "@": "VER_STG"
      },
      {
        "$": [
          {
            "$": 3,
            "@": "VEOR"
          },
          {
            "$": 1,
            "@": "COTY"
          }
        ],
        "@": "ACS",
        "CITY": 1,
        "VR": 0
      },
      {
        "$": [
          {
            "$": 2,
            "@": "CONFLITY"
          },
          {
            "$": 1,
            "@": "INGRITY"
          },
          {
            "$": 1,
            "@": "AVITY"
          }
        ],
        "@": "IT",
        "AVAILATY": 2,
        "CONFIDY": 0,
        "IY": 1
      },
      {
        "$": 1,
        "@": "AION"
      },
      {
        "$": 1,
        "@": "EXILITY"
      },
      {
        "$": 3,
        "@": "RL"
      },
      {
        "$": 1,
        "@": "RE"
      }
    ]') x 
    )
    select parse_xml(items.VALUE) xml, get(xml,'$') xml_value
    from json,
    lateral flatten( x ) items
    where get(XML,'@') = 'EXILITY';