Sqlcmd and / or PowerShell wrapping XML output

Question

I execute the following command:

sqlcmd -i "\\path\to\sqlfile.sql" -S "ServerName" -d "DBName" -y 0

Here sqlfile.sqlis a few statements DECLAREfor variables, and then a tree of nested statements SELECT ... FOR XML Path('...')to eventually create a large XML string as output.

At the PowerShell command prompt, I pass the output to a file:

sqlcmd -i "\\path\to\sqlfile.sql" -S "ServerName" -d "DBName" -y 0 | Out-File C:\path\to\output.xml

(Or I could use -oin the team itself, I have no strong preferences anyway.)

The goal is to have one XML file, which can then be loaded into another process in the subsequent process. This works well, except for one detail that is currently stuck with me. The output (to a file) is automatically verified with 2.033 characters. This means that it generates broken XML if (as in most cases) the wrapper breaks the tag:

<SomeTag>This is some content just to illustrate.</Som
eTag>

I tried using -win arguments sqlcmd. For testing that would succeed with values ​​below 2.033, but for any value above 2.033 ( 20000for example, which would be more than enough), the same maximum of 2.033 will remain.

I also tried calling -replacebefore passing the output to try to remove line breaks, for example:

([the sqlcmd command above]) -replace "\r", "" | Out-File C:\path\to\output.xml

\r, \n, \r\n . . ( -replace "KnownString", "TEST" TEST.)

, . - ? , PowerShell .sql XML ( , ) .xml.

-, / , 2033 ? , , ?

Answer 1

...

(: SQL 2012. , .)

.sql :

SET NOCOUNT ON
DECLARE @XML AS XML

SET @XML = ([the great big set of nested selects generating xml])

SELECT CAST(@XML AS NVARCHAR(MAX))

:

sqlcmd -i "\\path\to\sqlcode.sql" -S "ServerName" -d "DBName" -o "C:\path\to\output.xml" -y0

XML . ( - .)

Answer 2

:

• -replace '\r|\n', /

• sqlcmd, SqlClient powershell SQL- (, .sql powershell?).

, , ​​sqlcmd 2012, . : https://connect.microsoft.com/SQLServer/feedback/details/786004/