CODEWITHSUNDEEP
sql-serverxmlsql-server-2008r2-expressfor-xml
BojowyZajaczek
BojowyZajaczek

Reputation: 419

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

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:

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

Upvotes: 0

Views: 308

Answers (1)

billinkc
billinkc

Reputation: 61211

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

Upvotes: 2

Related Questions