Performing sqlcmd for query containing FOR XML and writing output in XML path. Generated XML format is damaged and some text are removed

Question

I have SQL query for XML like the following (SQL Server 2012):

Use MedStreaming
SELECT distinct 1 as Tag,
   NULL as Parent,  
   NULL AS [ExamTypes!1],
   NULL as [ExamType!2!ExamTypeID],  
   NULL       as [Template!3!ShortString],  
   NULL       as [Template!3!Order]
UNION ALL 
SELECT distinct 2    as Tag,  
   1 as Parent,  
   NULL,
   Temp_Type.ExamTypeId as [ExamType!2!ExamTypeID],  
   NULL       as [Template!3!ShortString],  
   NULL       as [Template!3!Order]  
FROM   LayoutSectionTemplate_ExamType AS Temp_Type  
INNER JOIN LayoutSection_Template as temp
ON Temp_Type.TemplateId = temp.TemplateId
UNION ALL  
SELECT 3 as Tag,  
   2 as Parent, 
   NULL, 
   Temp_Type.ExamTypeId,  
   temp.ShortString,
   Temp_Type.[Order]
FROM   LayoutSectionTemplate_ExamType AS Temp_Type  
INNER JOIN LayoutSection_Template as temp 
ON  Temp_Type.TemplateId = temp.TemplateId 
ORDER BY [ExamType!2!ExamTypeID],[Template!3!Order]  
FOR XML EXPLICIT

I try to call it and write the XML result in an XML file using the following command:

sqlcmd -i "%~dp0\MedStreaming.Util\Scripts\GetExamTypes-TemplatesRelations.sql" -o "C:\temp\examTypes-reportsTemplates.xml"

But the file result XML format is not correct, and some texts are removed. I tried to put "XML ON" on the first of the query, but it doesn't work (it gives me syntax error).

Any help ?

Answer 1

This is an annoying limitation of SQLCMD interface (same for Invoke-SqlCmd the Powershell module) - you have to put the XML or JSON output into NVARCHAR(MAX) or XML datatype so that SQLCMD can handle the output properly. There is a 2MB rowsize threshold for SQLCMD output whereafter it starts chunking the output data into additional DataRows.

Note: You should also set MaxCharLength if the data exceeds 4K characters in size.

SQLCMD XML Output

DECLARE @XML as XML
SET @XML= (select Name from TestXML For XML path ('')

SQLCMD JSON Output

DECLARE @json NVARCHAR(MAX)
SELECT @json = (select * from mytable for json auto)
select * from openjson(@json)

Another option is to use bcp utility which outputs directly to disk for larger data dumps.