the xmlsqlninja: Set-Based SQL

Showing posts with label Set-Based SQL. 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)

Monday, April 29, 2013

A Set-Based Solution to the Longest Common Subsequence Problem

by Alan Burstein

Updated on 9/28/2015

Update: Exactly 2 and a half years ago I posted what I believed to be a solution to the Longest Common Subsequence problem using a Tally table. Unfortunately I got a substring and a subsequence mixed up. Ironically, if you look at the Wikipedia page for the Longest Common Subsequence the first line reads, "Not to be confused with longest common substring problem." and for the Longest Common Substring the first line reads, "Not to be confused with longest common subsequence problem.". I guess I'm not the only one who has gotten these confused. Anyhow, I have spent the past 2 and a half years trying to develop a purely set-based solution to the Longest Common Subsequence and have failed. It's been a great learning experience though; this type of exercise has dramatically sharpened my SQL and math skills. For a great solution to this problem see Phil Factor's excellent solution from earlier this year. I do have an updated solution to the Longest Common Substring however. It uses my N-Grams Function. Below is my updated NGrams8K function and LCSS

A Nasty Fast Set-Based Solution to the Longest Common Substring Problem:

    CREATE
    
      FUNCTION dbo.NGrams8K (@string 
        varchar
        (
      8000), @n 
        int
      )
    
    /********************************************************************
    
      Created by:       Alan Burstein
    
      Created on:      3/10/2014
    
    Last Updated on: 09/09/2015
    
      n-gram
    
     defined:
    
      In the fields of computational linguistics and probability,
    
      an n-gram is a contiguous sequence of n items
      from a given
    
      sequence of text or speech. The items can be
      phonemes, syllables,
    
      letters, words or base pairs according to the
      application.
    
      For more information see: http://en.wikipedia.org/wiki/N-gram
    
    Use:
    
      Outputs a stream of tokens based on an input string.
    
      Similar to mdq.nGrams:
    
      http://msdn.microsoft.com/en-us/library/ff487027(v=sql.105).aspx.
    
      Except it only returns characters as long as K.
    
      nGrams8K also includes the position of the
      "Gram" in the string.
    
    Revision History:
    
       Rev 00 - 03/10/2014 Initial Development - Alan
      Burstein
    
       Rev 01 - 05/22/2015 Removed DQS N-Grams
      functionality,
    
                improved iTally - Alan Burstein
    
       Rev 02 - 05/22/2015 Changed TOP logic to
      remove implicit conversion
    
                - Alan Burstein
    
       Rev 03 - 9/9/2015 Added logic to only return
      values if @n is greater
    
                than 0 and
      less then length of @string - Alan Burstein
    
    ********************************************************************/
    
    RETURNS
    
      TABLE
      WITH
      SCHEMABINDING
      AS
      RETURN
      
      WITH
      
        L1(
      N)
      AS 
    
    (
    
        SELECT 1
    
      FROM 
      (
      VALUES
      
      (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
    
      (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
    
      (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
    
      (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
    
      (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
    
      (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
    
      (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
    
      (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
    
      (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)
    
              ) 
        t(
      N)
    
      ),
      
        iTally
      
        (
      N) AS 
    
    (
    
              SELECT 
        TOP
        (
      CONVERT(BIGINT,(DATALENGTH(@string)-(@n-1)),0)) 
    
        ROW_
          NUMBER(
        
      )
      OVER 
      (
      ORDER
      BY 
      (
      SELECT
      NULL))
      
              FROM L1 a CROSS JOIN L1 b 
        -- add two more
        cross joins to support varchar(max)
      
      )
      
      SELECT
      
              position = N,
    
              token = SUBSTRING(@
        string,N,@n
      )
    
      FROM
      iTally
      
        WHERE @n > 0 AND @n <= 
        DATALENGTH
        (
      @string);
    
      GO
    
    /********************************************************************
    
      Created by:      Alan
      Burstein
    
      Created on:      3/10/2013
    
    Last Updated on: 09/20/2015
    
    Use:
    
  Returns the longest common substring between two strings.
    
    Revision History:
    
        Rev 00 - 03/10/2013 Initial Development - Alan
      Burstein
    
        Rev 03 - 09/20/2015 Performance tuned using
      NGrams8K - Alan Burstein
    
    ********************************************************************/
    
    CREATE
    
      FUNCTION dbo.LCSS8K
    
    (
    
        @string1 
        varchar
        (
      8000),
    
        @string2 
        varchar
        (
      8000)
    
    )
    
    RETURNS
    
      TABLE
      WITH
      SCHEMABINDING
      AS
      RETURN
      
    WITH
    
      Strings AS 
    
    (
    
      SELECT
      
         String1 = CASE WHEN 
        LEN
        (
      @string1)>LEN(@string2) THEN @string1 ELSE @string2 END,
    
         String2 = CASE WHEN 
        LEN
        (
      @string1)>LEN(@string2) THEN @string2 ELSE @string1 END
    
    ),
    
        I(
      
      N)
      AS (SELECT position FROM
      Strings CROSS APPLY
      dbo.NGrams8K(String2,1))
    
    SELECT
    
      TOP (1) WITH TIES
    
        TokenLength
      = I.N,
    
      NG.Token
      
    FROM
    
      I CROSS APPLY Strings s CROSS
      APPLY 
        dbo.NGrams8K(
      String2,I.N) NG
    
    WHERE
    
        CHARINDEX
        (
      NG.token,String1) > 0
    
    ORDER
    
      BY N DESC;
    
      GO

Conclusion

Another problem solved without cursors, loops or recursive CTEs. Let's mark this one up as another win for the Tally Table. Thanks for reading!

--ab

Tuesday, May 21, 2013

Reading XML Data

Intro

Examples

Setting up the sample data

Reading XML data

... and the results:

Conclusion

Monday, April 29, 2013

A Set-Based Solution to the Longest Common Subsequence Problem

Updated on 9/28/2015

A Nasty Fast Set-Based Solution to the Longest Common Substring Problem:

Conclusion

Further Reading