I created a batch script that calls a T-SQL file and output it to text file. The T-SQL file contains select statement of a table to get the information of column with XML as datatype. The file will then be sent with either contents as body of email or as an attachment. However, when I open the text file, I see a long list of information similar to the one below:
<`EVENT_INSTANCE><EventType>CREATE_INDEX</EventType <PostTime>2009-05-29T12:54:28.463...
What I'm expecting is to see contents look like the one below:
<EVENT_INSTANCE>
<EventType>CREATE_INDEX</EventType>
<PostTime>2009-05-29T12:54:28.463</PostTime>
<SPID>54</SPID>
<ServerName>SERVER\INSTANCE</ServerName>
<LoginName>Domain\username</LoginName>
<UserName>dbo</UserName>
<DatabaseName>AdventureWorks</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>ni_ChangeAttempt_DBUser</ObjectName>
<ObjectType>INDEX</ObjectType>
<TargetObjectName>ChangeAttempt</TargetObjectName>
<TargetObjectType>TABLE</TargetObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>CREATE NONCLUSTERED INDEX ni_ChangeAttempt_DBUser ON
dbo.ChangeAttempt(DBUser)
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
If the above is not possible, I expect to see the output of query look like the one below:
Event Post Time Server Name
----- --------- -----------
CREATE INDEX 2009-05-29T12:54:28.463 Server\Instance
My question is: What is the appropriate output file to which to send the query results so that I can have a more meaningful and more organized view of the results?
ADDITION:
Please see below the contents of the batch script:
osql -E -S SERVER\INSTANCE -d <database_name> -iD:\scripts\qryddl.sql -oD:\scripts\qryddl01.txt -w2000 -n -h
bmail -s <ip address> -t <email> -f <email> -h -a " Database Object Changes from <ServerName>" -m D:\scripts\qrydll.txt
ADDITION 2:
The qrydll.sql just have this contents:
use <database_name>
GO
SELECT EventData FROM dbo.ChangeAttempt
The table dbo.ChangeAttempt has this structure:
Column Name Data Type
----------- ---------
EvenData xml
AttemptDate datetime
DBUser char(50)