# How can I load the entire XML response of an API into a Snowflake variant?

**URL:** <https://matillioncommunity.discourse.group/t/how-can-i-load-the-entire-xml-response-of-an-api-into-a-snowflake-variant/1992>\
**Category:** Matillion ETL\
**Created:** [December 14, 2022, 6:49pm UTC](https://matillioncommunity.discourse.group/t/how-can-i-load-the-entire-xml-response-of-an-api-into-a-snowflake-variant/1992 "2022-12-14T18:49:53Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![btorchin](https://avatars.discourse-cdn.com/v4/letter/b/ec9cab/32.png) [@btorchin](https://matillioncommunity.discourse.group/u/btorchin)\
**Post date:** [December 14, 2022, 6:49pm UTC](https://matillioncommunity.discourse.group/t/how-can-i-load-the-entire-xml-response-of-an-api-into-a-snowflake-variant/1992/1 "2022-12-14T18:49:53Z")

</div>

I have tried a Query Profile, but cant seem to load the entire XML into a variant. I can of coarse flatten it into columns, which works nicely, but my requirement is to either load the entire XML or the XML if the repeating array into Snowflake variants.

&nbsp;

If there a file copy that can start from an API call? Then I could dump it into S3.

&nbsp;

Thanks!

---

<div class="post-metadata">

**Author:** ![Michael](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@Michael](https://matillioncommunity.discourse.group/u/Michael)\
**Post date:** [December 20, 2022, 3:43pm UTC](https://matillioncommunity.discourse.group/t/how-can-i-load-the-entire-xml-response-of-an-api-into-a-snowflake-variant/1992/2 "2022-12-20T15:43:08Z")

</div>

Hi [@btorchin](https://matillioncommunity.discourse.group/u/btorchin)​ ,

you can use the Query Profile for that. Create a target table with a clomun of type VARIANT and name JSON upfront and then use this script for the query profile:

**\<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema"\>**

**\<api:info title="Your API" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1"\>**

**\<attr name="JSON" xs:type="variant" readonly="false" other:xPath="\*" /\>**

**\</api:info\>**

**\<api:set attr="DataModel" value="DOCUMENT" /\>**

**\<api:set attr="JSONPath" value="/" /\>**

**\<api:set attr="uri" value="https://your.api/url" /\>**

**\<api:script method="GET"\>**

**\<api:call op="jsonproviderGet"\>**

**\<api:push/\>**

**\</api:call\>**

**\</api:script\>**

**\</api:script\>**

Hope that helps. Good luck.

---

<div class="post-metadata">

**Author:** ![Michael](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@Michael](https://matillioncommunity.discourse.group/u/Michael)\
**Post date:** [December 20, 2022, 3:44pm UTC](https://matillioncommunity.discourse.group/t/how-can-i-load-the-entire-xml-response-of-an-api-into-a-snowflake-variant/1992/3 "2022-12-20T15:44:23Z")

</div>

My example is for JSON data. But I'd expect this to work similar for XML data.