Anyone got the time to help here. Seems to be going down the route of loops and NOLOCK.

Try this

WHERE [Permited_by].exist('/Root/Row/User/.[fn:upper-case(.)= ("SHW","SKT","PRS")]')>0

Maybe this?

Select Distinct State, (Select Stuff((Select '

' + City

From #tempCityState

Where State = t.State

FOR Xml Path(''),TYPE).value('.','VARCHAR(1000)'),1,1,'')) AS Cities

From #tempCityState t

You can shred your XML using something like this

declare @xvar xml=

'<SRCL>

<list>

.

.

</list>

</SRCL> ';

SELECT l1.v1.value('(SRCNm/string/text())[1]','VARCHAR(100)') as string1,

l1.v1.value('(SRCHsh/string/text())[1]','VARCHAR(100)') as string2,

...

There's an excellent article here[/url]

dwain.c (3/20/2013)

---

Lynn Pettis (3/20/2013)

---

I am really wondering how some people manage to get into contracting when they have no idea how to write good, performing code and expect a free...

SELECT a.id,

(SELECT b. AS "@ColName",

b.value AS "text()"

...

Here's another, same caveats apply

WITH CTE AS

(

SELECT id,grp,dte,

ROW_NUMBER() OVER(ORDER BY dte ASC, grp) AS rn

FROM @tbl tbl

),

CTE2 AS (

SELECT id,grp,dte,rn,

MIN(rn)...

Eugene Elutin (3/14/2013)

---

"Looks like" - it just really "Looks like" nothing more. That is whole problem for recursive CTE's - they are fine for limited number of recursions, they not...

Rather than using a cast, replace

FORXML PATH ('Customer'))

with

FORXML PATH ('Customer'),TYPE)

SELECT CourseID,

MAX(CASE WHEN ScheduleTerms='Q1' THEN 1 ELSE 0 END) AS Q1,

MAX(CASE WHEN ScheduleTerms='Q2' THEN 1 ELSE...

DECLARE @t TABLE(ID INT, Name CHAR(3),Code CHAR(1))

INSERT INTO @t(ID,Name,Code)

VALUES

(1, 'abc', 'A'),

(1, 'abc', 'B'),

(2, 'ght', 'F'),

(3, 'jku', 'G'),

(4, 'xyz', 'P'),

(4, 'xyz', 'Q'),

(5, 'rst', 'D');

SELECT a.ID,

...

Using DECIMALs with Binet's formula you can get the first 92 in the series

WITH Constants(one,half,root5,phi,phi2,phi4,phi8,phi16,phi32,phi64) AS (

SELECT CAST(1.0 AS DECIMAL(38,20)),

CAST(0.5 AS DECIMAL(38,20)),

...

SELECT t1.id,t1.id1

FROM tbltests t1

WHERE NOT EXISTS(SELECT * FROM tbltests t2

WHERE t2.id = t1.id1

...

Have a look at the 'Group Islands of Contiguous Dates' article here[/url]

DECLARE @t TABLE(L1 INT,L2 CHAR(1), L3 CHAR(1), dt Date, Forecast NUMERIC(10,5))

INSERT INTO @t(L1,L2,L3,dt,Forecast)

VALUES

(1, 'a', 'x', '01-jan-2013', 1.2),

(1, 'a', 'x',...