Databases
StackOverflow2013
Postsdbo
POWhat is the appropriate output file from T-SQL query of table that has XML as datatype
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat is the appropriate output file from T-SQL query of table that has XML as datatype
    text
    copied!<p>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:</p> <pre><code>&lt;`EVENT_INSTANCE&gt;&lt;EventType&gt;CREATE_INDEX&lt;/EventType &lt;PostTime&gt;2009-05-29T12:54:28.463... </code></pre> <p>What I'm expecting is to see contents look like the one below:</p> <pre><code>&lt;EVENT_INSTANCE&gt; &lt;EventType&gt;CREATE_INDEX&lt;/EventType&gt; &lt;PostTime&gt;2009-05-29T12:54:28.463&lt;/PostTime&gt; &lt;SPID&gt;54&lt;/SPID&gt; &lt;ServerName&gt;SERVER\INSTANCE&lt;/ServerName&gt; &lt;LoginName&gt;Domain\username&lt;/LoginName&gt; &lt;UserName&gt;dbo&lt;/UserName&gt; &lt;DatabaseName&gt;AdventureWorks&lt;/DatabaseName&gt; &lt;SchemaName&gt;dbo&lt;/SchemaName&gt; &lt;ObjectName&gt;ni_ChangeAttempt_DBUser&lt;/ObjectName&gt; &lt;ObjectType&gt;INDEX&lt;/ObjectType&gt; &lt;TargetObjectName&gt;ChangeAttempt&lt;/TargetObjectName&gt; &lt;TargetObjectType&gt;TABLE&lt;/TargetObjectType&gt; &lt;TSQLCommand&gt; &lt;SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /&gt; &lt;CommandText&gt;CREATE NONCLUSTERED INDEX ni_ChangeAttempt_DBUser ON dbo.ChangeAttempt(DBUser) &lt;/CommandText&gt; &lt;/TSQLCommand&gt; &lt;/EVENT_INSTANCE&gt; </code></pre> <p>If the above is not possible, I expect to see the output of query look like the one below:</p> <pre><code>Event Post Time Server Name ----- --------- ----------- CREATE INDEX 2009-05-29T12:54:28.463 Server\Instance </code></pre> <p>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?</p> <p>ADDITION:</p> <p>Please see below the contents of the batch script:</p> <pre><code>osql -E -S SERVER\INSTANCE -d &lt;database_name&gt; -iD:\scripts\qryddl.sql -oD:\scripts\qryddl01.txt -w2000 -n -h bmail -s &lt;ip address&gt; -t &lt;email&gt; -f &lt;email&gt; -h -a " Database Object Changes from &lt;ServerName&gt;" -m D:\scripts\qrydll.txt </code></pre> <p>ADDITION 2:</p> <p>The qrydll.sql just have this contents:</p> <pre><code>use &lt;database_name&gt; GO SELECT EventData FROM dbo.ChangeAttempt </code></pre> <p>The table dbo.ChangeAttempt has this structure:</p> <pre><code> Column Name Data Type ----------- --------- EvenData xml AttemptDate datetime DBUser char(50) </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload