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.

[ar7368]

What was the solution to this issue?. I get the same message...

[FATAL] 19:18:38 snowflake.bedmaster_snowflake_0_1.Bedmaster_Snowflake- tDBOutput_3 net.snowflake.client.loader.Loader$ConnectionError: State: COPY_INTO_TABLE, COPY INTO "TVR_20211028_191835_836_1" FROM '@~/TVR/INSERT/20211028_191835_836_D7I0QZ_1' on_error='ABORT_STATEMENT' file_format=( field_optionally_enclosed_by='"' empty_field_as_null=true), Error parsing JSON: <?xml version="1.0"?>

<catalog>

  <book id="bk101">

   <author>Gambardella, Matthew</author>

 File '@~/TVR/INSERT/20211028_191835_836_D7I0QZ_1/stream_20211028_191835_8360.gz', line 1, character 0

 Row 1, column "TVR_20211028_191835_836_1"["XML_LOAD":1]

 If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.

java.io.IOException: net.snowflake.client.loader.Loader$ConnectionError: State: COPY_INTO_TABLE, COPY INTO "TVR_20211028_191835_836_1" FROM '@~/TVR/INSERT/20211028_191835_836_D7I0QZ_1' on_error='ABORT_STATEMENT' file_format=( field_optionally_enclosed_by='"' empty_field_as_null=true), Error parsing JSON: <?xml version="1.0"?>

<catalog>

  <book id="bk101">

   <author>Gambardella, Matthew</author>

 File '@~/TVR/INSERT/20211028_191835_836_D7I0QZ_1/stream_20211028_191835_8360.gz', line 1, character 0

 Row 1, column "TVR_20211028_191835_836_1"["XML_LOAD":1]

 If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.

at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:124) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:64) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:434) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:338) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:482) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:194) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:131) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.core.SFStatement.execute(SFStatement.java:737) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.core.SFStatement.execute(SFStatement.java:635) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:238) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQuery(SnowflakeStatementV1.java:133) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.loader.ProcessQueue.run(ProcessQueue.java:145) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at java.lang.Thread.run(Thread.java:748) ~[?:1.8.0_301]

Oct 28, 2021 7:18:38 PM net.snowflake.client.loader.ProcessQueue run

SEVERE: State: COPY_INTO_TABLE, COPY INTO "TVR_20211028_191835_836_1" FROM '@~/TVR/INSERT/20211028_191835_836_D7I0QZ_1' on_error='ABORT_STATEMENT' file_format=( field_optionally_enclosed_by='"' empty_field_as_null=true), Error parsing JSON: <?xml version="1.0"?>

<catalog>

  <book id="bk101">

   <author>Gambardella, Matthew</author>

 File '@~/TVR/INSERT/20211028_191835_836_D7I0QZ_1/stream_20211028_191835_8360.gz', line 1, character 0

 Row 1, column "TVR_20211028_191835_836_1"["XML_LOAD":1]

 If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.

at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:124)

at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:64)

at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:434)

at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:338)

at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:482)

at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:194)

at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:131)

at net.snowflake.client.core.SFStatement.execute(SFStatement.java:737)

at net.snowflake.client.core.SFStatement.execute(SFStatement.java:635)

at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:238)

at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQuery(SnowflakeStatementV1.java:133)

at net.snowflake.client.loader.ProcessQueue.run(ProcessQueue.java:145)

... 1 more

[gjeremy1617088143]

hi:

<?xml version="1.0"?>

<catalog>

  <book id="bk101">

   <author>Gambardella, Matthew</author>

is a wrong formated xml

<?xml version="1.0"?>

<catalog>

<book id="bk101"/>

<author>

Gambardella, Matthew

</author>

</catalog>

is a good one

send me love and kudos