by 
Bienvenue à tous, it’s a pleasure to announce that on 5th October, at the Data Cloud World Tour Paris, thanks to the amazing hard work of Jade Le Van & Maxime Simon we’ll be launching Jeudis Givrés !
All of the Frosty Friday challenges will be translated into French and released on the following Thursday (for the sake of alliteration), followed by lots of community events to help boost those Parisian (and wider French) Snowflake skills!
To celebrate, we’re revisiting our Week 4 challenge on Spanish monarchs, but this time, in XML (which was partly invented by a Frenchman). Can you parse out the French monarchs?
Start Up Code
create or replace file format frosty_parquet
type = 'parquet';
create or replace stage s3_stage
url = 's3://frostyfridaychallenges'
create or replace table week64 as
select parse_xml($1:"DATA") as data
from @s3_stage/challenge_64/french_monarchs.parquet
(file_format => frosty_parquet);Remember if you want to participate:
- Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
- Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
- Post the URL in the comments of the challenge.
Thanks for the challenge, I always I find XML hard — and also, I think it’s about time that Snowflake should have a proper dynamic pivot…
the parsing…
https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_64_01.sql
and the pivoting
https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_64_03.sql
Sorry, when I said `thanks` above I meant `Merci!`
Was on leave last week so I’m a bit late to this one, but appreciate the challenge as always. It was fun finding a way to flatten the single-value lists and the multi-value lists in the same statement. It’s funny but also quite nice that my actual SELECT statement is only 9 lines of code
Not worked with XML data before, so was a bit of a challenge to get it working well