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:

  1. An XML fragment from a MS Word Doc
  2. An XML fragment from an SSRS Report
  3. An event from the Windows Event Viewer
  4. An XML fragment from an SSIS package
  5. 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

The new xmlsqlninja logo ('X-M-L'-'See-Quel' 'ninja')

Last Updated: 5/21/2013 (Posted)

No comments:

Post a Comment