topic Re: Error "Error parsing json" while loading xml data in variant column in Snowflake in Talend Studio

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

anushaya1512 — Wed, 26 Feb 2020 09:37:17 GMT

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.

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

manodwhb — Wed, 26 Feb 2020 12:34:12 GMT

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

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

manodwhb — Wed, 26 Feb 2020 12:35:37 GMT

@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?

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

anushaya1512 — Thu, 27 Feb 2020 04:03:37 GMT

@manodwhb

The data type in Snowflake for XML_DATA is variant

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

anushaya1512 — Thu, 27 Feb 2020 04:23:16 GMT

@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:

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

manodwhb — Thu, 27 Feb 2020 05:31:40 GMT

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

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

manodwhb — Thu, 27 Feb 2020 05:33:12 GMT

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

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

anushaya1512 — Thu, 27 Feb 2020 06:04:34 GMT

@manodwhb

Just tried it, it doesn't help.

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

manodwhb — Thu, 27 Feb 2020 06:14:26 GMT

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

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

anushaya1512 — Thu, 27 Feb 2020 06:26:13 GMT

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

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

ar7368 — Thu, 28 Oct 2021 23:27:18 GMT

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"?>

<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"?>

<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]

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"?>

<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]

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

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

gjeremy1617088143 — Fri, 29 Oct 2021 15:06:12 GMT

hi:

<?xml version="1.0"?>

<author>Gambardella, Matthew</author>

is a wrong formated xml

<?xml version="1.0"?>

Gambardella, Matthew

</author>

</catalog>

is a good one

send me love and kudos