This might work:

SELECT T.C.value('@Name', 'varchar(50)') FROM @myXMLData.nodes('/order/Product/Drops/Drop[@Number="1"]/Area') T(C)

note the test in brackets for the attribute Number.

My variant on this uses count instead of sum. Since count() ignores null values, you don't need the else part of the case:

select CustomerId

from #Purchase

where ProductCode in ('A', 'B',...

Koen Verbeeck (6/6/2014)

gbritton1 (6/6/2014)

Koen Verbeeck (6/6/2014)

TomThomson (6/6/2014)

Just a couple of examples: A British colleague who held an A-level in math and a bachelor's in business swore you could, in...

Koen Verbeeck (6/6/2014)

TomThomson (6/6/2014)

Just a couple of examples: A British colleague who held an A-level in math and a bachelor's in business swore you could, in fact, divide a...

Before we look into the issue, have you read, understood and applied the suggestions in Sean's post regarding splitting strings, SARGability, scalar functions etc.? There's little point in working...

try this:

select 0 as [@id]

, (select 'Valuta' as [@name]

, Valuta as [@value]

from ...

I've slowly grown to favor leading commas in queries for two reasons:

1. It's easy to comment out a column for debugging or to explicitly show that there is a column...

Does your approach work? If not, what is wrong with it? Does it have performance problems? If so, have you looked at the execution plans? What...

Actually that won't work at all since it can't handle CDATA

Solved it!

The trick is to specify CDATA at the top level, e.g.

USE AdventureWorks2012;

GO

SELECT 1 as Tag,

0 as Parent,

...

Here's some more on this problem:

on MSDN,

there is this example:

USE AdventureWorks2012;

GO

SELECT 1 as Tag,

0 as Parent,

...

Here's a variant using FLOWR. I think it's a bit more readable but YMMV:

;with xmlnamespaces(DEFAULT 'http://tempuri.org/Ultimate')

select ex.xml.query('

for $x in /Entity

where $x/Person/Last_Name = "Hillier"

and...

Here's another way:

with cte as (

select rn=ROW_NUMBER() over(order by name)

, CommaSeparated = max(t1.name + t2.classlist)

from #table1 t1

cross apply (

select ', ' +t2.class

...

You could add a second dataset that does the max function in SQL. Assume that your first query accesses a table called MyData. Assume that your first query...

This is a great example showing that Explicit is better than Implicit.