You can insert values into a new table based on a select query.

CREATE TABLE A
  AS (SELECT COL1, COL2
      FROM old_table
      WHERE COL3 = "A");

But you will have to do this for every distinct value of COL3 .

you can use python to do it,

import pandas as pd
import xml.etree.ElementTree as ET
import json
# Function to remove XML namespaces from the XML content
def remove_xml_namespaces(xml_content):
    return ''.join(['<{}>'.format(tag.split('}')[1]) if '}' in tag else tag for tag in xml_content.split('<')])
# Read the mapping file
mapping_df = pd.read_csv('mapping.csv')
# Read the JSON data from a file
with open('input2.json', 'r') as json_file:
    json_data = json.load(json_file)
# Initialize an empty list to store the data
data = []
for item in json_data:
    # Parse the XML string from the JSON data
    xml_string = item['xmlcol']
    
    # Remove XML namespaces
    xml_string = remove_xml_namespaces(xml_string)
    root = ET.fromstring(xml_string)
    # Iterate through root elements in the XML
    for element in root:
        # Initialize a dictionary to store the data for this XML
        xml_data = {}
        # Add "_id" value to the dictionary
        xml_data["_id"] = item["_id"]["$oid"]
        # Iterate through the mapping DataFrame
        for index, row in mapping_df.iterrows():
            xml_path = row['xml_path']
            column_name = row['column_name']
            # Find the element in the current root using the path
            elements = element.findall(xml_path)
            # Extract the values and append to the dictionary
            values = [element.text if element is not None else '' for element in elements]
            xml_data[column_name] = values
        data.append(xml_data)
# Create a DataFrame from the extracted data
result_df = pd.DataFrame(data)
# Iterate through columns containing arrays and apply explode
for column_name in result_df.columns:
    if isinstance(result_df[column_name].iloc[0], list):
        result_df = result_df.apply(lambda x: pd.Series(x[column_name]), axis=1)
        result_df.rename(columns=lambda col: column_name + str(col), inplace=True)
# Reset the DataFrame index
result_df.reset_index(drop=True, inplace=True)
# Replace empty strings with None
result_df = result_df.applymap(lambda x: None if x == '' else x)
# Show the resulting DataFrame
print(result_df)

mapping contains the map of which column you want to extract