Retieving data with "for xml"

[jw56578]

I execute a simple query with "for xml auto".
One of the fields in my table is "char" type and has a blank value.
when the query gets to this field the xml construction stops.
such that it creates the following , where "core" is the field of type
"char". What is the problem

<tablename company_id="111 9" group_id="0" ideal="0" core="

[Erland Sommarskog]

(jw*****@gmail. com) writes:

I execute a simple query with "for xml auto".
One of the fields in my table is "char" type and has a blank value.
when the query gets to this field the xml construction stops.
such that it creates the following , where "core" is the field of type
"char". What is the problem

<tablename company_id="111 9" group_id="0" ideal="0" core="

The blank value is most likely a NULL character. It appears that SQL2000
does not handle this correctly. Here is a repro:

CREATE TABLE a (a int not null, b varchar(12) NOT NULL)
go
INSERT a (a, b) values (1, '23')
INSERT a (a, b) values (2, '')
INSERT a (a, b) values (3, ' ')
INSERT a (a, b) values (4, char(0))
INSERT a (a, b) values (5, '//')
go
select * from a for xml auto
select b, a from a
go
drop table a

I tried the same in SQL 2005, which gives this correct result:

<a a="1" b="23"/><a a="2" b=""/><a a="3" b=" "/><a a="4" b="&#x00;"/><a a="5" b="//"/>
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp