New "AS XML" feature

[Ivan Smahin]

Hello All,

Friday, October 31, 2008, 7:57:48 AM, you wrote:


> We just yesterday with Ivan have discuss this CDATA issue.

> I think Ivan right today will check MS SQL work in this regard
> and let us know results. Then we decide how to change it.


Well, a little bit playing with MS SQL.
-------------

--
CREATE TABLE t1 ( f1 varchar(200) );
insert into t1 VALUES ('Editions RM - 626, rue Notre-Dame Est, Trois-Rivieres G8T 4G9 Quebec - Canada

');

-- 
select * from t1 for xml AUTO, TYPE
Msg 6841, Level 16, State 1, Line 1
FOR XML could not serialize the data for node 'f1' because it contains a character (0x000C) which is not allowed in XML.
To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.


But
select * from t1 for xml AUTO
Produces:
<t1 f1="Editions RM - 626, rue Notre-Dame Est, Trois-Rivieres G8T 4G9 Quebec - Canada&#x0D;&#x0A;&#x0D;&#x0A;&#x0C;"/>


Another playing with that table:
delete from t1;
insert into t1 VALUES ( '
' );
select * from t1 for xml AUTO;
Produces:
<t1 f1="&#x1;" />

select * from t1 for xml AUTO, type;
Msg 6841, Level 16, State 1, Line 1
FOR XML could not serialize the data for node 'f1' because it contains a character (0x0001) which is not allowed in XML.
To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.


Both XMLs can not be validating later...
FATAL ERROR: Invalid character reference


AFAIK there is a set of symbols which are not
allowed for XML 1.0 - I mean it can not be even set as '&#x..;'
Am I right - there are two such ranges:
&#x1; - &#x1F;
&#x7F; - &#x9F;
?


We can do it this way: if the data contains it we use CDATA and do
nothing (almost - because of '&', '"'... which are able to be
converted to '&amp; and so on )

Or do it as simple as MS-SQL - just using some magic - you are saying 'type'
we are throwing exception - and put it as is otherwise.

What do you think?


-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com