Error "Error parsing json" while loading xml data in variant column in Snowflake

[anushaya1512]

Hi 

Input = XML file

I want to load it into a variant column in Snowflake.

Below is the XML file:

<school school_name='School'>
<dept dept_id='100'>
<dept_Name>'Secondary'</dept_Name>
</dept>
<student id='1'>
<name>'ABC'</name>
<std>'10th'</std>
</student>
</school>

Code:

Can someone help here please?

Output should be the entire content of XML doc should be saved in the variant column.

[manodwhb]

@anushaya1512 , what is the data type of XML_DATA in snowflake DB?

[manodwhb]

@anushaya1512 , If you remove the reject link,you will get the error. I believe the data types are not matching.

You may need to convert to string?

[anushaya1512]

@manodwhb 

The data type in Snowflake for XML_DATA is variant

[anushaya1512]

@manodwhb 

I tried with the document and string data type both. Still I get the same Error parsing XML error.

Check below screenshots.

Tried an Example with document data type at the initial level and string data type while loading:

Tried an Example with String data type throughout:

[manodwhb]

@anushaya1512 , if you have not take reject link the job will fail and will give the error. Can you share that.

[manodwhb]

@anushaya1512 , you can convert document data type to sting using .toString() function.

[anushaya1512]

@manodwhb 

Just tried it, it doesn't help.

[manodwhb]

@anushaya1512 ,can  you remove the reject part and run the job and share the error?

[anushaya1512]

@manodwhb 

If I remove the reject part, the Snowflake component doesn't throw any error, shows the job has successfully completed, it kind off by passes the record.

Note: The snowflake db components, do not throw error for such records like the other DB components.