I have a Snowflake table with a column MY_XML.
(This is listed as an object type - is that normal?)
I would like to convert the column elements to JSON so that I can unlock the more-straightforward parsing behaviour (e.g. dot notation) discussed here https://stackoverflow.com/a/67223430/1021819.
How?!
Put another way: How can I use (preferably native) Snowflake SQL to convert XML to JSON (for, say, export)?
It's a bit like export a relational Snowflake table as valid JSON or XML, but for arbitrarily-nested objects (which I don't want to specify key by key, only to have to query again).
Things I have tried:
select
MY_XML,
PARSE_XML(MY_XML) as object, --produces unqueryable object
TO_JSON(MY_XML) as json_data,
PARSE_JSON(json_data):"$":"$":"$"[1]:"$"::string as arbitrary_field,
PARSE_JSON(json_data):"$":"$"::string as base
from my_table
- works, but each JSON element is a list of dicts all keyed by $ (with some @s), rather than my domain-specific key-value pairs.
[PS: Bonus points for discussion: since the object is of arbitrary nested structure, it gets increasingly messy to pull out all the fields. Perhaps I should rather flatten?]
[PPS: Any chance of an XML version of https://docs.snowflake.com/en/sql-reference/functions/json_extract_path_text ?]
Huge thanks as always!
