Skip to main content

Posts

Showing posts from February, 2008

Detecting Duplicate XML Data in SQL Server

I've been working quite a bit with XML in SQL Server lately (I'll try to do a post on some xquery stuff at some point), and I had a need to check XML data that I'm pulling off disk against a table in SQL Server to see if the data I pulled off disk is a duplicate with data already in the database.
The problem I ran into is that SQL Server "collapses" empty XML nodes when you insert data as XML (e.g. <myXmlNode><myXmlNode/> is turned into <myXmlNode/>, by SQL Server) so if the XML you're checking against hasn't gone through this collapsing process, you won't find duplicates accurately.
The solution turned out to be pretty simple and was suggested to me by a co-worker. First, you can't compare XML to XML directly in a query because, like any binary datatype in SQL Server, the = operator can't be used. Given the issue outlined above, you also can't just convert the XML in the database and the XML from disk into nva…

Dealing With Null Values in ColdFusion

The lack of nulls is a well-known ... shall we say "quirk" of ColdFusion.  In most cases it's a minor annoyance, but it's one of those things we just have to deal with. Recently I've been working on a rather large project for which the data integrity is absolutely critical (and I mean critical), and I'm starting to be increasingly annoyed by CF's lack of nulls, not only because of the extra work involved, but because in some cases it leads to unresolvable situations in which the data will not accurately reflect its true state.First, a little background for those of you who may not be familiar with what I'm talking about. In many languages, such as Java (I'll use that as a point of reference since that's the language I have the most experience with other than ColdFusion), there is the concept of null, which in its simplest terms means "nothing." For example, if variable x is an integer, it has two possible states in stron…

Getting XML Text From XML Nodes

I've been working with XML very heavily the past couple of weeks, and for whatever reason this is just one of those things I haven't had to do a whole lot of until now. Don't get me wrong, XML comes up pretty regularly for most people, but I haven't had to live and breathe it this much before.
As with most things in ColdFusion it's incredibly easy to jump in and get going, and for what I'm doing anyway, XmlSearch makes it absolutely trivial to tear through the XML and get the data I need.
I did run into one annoyance and thought I'd see if anyone had a better solution. Again, since I seem to have dodged working with XML this heavily until now I could just be missing something. Specifically I'm referring to the seeming necessity of two lines to get at most things when you use XmlSearch. If you've worked with XML much in CF you probably already know what I mean. Let's assume there's a single node (meaning really a single element array…