Slightly simpler...

select T.Loc.value('../../@Id','uniqueidentifier') AS [Institution]

, t.Loc.value('./Email[1]','varchar(128)') AS [Email]

, t.Loc.value('./BusinessRoleGuid[1]','uniqueidentifier') AS [BusinessRoleGuid]

FROM @x.nodes('/XML/Institution/Users/User') AS T(Loc )

Lots of ways of doing this

select a.ID,a.name,b.id as 'other id',b.name as OtherName

from addr a

left outer join addr b on b.id>a.id

and not exists (select * from addr c...

Assigning the COUNT(*) to a variable will prevent it returning a result set

DECLARE @RowsCount int

SET @RowSql = 'SELECT @RowsCount = COUNT(*) FROM MyTable'

EXEC SP_EXECUTESQL ...

Try joining to a tables of ranges

CREATE TABLE Ranges

(RangeFrom money NOT NULL,

RangeTo money NOT NULL,

Range varchar(20) NOT NULL,

PRIMARY KEY CLUSTERED (RangeFrom, RangeTo));

INSERT INTO Ranges VALUES (0, 50,...

The file as saved from Management Studio seems to be ANSI even though the XML indicates it is UTF-16.

One possible solution, open the file in notepad and save as Unicode...

select r.value('Name[1]','varchar(20)') as name,

r.value('Value[1]','decimal(12,6)') as value

from @x.nodes('/ArrayOfNameValuePair/NameValuePair') as x(r)

Something like this

declare @x xml

set @x='

... your xml

'

select r.value('local-name(.)','varchar(20)') as name,

r.value('./text()[1]','varchar(20)') as value

from @x.nodes('//*') as x(r)

Use sp_executesql with output parameters

.

.

.

select @STR='select @count=count(*) from '+@t1+' where unit_no=''352022000637171'';'

execute sp_executesql

@STR,

...

> You can't create an index on a table variable

But you can do this...

declare @t table(x int not null primary key clustered)

bull2000 (5/14/2008)

---

is there a way to ignore the truncate error and force the insert to finish.

For example,

Table_A has some columns whose length is longer than the ones in Table_B

Is...

Try this

SELECT CAST((CAST(b.[total shipping days] AS NUMERIC(5,2))/CAST(c.[shipping days] AS NUMERIC(5,2)))AS NUMERIC(5,2)) AS [average working days]

FROM

(SELECT

SUM(DATEDIFF(d,[order date], [ship date])) AS [total shipping days]

FROM orders)b,

(SELECT

COUNT(*) AS [shipping days]

FROM orders...

I tend to use the "max ... group by" pattern

select [Plan],

max(case when Setting='Colour' then [Value] end) as Colour,

...

Richard (5/21/2008)

---

That's a cute trick, I shall use that more often.

Although it only performs well when there is clustered index over the order by. (the sort kills it when there...

Use row_number

with cte as (

select id,BusKey,Name,StartDate,EndDate,

row_number() over(partition by BusKey order by EndDate desc,StartDate desc) as rn

from #t)

select id,BusKey,Name,StartDate,EndDate

from cte

where rn=1

Maybe this?

declare @x xml

set @x='

... your xml

'

select

r.value('Type[1]','VARCHAR(6)') as Type,

r.value('GUID[1]','VARCHAR(25)') as GUID,

r.value('Date[1]','DATETIME') as Date,

r.value('RegisterCode[1]','VARCHAR(6)') as RegisterCode,

r.value('User[1]','BIGINT') as [User],

r.value('StoreUID[1]','VARCHAR(6)') as StoreUID,

r.value('Charges[1]','MONEY') as Charges,

r.value('Taxes[1]','MONEY') as Taxes,

r.value('Tenders[1]','MONEY') as Tenders,

case when r.value('PostVoid[1]','VARCHAR(20)') is not...