# Loading XML file with multiple container nodes **URL:** \ **Category:** Matillion ETL\ **Created:** [September 23, 2021, 11:48pm UTC](https://matillioncommunity.discourse.group/t/loading-xml-file-with-multiple-container-nodes/1472 "2021-09-23T23:48:23Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![chris.odell1617208923357](https://sea2.discourse-cdn.com/flex002/user_avatar/matillioncommunity.discourse.group/chris.odell1617208923357/32/1105_2.png) [@chris.odell1617208923357](https://matillioncommunity.discourse.group/u/chris.odell1617208923357)\ **Post date:** [September 23, 2021, 11:48pm UTC](https://matillioncommunity.discourse.group/t/loading-xml-file-with-multiple-container-nodes/1472/1 "2021-09-23T23:48:23Z")
So I'm trying to get some XML files loaded and ultimately flattened out into a Snowflake table. The S3 Load component of course supports XML and even offers the option to "Strip outer element" but unfortunately, the file I need to ingest has two outer elements and despite a day of trying various methods I'm stumped on how to proceed.   If I remove one of the two outer elements, S3 Load does its thing and I end up with one row per \ element, which is exactly what I need. If I use S3 Load without any modifications to the file, I end up with the entire XML file loaded into a variant column, which thus far I've been unable to query successfully with Snowflake SQL.   Unfortunately though I have to consume these from a URL provided by the vendor and unless I can figure out some way to either a ) strip one of the elements from the file before S3 Load, or b) figure out how to query the desired sub-elements after loading the whole file into one column, I'm stuck.   The XML in question is attached. Any suggestions appreciated! ---
**Author:** ![Bryan](https://avatars.discourse-cdn.com/v4/letter/b/db5fbb/32.png) [@Bryan](https://matillioncommunity.discourse.group/u/Bryan)\ **Post date:** [September 28, 2021, 3:57pm UTC](https://matillioncommunity.discourse.group/t/loading-xml-file-with-multiple-container-nodes/1472/2 "2021-09-28T15:57:06Z")
Hi [@chris.odell1617208923357](https://matillioncommunity.discourse.group/u/chris.odell1617208923357)​ , Hi Chris, there are a couple approaches to take on this but the most out of the box approach would be to leverage the power of Snowflake as much as possible. Implement the pattern you spoke about in this statement , "If I use S3 Load without any modifications to the file, I end up with the entire XML file loaded into a variant column". Once the data is in snowflake you have several options for handling the flattening which will allow you to materialize the data in to tables or simply expose the data via a view. This is what the SQL statement would look like to query that data once it's loaded into a variant column as a single row. select get(xmlget(r.$1, 'title'), '$')::string as FEED\_TITLE, get(xmlget(r.$1, 'link'), '$')::string as FEED\_LINK, get(xmlget(r.$1, 'description'), '$')::string as FEED\_DESCRIPTION, get(xmlget(parse\_xml(f.THIS), 'g:id'), '$')::string as ITEM\_ID, get(xmlget(parse\_xml(f.THIS), 'g:gtin'), '$')::string as ITEM\_GTIN, get(xmlget(parse\_xml(f.THIS), 'title'), '$')::string as ITEM\_TITLE, get(xmlget(parse\_xml(f.THIS), 'description'), '$')::string as ITEM\_DESCRIPTION, get(xmlget(parse\_xml(f.THIS), 'g:brand'), '$')::string as ITEM\_BRAND, get(xmlget(parse\_xml(f.THIS), 'g:google\_product\_category'), '$')::string as ITEM\_GOOGLE\_PRODUCT\_CATEGORY, get(xmlget(parse\_xml(f.THIS), 'g:product\_type'), '$')::string as ITEM\_PRODUCT\_TYPE, get(xmlget(parse\_xml(f.THIS), 'g:item\_group\_id'), '$')::string as ITEM\_GROUP\_ID, get(xmlget(parse\_xml(f.THIS), 'g:color'), '$')::string as ITEM\_COLOR, get(xmlget(parse\_xml(f.THIS), 'g:size'), '$')::string as ITEM\_SIZE, get(xmlget(parse\_xml(f.THIS), 'g:image\_link'), '$')::string as ITEM\_IMAGE\_LINK, get(xmlget(parse\_xml(f.THIS), 'g:additional\_image\_link'), '$')::string as ITEM\_ADDITIONAL\_IMAGE\_LINK, get(xmlget(parse\_xml(f.THIS), 'g:price'), '$')::string as ITEM\_PRICE, get(xmlget(parse\_xml(f.THIS), 'g:availability'), '$')::string as ITEM\_AVAILABILITY, get(xmlget(parse\_xml(f.THIS), 'g:condition'), '$')::string as ITEM\_CONDITION, get(xmlget(parse\_xml(f.THIS), 'g:adwords\_labels'), '$')::string as ITEM\_ADWORDS\_LABELS, get(xmlget(parse\_xml(f.THIS), 'g:shipping\_label'), '$')::string as ITEM\_SHIPPING\_LABEL from YOUR\_SCHEMA.YOUR\_TABLE r, table(flatten(input =\> r.$1, recursive =\> true)) f where f.KEY = '@' and f.VALUE = 'item'; I hope this helps. ---
**Author:** ![chris.odell1617208923357](https://sea2.discourse-cdn.com/flex002/user_avatar/matillioncommunity.discourse.group/chris.odell1617208923357/32/1105_2.png) [@chris.odell1617208923357](https://matillioncommunity.discourse.group/u/chris.odell1617208923357)\ **Post date:** [September 28, 2021, 6:29pm UTC](https://matillioncommunity.discourse.group/t/loading-xml-file-with-multiple-container-nodes/1472/3 "2021-09-28T18:29:25Z")
Hey Bryan, thanks for the reply. Your suggestion to use xmlget() in a sql component is exactly what I ended up doing, though not quite as elegantly. Going to take some time later this week to study yours in detail 😄   I'm not sure why, but in my environment the "Strip outer element" option wasn't working until I deleted & re-added the S3 Load component. Once I fixed that, I was dealing with just one container node and was able to take a slightly simpler approach.   SELECT  xmlget(t1."VALUE",'g:id'):"$"::STRING AS id,  xmlget(t1."VALUE",'g:gtin'):"$"::STRING AS gtin, xmlget(t1."VALUE",'title'):"$"::STRING AS title, xmlget(t1."VALUE",'description'):"$"::STRING AS description, xmlget(t1."VALUE",'g:brand'):"$"::STRING AS brand, xmlget(t1."VALUE",'g:product\_type'):"$"::STRING AS product\_type, xmlget(t1."VALUE",'g:color'):"$"::STRING AS color, xmlget(t1."VALUE",'g:size'):"$"::STRING AS size, xmlget(t1."VALUE",'g:image\_link'):"$"::STRING AS image\_link, xmlget(t1."VALUE",'g:price'):"$"::STRING AS price, xmlget(t1."VALUE",'g:availability'):"$"::STRING AS availability, xmlget(t1."VALUE",'g:condition'):"$"::STRING AS condition, xmlget(t1."VALUE",'g:additional\_image\_link'):"$"::STRING AS additional\_image\_link from (SELECT value FROM VENDORETL."PUBLIC"."stage\_GIANT\_BIKES", LATERAL flatten(VENDORETL."PUBLIC"."stage\_GIANT\_BIKES".FILECONTENTS:"$") ) AS t1 WHERE t1."VALUE" LIKE '\%'