Select for xml

Question

Post reply

Select for xml

dr_csharp

SSCommitted

Points: 1765
More actions
May 1, 2009 at 3:27 am

#133617

Hi friends
when i select from my Region Table like this:
Select 'Region' as [xsi:type],* From Region as anyType For Xml Auto
result will be just a part of my records,about 34 from 100 records.
whats wrong with my select ?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Hi,

please post sample data as described in http://www.sqlservercentral.com/articles/Best+Practices/61537/.

Your sample data should include table definition, the namespace declaration as well as some sample data that pass and some that fail.

My first guess is that your schema definition eliminates NULL values. But again, just guessing...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

dr_csharp SSCommitted Points: 1765 More actions · Answer 2

theres not anything special:

i have a table named Region,with following fields :

Id int Identity(1,1)

Code varchar(10)

Title nvarchar(20)

as you know:when select for xml,all result set return as one record,so i think theres should be some limitation with select statement output length,because when number of records are less than 32,every thing is OK but when number of records increased theres some thing wrong!?

Thanks

GSquared SSC Guru Points: 260824 More actions · Answer 3

I just ran this:

create table #Region (

ID int identity primary key,

Code varchar(10),

Title varchar(20));

insert into #Region (Code, Title)

select left(newid(), 10), left(newid(), 20)

from dbo.Numbers

where Number between 1 and 100;

select 'Region' as Type, * from #Region as anyType for xml auto;

The final select returned all 100 records.

How are you outputing the final select?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

dr_csharp SSCommitted Points: 1765 More actions · Answer 4

i think it refers to select result length,because every thing is as like as mine ...

is there any thing about select result length?

LutzM SSC Guru Points: 107049 More actions · Answer 5

Did you notice that GSquared didn't reference the Type alias to an XML schema like you did?

What happens if you run the SELECT the same way?

If you still get less than 100 records you might need to provide the sample data that are failing.

Edit:

Do you run the statement to get the result in QA or do you store the result of the SELECT statement in a variable/table? If so, what's the length/structure of your target?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

dr_csharp SSCommitted Points: 1765 More actions · Answer 6

result in QA.

again i think here may be some limitation with select statement result..because every thing is usual..

if i select for xml auto,elements every thing is ok because each record is seprated from others in result,but when select for xml auto result come in one record ..

GSquared SSC Guru Points: 260824 More actions · Answer 7

If you're outputing the XML (query result) to "Results to text", there's a 255-character default limit to that. Doesn't change what SQL Server is doing in the background, but it will only show the first 255 characters on your screen.

Column width for XML output has a similar limitation.

Both are set in Tools, Options.

Is it possible that's the situation here?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

dr_csharp SSCommitted Points: 1765 More actions · Answer 8

when i check this:

tools->option->Query Result->SqlServer

theres 2 item:

1.#result to grid

here maximum characters retrieved for xml data set to 2MB

2.#result to text

here,maximum number of characters displayed in each column set to 256

so,when i select for xml,result will be in one text result and theres limitation with 256 characters

but how can i use a way that have same result and be a xml data ?

i mean 1.#

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 9

dr_csharp (5/9/2009)
when i check this:
tools->option->Query Result->SqlServer
theres 2 item:
1.#result to grid
here maximum characters retrieved for xml data set to 2MB
2.#result to text
here,maximum number of characters displayed in each column set to 256
so,when i select for xml,result will be in one text result and theres limitation with 256 characters
but how can i use a way that have same result and be a xml data ?
i mean 1.#

Run your query with "result to grid" selected.

Then you will get the whole XML in one cell.

It will be underlined like a hyperlink and then you can select it and view the entire XML.

dr_csharp SSCommitted Points: 1765 More actions · Answer 10

i checked it :

Tools=>Options=>Query Result=>Default Destination for results set to result to grids (by defaulte its Set to this )

:O(

any suggesstion ?