How do I convert the SQL Server XML data type to nvarchar with whitespace
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>
开发者_StackOverflow
How do I do this using SQL Server by itself or with a minimum of new tools?
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();
}
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
精彩评论