XML in SQL Server - Indexes

You MUST add indexes to an XML column in SQL Server. If you don't do that, queries will soon become very slow. Just run these statements:


CREATE PRIMARY XML INDEX primaryidx ON [table](XmlColumnname)
GO


CREATE XML INDEX pathidx ON [table](XmlColumnname)
USING XML INDEX  primaryidx  FOR PATH
GO


CREATE XML INDEX valueidx ON [table](XmlColumnname)
USING XML INDEX  primaryidx  FOR VALUE
GO


CREATE XML INDEX propidx ON [table](XmlColumnname)
USING XML INDEX  primaryidx  FOR PROPERTY
GO

XML in SQL Server - Inner join

There are a few ways to do inner joins from an XML column to another table. This is the easy way which delivers very good performance:
select *
from [table1] t1
inner join [table2] t2
on  t1.[XmlColumn.exist('/myrootelement/subelement[.=sql:column("t2.id")]') = 1

XML in SQL Server - Query datetime

I have XML stored in a SQl database like this:



<myrootelement xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <product>something</product>
  <deliverydate>2024-12-26T21:39:24</deliverydate>
</myrootelement>



Let's say I have 10,000 records like the above, and want to get the count of all records with a deliverydate greater than the current date. How can we do that?


First make sure there's a correct schema attached to the column in which the xml is stored. Then XQuery can be used to select the records. The fastest way to query XML in SQL Server is to use the 'exist' function:

SELECT count(*) FROM [table]

WHERE [column].exist('/ myrootelement[deliverydate > xs:dateTime(''2012-07-15T00:00:00'')]') = 1



The function xs:dateTime() is used to parse a string to a datetime value.  Please note that two single quote signs are written, and not a double quote sign.


Using parameters
What if we don't want to use a hardcoded value, but a parameter? I thought that would be pretty straightforward, so I wrote this query:

declare @rcurrentdate datetime; 

set @currentdate = getdate()

SELECT  count(*)  FROM [table]

WHERE [column].exist('/ myrootelement[deliverydate > xs:dateTime('' + @currentdate + '')]') = 1



But this resulted in an error:



Msg 9319, Level 16, State 1, Line 16
XQuery [Documenten.XmlStuurinformatie.exist()]: Static simple type validation: Invalid simple type value ' + @stringdate + '.

Changing the number of single quote signs didn't matter:

SELECT  count(*)  FROM [table]

WHERE [column].exist('/ myrootelement[deliverydate > xs:dateTime(' + @currentdate + ')]') = 1

Msg 8172, Level 16, State 1, Line 15

The argument 1 of the XML data type method "exist" must be a string literal.

or

SELECT  count(*)  FROM [table]

WHERE [column].exist('/ myrootelement[deliverydate > xs:dateTime(''' + @currentdate + ''')]') = 1

Msg 8172, Level 16, State 1, Line 15

The argument 1 of the XML data type method "exist" must be a string literal.

The right way
So how can we use a parameter in an XQuery? By telling the query to use a SQL parameter. For example:

declare @rcurrentdate datetime; 

set @currentdate = getdate()

SELECT  count(*)  FROM [table]

WHERE [column].exist('/ myrootelement[deliverydate > xs:dateTime(sql:variable("@currentdate"))]') = 1

XML Serialization with C#

There are a lot of ways to serialize and deserialize objects into xml strings. Some are easier than others, and some will give you headaches by unintelligible exceptions. In this blog I'll show you how I serialize and deserialize XML.

Let's first create basic object that we want to serialize:

public class MyObject

{

   public string Number{ get; set; }

   public string Status { get; set; }

   public bool IsSomething { get; set; }

}

When we want to serialize this object we basically have two serializers we can choose: XmlSerializer and DataContractSerializer. I prefer the latter. The best desciption of the differences between the two can be found here: XmlSerializer vs DataContractSerializer

When using DataContractSerializer we have to mark our object with DataContract, and all properties we want to serialize must be marked with DataMember, like this:

[DataContract]

public class MyObject

{

   [DataMember]

   public string Number{ get; set; }

   [DataMember]

   public string Status { get; set; }

   public bool IsSomething { get; set; }

}

As you can see the IsSomething property has no DataMember attribute, and therefore will not be serialized.

You can set the name of the class and properties in XML, like this:

[DataContract(Name = "myobject", Namespace = "")]

public class MyObject

{

   [DataMember(Name = "nr")]

   public long Number{ get; set; }

   [DataMember(Name = "status")]

   public string Status { get; set; }

}

Serialize object into xml string

The class MyObject can be serialized this way:

var myObject = new MyObject {Number = 97, Status = "complete"};

var stream = new MemoryStream();

var wr = XmlWriter.Create(stream);

var serializer = new DataContractSerializer(typeof (MyObject));

serializer.WriteObject(wr, myObject);

wr.Close();

Encoding utf8Encoder = new UTF8Encoding(false);

var result = utf8Encoder.GetString(stream.ToArray());

After running this code, result will look like this:

<?xml version="1.0" encoding="utf-8"?><myobject xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/ConsoleApplication1"><nr>97</ nr ><status>complete</status></myobject >

The code is pretty straightforward, except for one thing:

Encoding utf8Encoder = new UTF8Encoding(false);

var result = utf8Encoder.GetString(stream.ToArray());

Why don't I simply do this:

var result = Encoding.UTF8.GetString(stream.ToArray());

The code above will put a Byte Order Mark at the beginning of the xml string. It can't be seen in the Visual Studio debugger, but if you copy-paste the result into a text editor it will look like this:

?<?xml version="1.0" encod[...]

The ? is the Byte Order Mark. If you try to deserialize an xml string with a Byte Order Mark you will get this exception:

Data at the root level is invalid. Line 1, position 1

That why we use this code:

bool addByteOrderMark = false;

var utf8Encoder = new UTF8Encoding(addByteOrderMark);

var result = utf8Encoder.GetString(stream.ToArray());

Deserialize xml string into object

Let's begin with the xml string we serialized:

var xmlstring = "<?xml version=\"1.0\" encoding=\"utf-8\"?><myobject xmlns:i=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns=\"http://schemas.datacontract.org/2004/07/ConsoleApplication1\"><nr>97</ nr ><status>complete</status></myobject >";

Now we can easily deserialize this into an object:

MyObject myObject;

using (var ms = new MemoryStream(Encoding.UTF8.GetBytes(xmlstring)))

{

   var serializer = new DataContractSerializer(typeof (MyObject));

    myObject = (MyObject) serializer.ReadObject(ms);

}

Deserialize xml string into XDocument

Last thing I want to do is serialize an xml string into an XDocument, which we can query with Linq.

Let's start again with the xml string we serialized:

var xmlstring = "<?xml version=\"1.0\" encoding=\"utf-8\"?><myobject xmlns:i=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns=\"http://schemas.datacontract.org/2004/07/ConsoleApplication1\"><nr>97</ nr ><status>complete</status></myobject >";

It's also pretty straightforward to deserialize this into an XDocument:

XDocument xDocument;

using (var ms = new MemoryStream(new UTF8Encoding(false).GetBytes(xmlString)))

{

   using (var reader = new XmlTextReader(ms))

   {

      xDocument = XDocument.Load(reader);

   }

}

And there you have your XDocument.