CODEWITHSUNDEEP
c#sql-serverxmlado.net
ca9163d9
ca9163d9

Reputation: 29159

XML assigned to SqlXml parameter got extra-encoded?

The following code passes an XML parameter to a stored procedure. The SqlXml is converted from a string.

var range = GetXml("<Range><Column Name=\"Id\" Low=\"5002\" High=\"8076\" /></Range>");
cmd.Parameters.Add(new SqlParameter("@Range", SqlDbType.Xml) { Value = range });

SqlXml GetXml(string s)
{
    var memoryStream = new MemoryStream();
    var settings = new XmlWriterSettings();
    settings.OmitXmlDeclaration = true;
    settings.ConformanceLevel = ConformanceLevel.Fragment;

    using (var xmlWriter = XmlWriter.Create(memoryStream, settings))
    {
        xmlWriter.WriteString(s);
        return new SqlXml(memoryStream);
    }
}

However, it turns out the actual XML value passed to the stored procedure is

N'&lt;Range&gt;&lt;Column Name="Id" Low="5002" High="8076" /&gt;&lt;/Range&gt;'

The value was captured using SQL Server Profiler.

How can I remove the extra encoding of < and >?

Upvotes: 1

Views: 225

Answers (2)

shA.t
shA.t

Reputation: 16958

A secure way for casting strings is to use an encoding for example:

public static SqlXml GetXml(string s)
{
    var encoding = new UTF8Encoding();
    var memoryStream = new MemoryStream(encoding.GetBytes(s));
    return new SqlXml(memoryStream);
}

Upvotes: 1

Michael Liu
Michael Liu

Reputation: 55339

Don't use XmlWriter.WriteString. The purpose of this method is to add plain text content to an element, so the method replaces &, <, and > characters with &amp;, &lt;, and &gt;.

To treat the string as XML instead of plain text, you can create an XmlReader from the string and then pass the XmlReader to the SqlXml(XmlReader) constructor:

// using System.Data.SqlTypes;
// using System.IO;
// using System.Xml;

static SqlXml GetXml(string s)
{
    return new SqlXml(XmlReader.Create(new StringReader(s)));
}

Upvotes: 1

Related Questions