"Measure all that is measurable and attempt to make measurable that which is not yet so.

-- Galileo (1564 to 1642)"

The XML datatype makes a bunch of unmeasurable stuff measurable because it allows you to store and read XML. That may not sound like a big deal until you look under the covers you will discover that almost everything is stored as XML. Thanks to the XML datatype Word Documents*, Excel Files*, web pages (you are looking at XML right now)**, SQL traces, SSRS Report files (RDL), SSIS packages, RSS feeds, whatever.config, etc, etc, etc, etc, etc can be read, manipulated and created using T-SQL.

For fun, here's a query that will read data from a table and return an MS Word Document:

First let's setup the sample data:

IF OBJECT_ID('tempdb..#testdata') IS NOT NULL

DROP TABLE #testdata;

CREATE TABLE #testdata (id int primary key, txt varchar(100) NOT NULL);

INSERT INTO #testdata

SELECT 1,'Paragraph 1...' UNION ALL

SELECT 2,'Paragraph 2...' UNION ALL

SELECT 3,'Paragraph 3...';

Query that turns that data into a Word Doc:

DECLARE @pars AS varchar(1000)='',

@wordDoc varchar(1000)='<!--?-->

<w:wordDocument xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml">

<w:body><!--x--></w:body>

</w:wordDocument>';

WITH data(d) AS

(SELECT '<w:p><w:r><w:t>'+CAST(id AS char(1))+': '+txt+'</w:t></w:r></w:p>'+CHAR(13)

FROM #testdata)

SELECT @pars=@pars+d+CHAR(13)

FROM data;

SELECT REPLACE(REPLACE( @wordDoc,'<!--?-->',

'<?xml version="1.0" encoding="ISO-8859-1"?>'+CHAR(13)+

'<?mso-application progid="Word.Document"?>'),

'<!--x-->',CHAR(13)+@pars);

Results:

<?xml version="1.0" encoding="ISO-8859-1"?>

<?mso-application progid="Word.Document"?>

<w:wordDocument xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml">

<w:body>

<w:p><w:r><w:t>1: Paragraph 1...</w:t></w:r></w:p>

<w:p><w:r><w:t>2: Paragraph 2...</w:t></w:r></w:p>

<w:p><w:r><w:t>3: Paragraph 3...</w:t></w:r></w:p>

</w:body>

</w:wordDocument>

If you copy/paste the result set into notepad and save it as, say, wordDoc.xml you will see this (if you have MS office installed):

When you open it will look like this:

You could save it as a docx file or you could automate the whole thing.

* Find a .docx (Word) or .xlsx (Excel) file > change the extension to .zip and open it up :w00t:

** HTML 4.01 and higher must be well-formed XML