Advertisement

08.15.2008 at 10:50PM PDT, ID: 23653113 | Points: 100
[x]
Attachment Details

Transform SQL Server 2005 Profiler XML Trace file

Asked by daluu in Extensible Stylesheet Language Transformation (XSLT), SQL Server 2005

Tags: , , ,

Hi,

I'm a novice to using XSLT. I have tried to transform an XML trace file generated by SQL Server 2005 Profiler. The transforms I would like to do are

    * convert the XML trace (a data table when viewed in SQL Server 2005 Profiler) to CSV
    * extract the SQL queries in the XML column nodes with column name attribute value of "TextData"


In working on this, I noticed that the XML trace file is unicode. Not sure if that will affect parsing. I'm writing the XSLT 1.0 in Windows notepad, with default of utf-8.

See the general format of the SQL XML trace in code snippet. And see the code snippets for my XSLT implementations.

With both XSLT transforms, this is what I get:

2711061118161713912141522008-03-28T17:00:01.31-07:00-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
...

Not exactly what I was thinking. Did I write the XSLT incorrectly? Or is something wacked with the XML trace file?

Would be best if you have SQL Server 2005 Profiler and SQL Server 2005 so that you can produce your own XML trace file to test with. But I can send you a sample to test against.
Edit/Delete MessageStart Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:

...SQL2K5 XML trace file excerpts below...
<?xml version="1.0" encoding="utf-16"?>
<TraceData xmlns="http://tempuri.org/TracePersistence.xsd">
  <Header>
    <TraceProvider name="Microsoft SQL Server" MajorVersion="9" MinorVersion="0" BuildNumber="0" />
    <ServerInformation name="192.168.9.49" />
    <ProfilerUI>
      <OrderedColumns>
        <ID>27</ID>
        <ID>1</ID>
        <ID>10</ID>
        <ID>6</ID>
        <ID>11</ID>
        <ID>18</ID>
        <ID>16</ID>
        <ID>17</ID>
        <ID>13</ID>
        <ID>9</ID>
        <ID>12</ID>
        <ID>14</ID>
        <ID>15</ID>
        <ID>2</ID>
      </OrderedColumns>
...
    </ProfilerUI>
  </Header>
  <Events>
...
    <Event id="13" name="SQL:BatchStarting">
      <Column id="1" name="TextData"> SET FMTONLY OFF; SET NO_BROWSETABLE ON;SET NOCOUNT ON;
 
WITH	Messages
AS
	(
	SELECT	ROW_NUMBER() OVER ( ORDER BY ForwardedTime DESC ) AS Row, 
		DbId, Type,
		Urgent, Confidential, ReceiveStatus, MessageType,
		Unheard,
		AssociateFirstName, AssociateLastName,
		AssociateFirstLast, AssociateLastFirst,  
		CreatedTime,
		AssociateDbId,
		AssociateType,
		AudioDbId, AudioType,
		SenderCallerIDName,
		SenderCallerIDNumber,
		Comments, ForwardedTime,
		ParentDbId, ParentType, AudioGUID,
		Length,
		SenderCallerIDAccessCode,
		SenderCallerIDType,
		SenderCallerIDSubType,
		CallbackAddressDbId, CallbackAddressType, SendStatus,
		CallbackAddress, CallbackAccessCode,
		CustomData,
		CallLogDbId, CallLogType
	FROM	dbo.MessagesView
	WHERE	ParentDbId = 1519 
	)
 
SELECT	
	DbId, Type,
	Urgent, Confidential, ReceiveStatus, MessageType,
	Unheard,
	AssociateFirstName, AssociateLastName,
	AssociateFirstLast, AssociateLastFirst,  
	CreatedTime,
	AssociateDbId,
	AssociateType,
	AudioDbId, AudioType,
	SenderCallerIDName,
	SenderCallerIDNumber,
	Comments, ForwardedTime,
	ParentDbId, ParentType, AudioGUID,
	Length,
	SenderCallerIDAccessCode,
	SenderCallerIDType,
	SenderCallerIDSubType,
	CallbackAddressDbId, CallbackAddressType, SendStatus,
	CallbackAddress, CallbackAccessCode,
	CustomData,
	CallLogDbId, CallLogType
FROM
	Messages 
WHERE
	Row BETWEEN 1 AND 360
ORDER BY
	Row
 
SELECT	rowTotal = COUNT(DbId)
FROM	dbo.MessagesView
WHERE	ParentDbId = 1519  SET NO_BROWSETABLE OFF;</Column>
      <Column id="9" name="ClientProcessID">3552</Column>
      <Column id="11" name="LoginName">tv_client</Column>
      <Column id="10" name="ApplicationName">.Net SqlClient Data Provider</Column>
      <Column id="12" name="SPID">368</Column>
      <Column id="14" name="StartTime">2008-03-28T17:00:41.387-07:00</Column>
    </Event>
...
</Events>
</TraceData>
 
...my XSLT for XML to CSV conversion of the trace file...
 
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <!-- Output as CSV text and define variables -->
    <xsl:output method="text"/>
    <xsl:strip-space elements="*"/>
    <xsl:variable name="newline" select=" '
' " />
    <xsl:param name="comma"><xsl:text>,</xsl:text></xsl:param>
 
    <xsl:template match="TraceData">
        <!-- Generate CSV headers-->
	<xsl:apply-templates select="Header/ProfilerUI/OrderedColumns"/>
        <!-- Parse the trace data into CSV data rows-->
	<xsl:apply-templates select="Events//Event"/>
    </xsl:template>
 
    <xsl:template match="OrderedColumns">
	<xsl:for-each select="child::node()">
		<xsl:sort select="." data-type="number" order="ascending"/>
		<xsl:choose>
                        <!-- Primary Key is EventClass column-->
			<xsl:when test="self = 27">
				EventClass
				<xsl:value-of select="$newline"/>
			</xsl:when>
			<xsl:otherwise>
<!-- Define header and preserve order by looking up column name using the given ID -->
				<xsl:value-of select="/TraceData/Events/Event/Column[1][@id = self]/@name"/>
				<xsl:value-of select="$comma"/>
			</xsl:otherwise>
		</xsl:choose>
        	<xsl:choose>
			<xsl:when test="position() = last()">
				<xsl:value-of select="$newline"/>
			</xsl:when>
			<xsl:otherwise>
				<xsl:value-of select="$comma"/>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:for-each>
    </xsl:template>
 
    <xsl:template match="Event">
	<!-- Assume EventClass is always 1st column -->
	<xsl:value-of select="@name"/>
	<xsl:value-of select="$comma"/>
        <xsl:for-each select="child::node()">
		<xsl:sort select="@id" data-type="number" order="ascending"/>
        	<xsl:value-of select="."/>
		<xsl:choose>
			<xsl:when test="position() = last()">
				<xsl:value-of select="$newline"/>
			</xsl:when>
			<xsl:otherwise>
				<xsl:value-of select="$comma"/>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:for-each>
    </xsl:template>
</xsl:stylesheet>
 
...my XSLT for extracting only the TextData column in trace file...
 
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="text"/>
    <xsl:strip-space elements="*"/>
    <xsl:variable name="newline" select="'
'" />
    <xsl:param name="newlineY"><xsl:text>
	</xsl:text></xsl:param>
    <xsl:template match="TraceData">
	    <xsl:apply-templates select="Events//Event"/>
    </xsl:template>
    <xsl:template match="Event">
	    My bad...
        <xsl:for-each select="//Column[@name = 'TextData']">
        	<xsl:value-of select="."/>
		    <xsl:value-of select="$newline"/>
		    <xsl:value-of select="$newline"/>
	    </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>
[+][-]08.20.2008 at 05:49AM PDT, ID: 22268816

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]08.21.2008 at 08:31AM PDT, ID: 22280625

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628