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 ?

Community · Accepted Answer · 2017-05-23 10:30:01Z

2

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.

edited May 23, 2017 at 10:30

CommunityBot

11 silver badge

answered Mar 23, 2017 at 20:45

SliverNinja

31.7k12 gold badges121 silver badges182 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

SQLCMD XML Output

SQLCMD JSON Output

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

SQLCMD XML Output

SQLCMD JSON Output

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related