Open freijon opened 1 week ago
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.
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?
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.
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
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.
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.