0

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...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
BojowyZajaczek
  • 419
  • 1
  • 3
  • 10
  • Is it too few like say `40` and you expected more? – billinkc Feb 24 '15 at 20:16
  • The other assumption I'm making is that the XML you are seeing is truncated because SSMS truncates at ... whatever the default is. If you change the settings in Tools, Options, Query Results, SQL Server, Results to Grid, XML Data = Unlimited, do you get the XML you expect? – billinkc Feb 24 '15 at 20:19
  • Output as text is limited in SQL Server Management Studio. Never rely on it for large blocks of text. Even if you up the default maximum in the options, you'll still get truncated displays for very large blocks of text. – pmbAustin Feb 24 '15 at 20:20
  • Solved by Tools, Options, Query Results, SQL Server, Results to Grid, XML Data = Unlimited. Thanks @billinkc – BojowyZajaczek Feb 24 '15 at 20:24

1 Answers1

2

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

billinkc
  • 59,250
  • 9
  • 102
  • 159