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

Matillion ETL
1

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.

 

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

 

Thanks!

2

Hi @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.

3

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