How do I convert the SQL Server XML data type to nvarchar with whitespace.

Question

I am generating a very large dataset into an XML file to send to an external web service. This file is about 20 megabytes and has a validation error somewhere near character 18995504 of the only line in the file.

DECLARE @Text nvarchar(MAX)

SET @Text = (SELECT xml FROM (...) multiLeveledQueryFromHell)
SET @Text = '<root xmlns="urn:examplenamespace" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:schemaname urn:schemaurl">' + @Text + '</root>'
EXECUTE WriteToFile(@Filename, @Text)

The conversion from xml to nvarchar(MAX) leaves me with a string like <root ...><elements>...</elements></root>. I want to get it in the multi-line tabbed version

<root ...>
  <elements>
    ...
  </elements>
</root>

How do I do this using SQL Server by itself or with a minimum of new tools?

Answer 1

I guess you have to loop through the xml yourself and add CHAR(13) + CHAR(10) If you only need a file or two (in other words, you don't need to do this programatically), you can use Visual Studio. Paste the XML and press: CTRL + K, CTRL + D

Answer 2

This little snippet of C# worked fast and efficiently enough to do the convert outside of SQL Server. Maybe there's something that can be done with a CLR assembly inside SQL Server dealing with the Xml datatype directly.

static void Main(string[] args)
{
    XmlTextReader reader = new XmlTextReader(Console.In);
    XmlTextWriter writer = new XmlTextWriter(Console.Out);

    writer.Formatting = Formatting.Indented;

    XmlDocument document = new XmlDocument();
    document.Load(reader);
    document.WriteTo(writer);

    reader.Close();
    writer.Close();
}