mdq.xmlTransform can do amazing things. Today I will show you how to write a T-SQL query that returns a Microsoft Office Word document viewable in Word 2003 and later (and most programs that open .doc or .docx files). With mdq.xmlTransform it's very easy. If you don't yet have it, see Setting up mdq.XmlTransform for DDL and setup instructions; it takes less than a minute to setup.
Let's get right to it
Some may be intimidated at the idea of writing a query that returns an MS Word document. Don't be, it's simple. That's why I'll keep this real short and just show you.
(1) A Quick WordProcessingML (WordML) tutorial
1. Open notepad (Start > Run > notepad.exe)
2. Copy/paste the code below into your blank notepad file:
3. Go to file > save as and for "Save as Type" select All Files
4. For the file name type helloWord.xml
5. Click Save then close the file.
Now open the file with Microsoft Word version 2003 or later and you should see this:
Congrats! You just created your first word document using WordML.
(2) Using mdq.xmlTransform to produce a Word document
SQL Server supports the XML Data Type. WordML is XML. mdq.xmlTransform transforms XML. Now that you have seen how WordML works lets write a SQL query that returns a Word Document.
Using a database with mdq.xmlTranform, copy/paste and execute this code into SSMS:
You should already know what will happen if you: copy/paste the result set into a new notepad file, save it like we did earlier, and then open it with Word (but feel free to do it again if you thought it was cool).
...and now for something a little more impressive...
Hopefully some developers, DBAs and BI people see the potential here. Think of how many SQL objects that are (or can be) stored in XML format. Query plans, traces, SSRS reports (RDL) and SSIS packages (DTSX packages) to name a few. Word and Excel files, your whatever.config files, a multitude of SharePoint objects, RSS feeds, web service data, Extended Events, etc, etc... All XML. Thanks to the XML data type and mdq.xmlTransform, information from all these things can be stored, measured, analyzed and, as I will show you in a moment, stuffed into XML files or fragments.
(3) Create a Word doc with SSRS Report (RDL) data
The code below contains two XML documents. For the first (1) I grabbed an some data from an SSRS RDL file (just part of it to keep things simple). Using T-SQL REPLACE I removed the rd: namespace references. The second (2) is an XML transform that will extract the Data Provider and Connection String from an SSRS report and use it to create a Word Doc.
If we feed that RDL data and transform to mdq.xmlTransform like this:
We get this:
mdq.xmlTransform is a powerful tool. Period. Today I showed you how to create a basic Word document using just T-SQL and mdq.xmlTransform. Thanks for reading!
Last Updated: 4/29/2013 11:35am (Posted, fixed code issues)