How to cast string to SqlXml

Question

I have a method that returns a well formed Xml string. How do I cast this string to SqlXml ?

Answer 1

Another way in pure C# - for posterity:

using (var memoryStream = new MemoryStream())
{
    using (var xmlWriter = XmlWriter.Create(memoryStream))
    {
        xmlWriter.WriteString(xmlData.ToString().Trim());
        return new System.Data.SqlTypes.SqlXml(memoryStream);
    }
}

Answer 2

I know this has been answered, but neither of the answers seemed to work for me. Instead I took another approach using this oneliner

var sqlXml = new SqlXml(new XmlTextReader(new StringReader(stringToParseToSqlXml)));

However this is leaking, but changing it to:

using (var reader = new StringReader(stringToParseToSqlXml))
{
    using (var xmlreader = new XmlTextReader(reader))
    {
        var sqlXml = new SqlXml(xmlreader);
        // Do other stuff
    }
}

I've used this, and it seems to works for me.

Answer 3

This is my version of a C# conversion method.

public static SqlXml ConvertString2SqlXml(string xmlData)
{
    UTF8Encoding encoding = new UTF8Encoding();
    MemoryStream m = new MemoryStream(encoding.GetBytes(xmlData));
    return new SqlXml(m);
}

I am not quite please with it, because I would have liked to have the MemoryStream with a using block, but that gave me:

Cannot access a disposed object. Object name: 'Invalid attempt to call Read when the stream is closed.'.

I am willing to accept that issue, because this code is only used in unit-tests, where I want to test an SqlFunction with an SqlXml parameter, which will be deployed to the SQL Server CLR.

The answer of ReinhardtB did not work for me, due to the following issues: When calling this with xmlData "<?xml version=\"1.0\" encoding=\"utf-8\"?><test></test>" (the back slashes are in the C# string literal), I got:

System.InvalidOperationException occurred Message=Token Text in state Start would result in an invalid XML document. Make sure that the ConformanceLevel setting is set to ConformanceLevel.Fragment or ConformanceLevel.Auto if you want to write an XML fragment.

This was easily fixed by the following modification:

XmlWriterSettings settings = new XmlWriterSettings();
settings.ConformanceLevel = ConformanceLevel.Fragment;
using (var xmlWriter = XmlWriter.Create(memoryStream, settings))

However, this led to the following exception:

System.ObjectDisposedException occurred Message=Cannot access a disposed object. Object name: 'Invalid attempt to call Read when the stream is closed.'. Source=System.Data

This, I could work-around by omitting the using block for the MemoryStream (which is of course a bad idea), just to hit the next issue: when inspecting the value of SqlXml, SqlXml.Value, I found that it contains the value <?xml version="1.0" encoding="utf-8"?><test></test>, that is, < and > have been escaped! This looks like a serious issue with the SqlXml type.

Answer 4

In SQL Server, a simple: CAST(MyVarcharString AS xml)

Or assign it to the SQLDbType.Xml in .net as, say, a parameter

Answer 5

SqlParameter works fine if you set the correct length.

comm.Parameters.Add("@XML_Field", SqlDbType.Xml, stringXml.Length)
comm.Prepare()
comm.Parameters("@XML_Field").Value = stringXml

And -1 if you want to set DBNull.Value in some cases :

comm.Parameters.Add("@XML_Field", SqlDbType.Xml, -1)
comm.Prepare()
comm.Parameters("@XML_Field").Value = DBNull.Value