SQL Server produces bad/invalid/damaged XML with FOR XML

Question

I've got a simple (?) problem.

I have a table t_dane_nieruch with one column nr_ewid (int, not null)

My query looks like this:

select top 40 
    nr_ewid
from 
    t_dane_nieruch 
for xml auto, elements

It produces this output:

<t_dane_nieruch>
    <nr_ewid>3</nr_ewid>
</t_dane_nieruch>
<t_dane_nieruch>
    <nr_ewid>4</nr_ewid>
</t_dane_nieruch>
<t_dane_nieruch>
    <nr_ewid>7</nr_ewid>
</t_dane_nieruch>
<t_dane_nieruch>
    <nr_ewid>8</nr_ewid>
</t_dane_nieruch>
<t_dane_nieruch>
    <nr_ewid>11</nr_ewid>
</t_dane_nieruch>
<t_dane_nieruch>
    <nr_ewid>49</nr_ewid>
</t_dane_nieruch>
<t_dane_nieruch>
    <nr_ewid>51</nr_ewid>
</t_dane_nieruch>

Problems:

• Output is not a valid XML
• Numbers of output elements is too few

Using SSMS with SQL Server 2008 R2 Express - output to text or file produce the same...

Answer 1

You're likely seeing truncation which SSMS defaults to 256 characters for non-XML data types and 1MB for XML when sent to the Grid.

You can modify this setting by clicking Tools, Options, Query Results, SQL Server, Results to Grid, XML Data = Unlimited