The problem with this approach is that when the execution plan is created, it has to read ALL records to know which are within the "binary" range you talk about....

DECLARE@data XML = '

<ROOT>

<Employee>

...

If the search criteria is dynamic (usnig a variable) see this blog post

http://weblogs.sqlteam.com/peterl/archive/2008/09/01/Updated-XML-search-test-case-with-variables.aspx

Works for me. Remember UTF-16 is similar to UNICODE.

Please note the N string-prefix.

declare @xml xml

set @xml =

N'<?xml version="1.0" encoding="utf-16"?>

<root>

<names>

...

Same question asked over here last week http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=146582

I guess OP gave up.

Are you using table variables? Or temp tables?

There are no statistics for table variables. The number of records are always estimated to 1 in the query optimizer for table variables.

Let's do some testing...SET NOCOUNT ON

-- Prepare sample data

DECLARE@UserRole TABLE

(

UserID INT NOT NULL,

RoleName VARCHAR(100) NOT NULL,

ProjectID INT NOT NULL,

RoleAssignedDate DATETIME NOT NULL

)

-- Populate sample data

INSERT@UserRole

(

UserID,

RoleName,

ProjectID,

RoleAssignedDate

)

VALUES(1112, 'Technical Director', ...

-- The XML way and current connection language setting

SELECTm.i.value('(text()[1])', 'VARCHAR(20)') AS theMonthName,

ROW_NUMBER() OVER (ORDER BY m.i) AS theMonthNumber

FROM(

SELECTCAST('<m>' + REPLACE(months, ',', '</m><m>') + '</m>' AS XML).query('(.)')

FROMsys.syslanguages

WHERElangid = @@LANGID

) AS d(m)

CROSS...

See

http://beyondrelational.com/blogs/jacob/archive/2010/06/09/xquery-lab-56-inserting-elements-with-unicode-values.aspx

The argument for NTILE function is an integer, not a column value. NTILE(4) OVER (PARTITION BY Col1 ORDER BY Col1) will work.

Paul, what if...

Edge condition fixed!

You can make the code as a scalar function in your database and add the result of the query to your resultset in your report.

I posted a generic example here http://weblogs.sqlteam.com/peterl/archive/2010/04/28/The-Excel-Column-Name-assigment-problem.aspx

if someone is interested about a generic solution...

The solution is a one-statement query and works really well for any number under INT range.

Here are two problems?

The original and the "Excel column numbering" question?

For the original question, to keep the result within VARCHAR(8000) the maximum line number to convert is 208000 which...