0

After referring to those two posts - XML Output is Truncated in SQL, Generating an XML output using XML OPTIONS in SQL Server

And setting in SQL Server Management Studio (SSMS) as - Option Setting in SSMS to set XML output data to an unlimited size.

DECLARE @xmldata xml
SET @xmldata = (
SELECT 
'DATE' as 'column/@name', [DATE] as 'column', null, 
'ID' as 'column/@name', [ID] as 'column', null, 
'NAME' as 'column/@name', [NAME] as 'column', null
FROM TEST_TBL
FOR XML PATH('row'), ROOT('resultset')
)
SELECT @xmldata AS returnXML

The XML output in SSMS is all right. But if I run SQLCMD.EXE to generate an XML output file as below,

DECLARE @xmldata xml
SET @xmldata = (
SELECT 
'DATE' as 'column/@name', [DATE] as 'column', null, 
'ID' as 'column/@name', [ID] as 'column', null, 
'NAME' as 'column/@name', [NAME] as 'column', null
FROM TEST_TBL
FOR XML PATH('row'), ROOT('resultset')
)
SELECT @xmldata AS returnXML
:OUT abc.XML
GO

The output file - abc.XML is truncated at the length of 1,048,578 in a single row.

Is there any way to set the XML output file - abc.XML to an 'Unlimited' size as in SSMS?

Thanks for your help!

coconuts
  • 7
  • 4
  • SQL-Server is very limited when you need interaction with the file system. I prefer [bulk copy utility BCP](https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017) for such issues... – Shnugo Nov 03 '18 at 16:11
  • @Shnugo, thanks for your very useful tip. I'll give a try. – coconuts Nov 04 '18 at 18:02
  • @Shnugo, after several trials, BCP works for me now. Thanks for your very helpful tip. – coconuts Dec 02 '18 at 17:21

1 Answers1

0
DECLARE @Path VARCHAR(4000), @AUTCMD VARCHAR(4000), @XMLCMD VARCHAR(4000)
SET @Path = 'YourPathHere'
SET @AUTCMD = 'net use ' + @Path + ' YourPasswordHere /USER:YourUserNameHere' 
SET @XMLCMD = 'bcp "DECLARE @xmldata xml SET @xmldata = (SELECT ''DATE'' as ''column/@name'', [DATE] as ''column'', null, ''ID'' as ''column/@name'', [ID] as ''column'', null, ''NAME'' as ''column/@name'', [NAME] as ''column'', null FROM TEST_TBL FOR XML PATH(''row''), ROOT(''resultset'')) SELECT @xmldata AS returnXML" queryout "' + @Path + '\abc.XML" -S ' + @@SERVERNAME + ' -T -c'


exec xp_cmdshell @AUTCMD
exec xp_cmdshell @XMLCMD
coconuts
  • 7
  • 4