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

Question

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.

White space within nodes is generally ignored as generally its only used to improve readability and it's not actually the payload of the document. You should be able to preserve it, see http://www.w3.org/TR/xml11/#sec-white-space, I've not tried with SQL server — joocer, Aug 20 '12 at 09:27
You are right, I had read about that flag but hadn't tried using it. XmlParameter.Value = "Andrew "; Does fix the problem. In my use case i can't always change the incoming XML though. Any other ideas would be awesome! — Tristan, Aug 20 '12 at 09:39
In my use case we are using the XML document as a user specified template to format outgoing messages So i actually need to store the white space. The implementing developer has added the xml:space attribute to his constructed xml now. But the reason i'm asking is that it goes through a very generic DAL for storage, which is where the SQLParameter is used. I think i'm stuck with the INSERT/UPDATE + CONVERT combo for the moment. That way our developers won't need to construct proper XML. — Tristan, Aug 21 '12 at 06:08
This is about the rules for XML, not anything specifically about how ADO.Net/SQL are dealing with it. The XML rules say that whitespace isn't significant in this case, and also that `` and `` are exactly equivalent. If those aren't true for what you're dealing with, then what you're dealing with *isn't* XML - it's a string that closely resembles XML. — Damien_The_Unbeliever, Aug 21 '12 at 06:22

score 1 · Accepted Answer · answered Aug 21 '12 at 10:33

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.

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

1 Answers1