Output data is not easy to parse

[freijon]

In CLI mode, the returned data sometimes needs to be used in scripts or automation. It therefore would be nice if the returned data would be easily parse-able. It would be nice if an output format could be selected, like CSV, JSON, XML, etc. I'm aware that I could define a separator in horizontal output format. But unfortunately the output values won't be returned with quotes if the values themselves contain the separator character, making the parsing a big pain.

[shueybubbles]

What if we were to define an environment var like SQLCMDXMLMODE so the non-interactive mode could turn on XML mode the same way interactive mode uses :XML ON ? Then you could use FOR XML AUTO and the like in your TSQL and it'd just print it as-is. I would be reluctant to have sqlcmd itself be responsible for encoding the data to JSON or XML, because its output might differ from the equivalent server-side conversion.

Maybe sqlcmd could encode CSV, though even within CSV there are a bunch of corner cases that some applications and parsers disagree on how to handle.

[freijon]

An interesting approach. A thing to consider: I usually execute sql files (with -i) which I also use with Azure Data Studio. I would therefore like to be able to decide to only show the XML output only with sqlcmd but not with ADS. Would the env var SQLCMDXMLMODE just be used to output raw data instead of formatted data?

[shueybubbles]

SQL Server is going to output XML if you use FOR XML AUTO in your query no matter where you run it. I think it is out of scope for sqlcmd itself to start re-encoding output to JSON or XML. If your typical .sql file content is a single select, one possible solution would be to have a second .sql file whose only content is FOR XML AUTO and add that file to the -i switch in your script.

[freijon]

I see. What would SQLCMDXMLMODE do exactly then? Also, in sqlcmd when using FOR XML AUTO the output gets truncated at a certain position. Would the env var fix that? Also, I thought -c "FOR XML AUTO; GO" could solve my problem with query files, but apparently that doesn't do anything

[shueybubbles]

do you have an example of XML content being truncated after using :XML ON in interactive mode? If ODBC sqlcmd and go-sqlcmd behave differently we should fix that too. The intent of the new environment variable would be to act like :XML ON command was in effect.

The only thing -c does is tell sqlcmd what delimiter to use instead of GO to separate batches; the value of the delimiter itself is never sent to the server.

[freijon]

Here is an example of an output XML created as follows in interactive mode:

:XML ON
SELECT * FROM TBL_FJ_TEST FOR XML AUTO
GO

output.txt

The file is cut off at column (character) 2034, it seems a newline character is inserted?