CODEWITHSUNDEEP
c#xmlt-sqlado.netsqlparameter
Tristan
Tristan

Reputation: 165

SQLParameter of XML type with whitespace elements, loses the whitespace?

I've got a problem with SQLParameter when I try to use one to store into a column of XML type. I am creating the parameter of SqlDbType.Xml. It appears that any XML Elements that are only white space are converted to closed elements.

This is the same behaviour as 

SELECT CAST('<Cust><Fname>Andrew</Fname><Lname> </Lname></Cust>' as XML) 
VS 
SELECT CONVERT(xml, '<Cust><Fname>Andrew</Fname><Lname> </Lname></Cust>', 1)

I'm surprised to see that as the default behaviour within SQLParameter. Is there some way to make a SQLParameter of SqlDbType.Xml care about its white space? As a shot in the dark i tried setting the Precision to 1, but that didn't help it...

The code below ends up with this in the database

<Cust><Fname>Andrew</Fname><Lname /></Cust>

As you can see it strips out the whitespace XML element

// CREATE TABLE T(c1 int primary key, c2 xml)
static void Main(string[] args) {

using (var Connection = new System.Data.SqlClient.SqlConnection("Data Source=localhost;Initial Catalog=Test;Integrated Security=SSPI;"))
{
    Connection.Open();

    using (var Command = Connection.CreateCommand())
    {
      Command.CommandText = "INSERT INTO T VALUES (1, @s)";

      var XmlParameter = Command.Parameters.Add("s", System.Data.SqlDbType.Xml);
      XmlParameter.Value = "<Cust><Fname>Andrew</Fname><Lname>  </Lname></Cust>";

      Command.ExecuteNonQuery();
    }

    Connection.Close();
}

Does anyone know of a way to solve it that isn't parsing the parameter in as binary and the converting it as part of the insert/update command?

Command.CommandText = "INSERT INTO T VALUES (1, CONVERT(xml, @s, 1))";

Any help or feedback would be greatly appreciated.

Upvotes: 2

Views: 2578

Answers (1)

joocer
joocer

Reputation: 1121

If you're receiving the XML from an external source and it's just starting with the element, you can prefix the string with

<?xml version="1.0"?>
<!DOCTYPE Cust [
<!ATTLIST Cust xml:space (preserve) #FIXED 'preserve'>
]>

This should make the entire document (the Cust root node down) preserve whitespace. If you're only interested in specific fields then instead of the ATTLIST saying Cust, add a new ATTLIST for each of the elements you want to preserve the whitespace in. e.g.

<!ATTLIST Fname xml:space (preserve) #FIXED 'preserve'>
<!ATTLIST Lname xml:space (preserve) #FIXED 'preserve'>

Again, I've not tried with SQL server (I just don't have access to one) but the XML processor in IE and Chrome add the xml:space="preserve" to the document as expected which from what you'd said above gave you the behaviour you were looking for. This approach does it without having the deeply manipulate the XML, you're just adding some processing directives to the beginning of the document.

Upvotes: 1

Related Questions