Obscure Problems and Gotchas

Introduction

Using XML with tSQL

Reading in a list of elements (The Micky Mouse Example)

DECLARE @XMLlist XML = '<list><i>2</i><i>4</i><i>6</i><i>8</i><i>10</i><i>15</i><i>17</i><i>21</i></list>'

SELECT @XMLList

SELECT r.c.value('.','int') AS IDs
FROM @XMLList.nodes('/list/i') AS r ( c )

As you can see there is a list of integers being set into an XML parameter. Using the nodes method with it’s XQuery string parameter; the XML is translated into a list of integers by reading each row and the column. Each column’s content is converted to an integer using the value method. This is as basic as it gets, it is useful to play around with, but not useful if you want to pass a multidimensional object (object with multiple properties) aka a matrix. This example is only showing how to convert a list of scalar values – which is definitely not the case most of the time.

Output in SSMS

Passing in an object (More realistic and less “hello worldy”)

As I have pointed out in the previous example, that is just working with scalars. When dealing with objects you are dealing with a matrix or an object with multiple properties. Therefore this example shows you how to read in XML that represents a simple object.

-- Simple object example
DECLARE @xml XML = '<obj><a>John</a><b>Smith</b><c>10</c></obj>'

SELECT @xml

-- Method 1: Full path to each value
SELECT 
 @xml.value('(/obj/a)[1]','varchar(10)'),
 @xml.value('(/obj/b)[1]','varchar(10)'),
 @xml.value('(/obj/c)[1]','int')

-- Method 2: Selecting the base node
SELECT 
 r.c.value('(./a)[1]','varchar(10)'),
 r.c.value('(./b)[1]','varchar(10)'),
 r.c.value('(./c)[1]','int')
FROM @xml.nodes('/obj') AS r(c)

-- Method 3: Individual hits
SELECT r.c.value('.','varchar(10)') FROM @xml.nodes('/obj/a') AS r(c)
SELECT r.c.value('.','varchar(10)') FROM @xml.nodes('/obj/b') AS r(c)
SELECT r.c.value('.','int') FROM @xml.nodes('/obj/c') AS r(c)

The above is demonstrating three different ways to accomplish the same end result (for the most part). Let’s explore each of these methods (the SSMS output is below this list):

1. Full path to each value – Notice there is no FROM clause, this is because the XQuery in the value method is pointing directly to a node using a full path. Notice that there is a “[1]” after value – this isn’t a mistake, it is for selecting the first element found in “node a”.
2. Selecting the base node – Notice that there is a FROM clause this time. The reason for this is to declare the path being queried using the nodes method a single time instead of repeatedly declaring it in the value method. This can be more useful for longer paths. The syntax “r(c)” denotes row [r] and column [c] – you can use any letters you want, some people use x and y – I just think r and c makes more sense.
3. Individual hits – this is the method being used for the Mickey Mouse example above, notice how inefficient this is with respect to traversing an object – this method requires three queries instead of one and it returns three virtual tables which isn’t terribly useful for querying and I will further show why down below. This is why showing someone how to select a list of integers isn’t an optimal way of explaining to them how to use XML with tSQL.

select
 TABLE_NAME,
 xQueryPath = 'r.c.value(''(./' + column_name + ')[1]'',''' + data_type + '(' + CAST(ISNULL(Character_maximum_length, 0) as varchar(10)) + ')''),'
from YourDatabaseNameHere.information_schema.columns

This query will produced XQueries for each column in all of the tables in your database. The output is just starter code – you still need to manipulate it to make it work properly. This is better than writing it by hand.

The Hard Coding Dilemma and Trade Off at the Cost of Speed

Whenever people would argue with me about hard coding my insert and update queries in code as in-line SQL (assuming the absence of an ORM or Generated Service Layer), everyone always falls back to saying – “hey just use XML”, but I would always throw into their face “Okay, so you want me to hard code XML instead? How is that better?” to which I always just get a broken and distant look. The point is you are going to be hard coding something UNLESS you use XML serialization or some other form of custom serialization – all of which inevitably use reflection.

Reflection will ALWAYS make things slower – however it is far more convenient, almost maintenance free and safer than hard coding XML. This convenience comes at an inconvenience though – the cost of a small performance decrease, but if the point here was to stop the taboo of hard coding SQL in code, then you cannot be a hypocrite and hard code XML instead – that’s just pointless and stupid.

Code for Executing a Non-Query by passing a Target Object as XML

This is code that I wrote for passing a target object as XML to a target stored procedure regardless of what it does. As you have read ad nauseam by this point, the target object is serialized into XML using Magic unless you read the next section to understand how this is done.

//Using blocks required
using System.IO;
using System.Xml;
using System.Xml.Serialization;

protected int ExecuteNonQuery<T>(T anyObject, string storedProcedureName)
{
 SQLText = ConstructXmlArguments(anyObject, storedProcedureName);

 //This method is not provided to you in this example - use a basic Execute Non Query method here
 return ExecuteNonQuery();
}

protected int ExecuteInsert<T>(T anyObject, string storedProcedureName)
{
 SQLText = ConstructXmlArguments(anyObject, storedProcedureName);

 //This method is not provided to you in this example - use a basic Execute Insert or Execute Scalar method here
 return ExecuteScalar().ConvertToInt32();
}

//This method is fully functional on its own and does not need to be modified
public static string SerializeToXmlString<T>(T anyObject)
{
 string strXML = string.Empty;

 XmlSerializer ser = new XmlSerializer(typeof(T));

 //Setting the namespace to nothing on purpose
 XmlSerializerNamespaces ns = new XmlSerializerNamespaces();
 ns.Add(string.Empty, string.Empty);

 //Setting the rest of the xml writer settings to remove inconvenient parts for tSQL
 XmlWriterSettings settings = new XmlWriterSettings();
 settings.Indent = false;
 settings.NewLineHandling = NewLineHandling.None;
 settings.OmitXmlDeclaration = true;

 //Write everything into memory
 using (MemoryStream ms = new MemoryStream())
 {
  //Write the XML to memory
  using (XmlWriter xmlWriter = XmlWriter.Create(ms, settings))
  {
   ser.Serialize(xmlWriter, anyObject, ns);
  }

  //Rewind
  ms.Position = 0;

  //Read the stream into a string
  using (StreamReader sr = new StreamReader(ms))
  {
   strXML = sr.ReadToEnd();
  }
 }

 return strXML;
}

Please note that this is the partial implementation and that this code was taken from a DAL that I wrote. You need to retrofit it to work with your DAL that you intend on using it with, the above code is for basic idea and reference only.

SerializeToXmlString Method

<?xml version="1.0" encoding="utf-16"?>
<Bob xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Name>Bob the 5th</Name>
  <MaryInstance>
    <IsMary>true</IsMary>
  </MaryInstance>
  <MaryInstance2>
    <IsMary>false</IsMary>
  </MaryInstance2>
  <Age>62</Age>
</Bob>

<Bob><Name>Bob the 5th</Name><MaryInstance><IsMary>true</IsMary></MaryInstance><MaryInstance2><IsMary>false</IsMary></MaryInstance2><Age>62</Age></Bob>

public class Bob
{
 public string Name { get; set; }
 
 public string Occupation { get; set; }
 
 public int Age { get; set; }
 
 public string Mood { get; set; }
 
 public DateTime CreatedOn { get; set; }
}

<Bob>
 <Name>Bob the 5th</Name>
 <Occupation>Fast Food Engineer</Occupation>
 <Age>40</Age>
 <Sex>None Yet</Sex>
 <Mood>Resentful of others</Mood>
 <CreatedOn>2014-11-18T22:30:42.4817743-04:00</CreatedOn>
</Bob>

CREATE PROCEDURE [dbo].[Bob_NewBob]
 @parameters XML
AS
BEGIN
 SET NOCOUNT ON;

    INSERT INTO [dbo].[Bob]
           ([name]
           ,[occupation]
           ,[age]
           ,[sex]
           ,[mood]
           ,[createdOn])
     SELECT 
  r.c.value('(./Name)[1]','varchar(100)'),
  r.c.value('(./Occupation)[1]','varchar(100)'),
  r.c.value('(./Age)[1]','int'),
  r.c.value('(./Sex)[1]','varchar(20)'),
  r.c.value('(./Mood)[1]','varchar(255)'),
  CAST(r.c.value('(./CreatedOn)[1]','varchar(35)') as datetime2)
 FROM @parameters.nodes('/Bob') AS r(c)
END