Is the length of empty string always 0?

DECLARE @x nvarchar(max), @b varbinary(max), @v sql_variant;

SELECT

@x=,@b=cast( as varbinary(max)),@v=cast( as sql_variant)

— For those variable length data type, the length of empty string always returns 0

SELECT

DATALENGTH(@x),DATALENGTH(@b),DATALENGTH(@v);

GO

— for untyped xml:

declare

@x xml;

— You can assign empty string to an untyped xml variable

SET

@x=N

SELECT

@x

— Check its length, it is not 0! It always returns 5;

SELECT

DATALENGTH(@x)

GO

— for typed xml:

CREATE

XML SCHEMA COLLECTION dbo.TestSchema

AS

N

‘<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;

<xsd:element name="Root">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="ContactName" type="xsd:string" />

<xsd:element name="JobTitle" type="xsd:string" minOccurs="0" />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>’

GO

declare

@x xml(dbo.TestSchema );

— You can even assign empty string to an typed xml variable (strange behaviour)

SET

@x=N

SELECT

@x

— Check its length, it is not 0! It always returns 5;

SELECT

DATALENGTH(@x)

GO

— This is because by default typed xml variable/column is CONTENT. 

— DECLARE xml(dbo.TestSchema) is equivalent to DECLARE @xml(CONTENT dbo.TestSchema)

— For DOCUMENT type of xml, you cannot assign a empty string:

declare

@x xml(DOCUMENT dbo.TestSchema );

— You can NOT even assign empty string to DOCUMENT type xml variable

SET @x=N

So be carefull when checking an xml variable is empty or not. Do not use Datalength(@xmlVariable)=0 to check an xml is empty or not.

GO

Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s