0

I need to generate an XML file from a SQL Server database daily and save it on the local machine. What is the way to do it other than using FOR XML clause?

Apart from being unable to output the result to a file, generating the file using this method only works correctly for smaller datasources. I've tried running the script for a datasource that contains 150k+ rows and the generated file just gets cut at some point and SSMS warns me about the file being too big.

I've tried setting the Maximum Characters Retrieved value to unlimited, but it doesn't really make any difference.

What is the best way to solve this problem?

Dale K
  • 25,246
  • 15
  • 42
  • 71
bapster
  • 151
  • 8
  • Showing your attempts and the actual error will help here. If you really are creating a an XML string that's too big, that means it's **over** 2GB in size. Is that really the case? That would be a HUGE XML file. – Thom A May 15 '19 at 07:52
  • Is it obligate to use SSMS in order to get your XML? Did you try using SQL Server agent job. –  May 15 '19 at 07:54
  • 2
    SSMS output is limited in number of characters. That is not the tool for your job. Perhaps [SSIS](https://learn.microsoft.com/en-us/sql/integration-services/sql-server-integration-services) is a better tool for your needs. – TT. May 15 '19 at 08:02
  • Please check https://stackoverflow.com/questions/2742651/xml-output-is-truncated-in-sql – Renat May 15 '19 at 08:02
  • @TT. I thought of using SSIS package, but is it possible to generate an XML file using the standard components? – bapster May 15 '19 at 08:10
  • 1
    [Look around](https://duckduckgo.com/?q=export+XML+file+using+SISS) – TT. May 15 '19 at 08:13
  • Besides SSIS, SQL Agent job, you can accomplish your task using SQLCLR. It means creating SP or Scalar Function that will receive T-SQL query as input parameter and return XML. I developed a solution that will return HTML. Check out my article on SQL Server Central https://www.sqlservercentral.com/articles/practical-usage-of-sqlclr-building-querytohtml-function –  May 15 '19 at 08:16
  • You might create a SP, VIEW, TVF or UDF to create your result and call this from any external tool you know. PowerShell was my choice probably... – Shnugo May 15 '19 at 08:36
  • 1
    You want to put 150K rows in an XML file? Something will need to go and decode that at the other end right? XML is a bad bulk data transmission format – Nick.Mc May 15 '19 at 08:40
  • One way to export 150K rows is simply export a file with BCP.EXE then whack some tags on the front and back. Hey presto! XML! – Nick.Mc May 15 '19 at 08:46
  • Ok, I've managed to export the xml using SSIS, however the content is not in a pretty format. You guys know any tools that could make the content look more friendly? Already tried XML tools from notepad++ plugins, but it crashes or freezes for a couple of minutes and leaves no changes :/ – bapster May 15 '19 at 11:23
  • 1
    XML, though readable in a sense, should not be in a "readable" (pretty-printed) format for it to be processed by other tools/programs. You could [look around](https://duckduckgo.com/?q=pretty+print+xml+free) for some tools. The one I typically use (XMLSpy) isn't free. Not sure if it would work fast for huge XML files (never done for very large XML files). – TT. May 15 '19 at 11:27
  • What is the purpose of this XML? If it's valid XML readable doesn't come into it - it's not for reading, it's for data interchange. You could try visual studio code to read it. – Nick.Mc May 16 '19 at 10:33
  • @Nick.McDermaid Yeah, it's a valid XML and it's going to be read by some external software. I just wanted to have it pretty printed for the purpose of testing. Anyway, the problem is solved. Thanks for your effort, guys :) – bapster May 16 '19 at 14:19
  • You can keep using the FOR XML clause, but you should use another tool instead of SSMS. You can also export an XML file using BCP (without the size limitations of SSMS). – Razvan Socol May 17 '19 at 04:50

0 Answers0