the xmlsqlninja: SSIS

Showing posts with label SSIS. Show all posts

Tuesday, May 21, 2013

Reading XML Data

Intro

In this post I will demonstrate how to query XML data. This is one of those things that is much easier to learn by example so I provided five examples. I won't get into how to get the data in or out of SQL Server because that topic is outside the scope of this article. All you need to know is that it's easy if you know a little WMI scripting, powershell or .NET.

Examples

In the examples below I will read XML data from:

An XML fragment from a MS Word Doc
An XML fragment from an SSRS Report
An event from the Windows Event Viewer
An XML fragment from an SSIS package
An XML fragment from a SQL trace

For these examples I will not include the entire XML document because the XML can be quite verbose. Instead I am using XML fragments (a portion of the entire XML document). This way you can get the general idea without having to copy/paste a ton of XML.

Setting up the sample data

Use this code:

/*******************************************************
(0) Create
some sample data
 
Note: For simplicity I am only
including XML fragments and 
I removed the namespaces, namespace
declarations, etc. 
*******************************************************/
IF OBJECT_ID('tempdb..#data') IS NOT NULL
       DROP TABLE #data;
 
CREATE TABLE #data (id int primary key, xmldesc
varchar(100)
not null, doc xml not null);
 
DECLARE @worddoc
xml='
<wordDocument>
<body>
       <p><r><t>1:  Paragraph 1...</t></r></p>
       <p><r><t>2:  Paragraph 2...</t></r></p>
       <p><r><t>3:  Paragraph 3...</t></r></p>
</body>
</wordDocument>';
DECLARE @ssrs_report
xml=
REPLACE(CAST(
'<Report>
       <DataSources>
              <DataSource Name="QuestNTDev">
                     <DataSourceReference>Something_Dev</DataSourceReference> 
                     <rd:SecurityType>None</rd:SecurityType>
                     <rd:DataSourceID>54f84859-75fd-45a8-a5b7-5f6fec3ede7b</rd:DataSourceID>
              </DataSource>
       </DataSources>
       <DataSets> 
              <DataSet Name="ds_p_major_region"/>
              <DataSet Name="ds_p_country_code"/>
              <DataSet Name="ds_p_acct_group"/>
              <DataSet Name="ds_p_currency_code"/>
              <DataSet Name="ds_p_profit_center"/>
              <DataSet Name="ds_p_office_id"/>
              <DataSet Name="ds_p_total_by_office"/>
              <DataSet Name="ds_p_subtotal_on_summary"/>
              <DataSet Name="ds_get_spid"/>
              <DataSet Name="ds_output"/>
       </DataSets>
</Report>' AS
varchar(1000)),'rd:','');
DECLARE @events xml='
<Events>
       <Event>
              <System>
                     <Provider
Name="MSSQL$SQL2012"/>
                     <EventID Qualifiers="16384">17137</EventID>
                     <TimeCreated SystemTime="2012-05-10T16:18:53.000000000Z"/>
                     <EventRecordID>16563</EventRecordID>
                     <Channel>Application</Channel>
                     <Computer>SQLSERVER1</Computer>
              </System>
              <EventData>
                     <Data>ReportServer$SQL2012TESTTempDB</Data>
                     <Binary>F14200000A000000...</Binary>
              </EventData>
       </Event>
       <Event>
              <System>
                     <Provider
Name="MSSQL$SQL2012"/>
                     <EventID Qualifiers="19999">1000225</EventID>
                     <TimeCreated SystemTime="2013-05-6T16:18:53.000000000Z"/>
                     <EventRecordID>16999</EventRecordID>
                     <Channel>Application</Channel>
                     <Computer>SQLSERVER1</Computer>
              </System>
              <EventData>
                     <Data>blah</Data>
                     <Binary>F14200000A000000...</Binary>
              </EventData>
       </Event>
</Events>';
DECLARE @ssis_package
xml=
REPLACE(CAST(
'<DTS:Executable DTS:ExecutableType="SSIS.Package.2">
 
<DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property>
 
<DTS:Property DTS:Name="VersionComments"></DTS:Property>
 
<DTS:Property DTS:Name="CreatorName">DOMAIN\username</DTS:Property>
 
<DTS:Property DTS:Name="CreatorComputerName">AJBTEST</DTS:Property>
 
<DTS:Property DTS:Name="CreationDate"
                DTS:DataType="7">11/25/2012 3:35:28 PM</DTS:Property>
</DTS:Executable>
' AS varchar(1000)),'DTS:','');
DECLARE @sqlTrace
xml='
<TraceData>
 
<Events>
   
<Event id="65534" name="Trace Start">
     
<Column id="14" name="StartTime">2013-05-10T17:21:28.007-05:00</Column>
   
</Event>
    <Event id="13" name="SQL:BatchStarting">
     
<Column id="1" name="TextData">SELECT
1</Column>
     
<Column id="3" name="DatabaseID">7</Column>
     
<Column id="35" name="DatabaseName">ajbTest</Column>
     
<Column id="12" name="SPID">60</Column>
      <Column id="14" name="StartTime">2013-05-10T17:21:48.917-05:00</Column>
     
<Column id="26" name="ServerName">MYSERVER\SQL2012Express</Column>
   
</Event>
   
<Event id="65533" name="Trace Stop">
     
<Column id="14" name="StartTime">2013-05-10T17:21:54.46-05:00</Column>
   
</Event>
 
</Events>
</TraceData>';
 
INSERT INTO #data 
       SELECT 1, 'An XML fragment from a MS Word Doc',  @worddoc UNION ALL 
       SELECT 2, 'An XML fragment from an SSRS Report', @ssrs_report
UNION ALL 
       SELECT 3, 'An event from the Event Viewer',             @events UNION ALL 
       SELECT 4, 'An XML fragment from an SSIS pkg',    @ssis_package UNION ALL 
       SELECT 5, 'An XML fragment from a SQL trace',    @sqlTrace;

The contents of #data will look like this:

Reading XML data

Note my comments in the code

/*******************************************************
(1) Reading from a word doc
*******************************************************/
SELECT p.value('(r/t/text())[1]', 'varchar(20)') AS p_num
FROM #data
CROSS APPLY doc.nodes('wordDocument/body/p')
AS doc(p)
WHERE id=1;
 
/*******************************************************
(2) Reading from an SSRS Report
*******************************************************/
SELECT datasource.value('(DataSourceReference/text())[1]', 'varchar(20)') AS DataSource,
              dataset.value('(@Name)', 'varchar(20)') AS DataSet
FROM #data
CROSS APPLY doc.nodes('Report/DataSources/DataSource') AS datasources(datasource)
CROSS APPLY doc.nodes('Report/DataSets/DataSet') AS datasets(dataset)
WHERE id=2;
 
/*******************************************************
(3) Reading from extracted event viewer
data
*******************************************************/
SELECT [System].value('(Computer/text())[1]',
'varchar(20)') AS SQLBox,
              [System].value('(../EventData/Data/text())[1]', 'varchar(50)') AS DB,
              [System].value('(TimeCreated/@SystemTime)[1]', 'varchar(20)') AS StartupTime
FROM #data
CROSS APPLY doc.nodes('Events/Event/System') AS events([System])
WHERE id=3
AND [System].value('(EventRecordID/text())[1]', 'int')=16563
AND [System].value('(Channel/text())[1]', 'varchar(20)')='Application';
 
 
/*******************************************************
(4) Reading from a SSIS package
*******************************************************/
SELECT x.value('(@ExecutableType)[1]',
'varchar(100)') AS PkgType,
              x.value('(Property[@Name="CreatorName"])[1]',
'varchar(100)') AS Creator,
              x.value('(Property[@Name="CreationDate"])[1]',
'varchar(100)') AS CreatedOn,
              x.value('(Property[@Name="CreatorComputerName"])[1]', 'varchar(100)') AS Computer
FROM #data
CROSS APPLY doc.nodes('Executable') AS ex(x)
WHERE id=4;
 
 
/*******************************************************
(5) Reading from an SQL trace (XML
format)
*******************************************************/
SELECT 
e.value('(@name)[1]', 'varchar(100)') AS [event], 
              e.value('(Column[@name="StartTime"]/text())[1]',
'varchar(100)') AS StartTime,
              e.value('(Column[@id="1"]/text())[1]', 'varchar(100)') AS Command
FROM #data
CROSS APPLY doc.nodes('TraceData/Events/Event')
AS events(e)
WHERE id=5;

... and the results:

Conclusion

Today I demonstrated how to use T-SQL to read from different XML data structures. Thanks for reading!

--ab

Last Updated: 5/21/2013 (Posted)

Sunday, April 28, 2013

mdq.XmlTransform -- Part 2: Creating an MS Word Doc

by Alan Burstein

Intro

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:

<?xml version="1.0"
encoding="utf-8"?>

    <?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>Hello Word!!!</w:t>

                </w:r>

            </w:p>

            </w:body>

        </w:wordDocument>

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:

DECLARE @xslt xml='
<xsl:stylesheet
version="1.0"
   
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
   
xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml">
   
<xsl:output method="xml" 
xml:space="default" />
 
   
<!--  This template does all
the work -->
   
<xsl:template match="/" xml:space="default">
       
<xsl:processing-instruction name="mso-application"
xml:space="default">
           
<xsl:text>progid="Word.Document"</xsl:text>
       
</xsl:processing-instruction>
   
       
<w:wordDocument><!-- This creates the word doc -->
            <w:body>
            <w:p>
                <w:r>
                    <w:t>Created with
mdq.xmlTransform (no loops)</w:t>
                </w:r>
            </w:p>
    
       </w:body>
       
</w:wordDocument>
   
</xsl:template>   
   
<xsl:template match="DataSourceID"/>
</xsl:stylesheet>';
 
SELECT REPLACE(mdq.xmlTransform('',@xslt),
                      '<?mso-application
progid="Word.Document"?>', 
                      '<?xml
version="1.0" encoding="ISO-8859-1"?>
                       <?mso-application
progid="Word.Document"?>') AS xml_output;
 

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.

<!--  (1) Grab an RDL XML
FRAGMENT from an SSRS report -->

<!-- 
(using T-SQL REPLACE to remove the rd: namespace references) -->
<DataSource Name="ReportingDemo">

    <DataSourceID>f34d206b-ca72-4ca6-9d5c-4151cd7eaxxx</DataSourceID>

    <ConnectionProperties>

        <DataProvider>SQL</DataProvider>

        <ConnectString>

            Data Source=ABC;EFG Catalog=XYZ

        </ConnectString>

    </ConnectionProperties>

</DataSource>

<!—(2)
Create an XSLT function that puts this data into a MS Word Doc -->
<xsl:stylesheet version="1.0"

    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

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

    <xsl:output method="xml"  xml:space="default" omit-xml-declaration="no" />

    <!--  This template does all the work -->

    <xsl:template match="/DataSource/ConnectionProperties"
xml:space="preserve">

        <xsl:processing-instruction name="mso-application" xml:space="default">

            <xsl:text>progid="Word.Document"</xsl:text>

        </xsl:processing-instruction>

        <w:wordDocument><!-- This creates the word doc -->

            <w:body>
                <!-- These
two templates collect the data -->

                <xsl:apply-templates select="DataProvider"/>

                <xsl:apply-templates select="ConnectString"/>

            </w:body>

        </w:wordDocument>

    </xsl:template>

    <xsl:template match="DataProvider" xml:space="preserve">

            <w:p>

                <w:r>

                    <w:t><xsl:apply-templates /></w:t>

                </w:r>

            </w:p>

    </xsl:template>

    <xsl:template match="ConnectString" xml:space="preserve">

            <w:p>

                <w:r>

                    <w:t><xsl:apply-templates /></w:t>

                </w:r>

            </w:p>

    </xsl:template>

    <!-- This
disposes of DataSourceID-->

    <xsl:template match="DataSourceID"/>

</xsl:stylesheet>

If we feed that RDL data and transform to mdq.xmlTransform like this:

DECLARE @xml xml='
<DataSource Name="ReportingDemo">
   
<DataSourceID>f34d206b-ca72-4ca6-9d5c-4151cd7eaxxx</DataSourceID>
    <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString>
            Data Source=ABC;EFG Catalog=XYZ
        </ConnectString>
    </ConnectionProperties>
</DataSource>',
 
@xslt xml='
<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml">
    <xsl:output method="xml" 
xml:space="default" />
 
    <!--  This template does all the work -->
    <xsl:template
match="/DataSource/ConnectionProperties"
xml:space="default">
        <xsl:processing-instruction
name="mso-application" xml:space="default">
           
<xsl:text>progid="Word.Document"</xsl:text>
        </xsl:processing-instruction>
   
        <w:wordDocument><!-- This creates the word doc
-->
            <w:body>
                <!-- These two templates collect the data -->
                <xsl:apply-templates
select="DataProvider"/>
                <xsl:apply-templates
select="ConnectString"/>
            </w:body>
        </w:wordDocument>
    </xsl:template>
   
    <xsl:template match="DataProvider"
xml:space="preserve">
            <w:p>
                <w:r>
                    <w:t><xsl:apply-templates
/></w:t>
                </w:r>
            </w:p>
    </xsl:template>
 
    <xsl:template match="ConnectString"
xml:space="preserve">
            <w:p>
                <w:r>
                    <w:t><xsl:apply-templates
/></w:t>
                </w:r>
            </w:p>
    </xsl:template>
 
    <!-- This disposes of DataSourceID-->
    <xsl:template match="DataSourceID"/>
</xsl:stylesheet>';
 
SELECT REPLACE(mdq.xmlTransform(@xml,@xslt),
                      '<?mso-application progid="Word.Document"?>', 
                      '<?xml version="1.0"
encoding="ISO-8859-1"?>
                       <?mso-application
progid="Word.Document"?>') AS xml_output;
 

We get this:

<?xml version="1.0"
encoding="utf-8"?>

    <?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>SQL</w:t>

                </w:r>

            </w:p>

            <w:p>

                <w:r>

                    <w:t>Data Source=ABC;EFG Catalog=XYZ</w:t>

                </w:r>

            </w:p>

            </w:body>

        </w:wordDocument>

...which, if we copy/paste into notepad and save as wow.xml, then open with Microsoft Office Word, we get:

Conclusion

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!

--ab

Last Updated: 4/29/2013 11:35am (Posted, fixed code issues)