Truncated XML Field

[danhoulihan]

I am trying to pull an XML field into the designer using the Input Data tool connected to a Microsoft SQL database. The SQL statement is simply "select xmlresult from #XML_File". The #XML_File is being created in the Pre SQL Statement. xmlresult is defined as an XML field. If I execute "select datalength(xmlresult) from #XML_File" I get 55583909. The length of the field created by the query is 16777216. I am not trying to parse the XML data, I am simply trying to save it off to a file. I'm doing that by exporting the variable to a .csv file with a delimiter of "\0". The file contains all 16777216 bytes from the field and it is clearly truncated. Suggestions?

[SubratDas5]

What is the datatype that is getting assigned after the file is loaded?

You can change it to v_wstring and then check the length of the string in alteryx using a formula tool..

This should give you some headway.

[danilang]

Hi @danhoulihan 

16777216 is 2^24 so it looks like this is some kind of field length limitation imposed somewhere in the input chain.  It could be on the Alteryx side or on the SQL OLEdb/ODBC side.  This same limitation applies to CSV files as well.  I've got a 19MB text file that is a single string and I can't get the input tool to read it in as a CSV, though if I break it into chunks it comes in just fine in several records

Here are a couple of alternatives you can try

1. Since you're creating the xml in the preSQL, can you output the results into a table with line breaks instead of an XML field? 

2.  Another alternative would be to reproduce the logic from the preSQL within Alteryx and generate the xml from the raw data.

Dan

[danhoulihan]

I'm not so much loading a file as I am retrieving data from my SQL database. A field of the type XML is created in the SQL code. The filed type being selected by the Input Data tool is V_String. I've tried adding a Select tool and changing the data type their but by the time that tool enters the picture that data has already been lost. Is there a way to override the datatype being selected by the Input Tool?

[danhoulihan]

I could probably find a way to chunk this into smaller parts to be assembled on the back end but that's a path that I don't want to go down, at least not for this particular task. I don't own the code that's creating the XML field and doing anything other than building a means to get the data from the database into a file without alteration is not a good option for me. Introducing Alteryx at all is taking this outside of our normal production environment, which may or may not be approved. If I can get this to work with some sort of simple configuration change then I might be able to use this. Otherwise, I'll need to set this aside for now. Thanks!

[emilyforeman]

Hi @danhoulihan

Did you ever resolve this issue? I am experiencing the exact issue. Pulling in an XML from an input tool and getting the truncation to the exact value of 16777216. Curious of your solution or work around as this is a major blocker for a client. 

Thanks!