Fuzzy Data Matching with SQL

Chapter 13. Code Is Data, Too!

This chapter is interesting for those who may not have any use for examples about customer data. It just might expand your concept of “data” and the kinds of fuzzy things we can do with it in SQL! It includes a second case study based on work I have done repeatedly for employers and clients over the years. We’re going to approach it stepwise, all aimed at looking at code as data: data that can be read and written!

Working with XML Data

Often you get your import data as a CSV, tab-separated values (TSV), or Excel file. Sometimes there are other more esoteric delimiting schemes. Perhaps the most esoteric textual data format ever devised was XML. I won’t go into the depths of how to deal with it with namespaces, entity encodings, all that. Once you understand the basics like XPath, the SQL documentation is easy enough to read. I just want to point out that if you get some XML data, via a file transfer, web service response payload, or whatever, the first thing you can do is import it into a SQL table and then ETL out of that. Let’s check it out:

CREATE TABLE staging.XMLDemo
(
    ImportLine XML NULL
);

INSERT INTO staging.XMLDemo
VALUES
(
    '<Customer>
         <LastName>Snedley</LastName>
         <FirstName>Mortimer</FirstName>
     </Customer>'
);

SELECT * FROM staging.XMLDemo;

ImportLine
<customer><lastname>Snedley</lastname><firstname>Mortimer</firstname></customer>

Note that in tools like SQL Server Management Studio (SSMS), the preceding result would ...