Enjoy Every Sandwich

Thoughts on SQL, XML, .NET and sometimes beer.

<November 2008>
SuMoTuWeThFrSa
2627282930311
2345678
9101112131415
16171819202122
23242526272829
30123456

Navigation

Tools

List O'Links

Kent's Other Stuff

Subscriptions

News

Please read these
Notices and Disclamiers

Post Categories

Article Categories



Thursday, October 21, 2004 - Posts

SQL 2005 Beta 2 Technical Preview Week
SQL Webcast Image

Katie Bar the Door! There's a whole slew of SQL 2005 webcasts come'n down the information superhighway in the form of SQL 2005 Beta 2 Technical Preview Week, - December 6-10, 2004. You might even win T-shirts and an XBOX (see the site for details).

Overview of the new Developer features in SQL Server 2005—Level 200
Monday, December 06, 2004
10:00 AM - 11:00 AM PT
Register here

Introducing the New SQL Server Management Studio—Level 100
Monday, December 06, 2004
1:00 PM - 2:00 PM PT
Register here

SQL Server 2005 as a .NET Runtime Host—Level 100
Monday, December 06, 2004
3:00 PM - 4:00 PM PT
Register here

Introducing XML in SQL Server 2005—Level 200
Monday, December 07, 2004
10:00 AM - 11:00 AM PT
Register here

Introducing ADO.NET 2.0 for SQL Server 2005—Level 200
Monday, December 07, 2004
12:00 PM - 1:00 PM PT
Register here

T-SQL Enhancements in SQL Server 2005—Level 200
Monday, December 07, 2004
3:00 PM - 4:00 PM PT
Register here

The New Security Model in SQL Server 2005—Level 200
Wednesday, December 08, 2004
10:00 AM - 11:00 AM PT
Register here

Introducing Web Services in SQL Server 2005—Level 200
Wednesday, December 08, 2004
1:00 PM - 2:00 PM PT
Register here

Introducing Service Broker in SQL Server 2005—Level 200
Wednesday, December 08, 2004
3:00 PM - 4:00 PM PT
Register here

Introducing Reporting Services for SQL Server 2005—Level 200
Thursday, December 09, 2004
10:00 AM - 11:00 AM PT
Register here

Introducing SQL Server Integration Services for SQL Server 2005—Level 200
Thursday, December 09, 2004
1:00 PM - 2:00 PM PT
Register here

Introducing SQL Server 2005 Analysis Services for Developers—Level 200
Thursday, December 09, 2004
3:00 PM - 4:00 PM PT
Register here

Introducing Full-Text Search in SQL Server 2005—Level 200
Friday, December 10, 2004
10:00 AM - 11:00 AM PT
Register here

Introducing Replication in SQL Server 2005—Level 200
Friday, December 10, 2004
1:00 PM - 2:00 PM PT
Register here

Introducing Notification Services in SQL Server 2005—Level 200
Friday, December 10, 2004
3:00 PM - 4:00 PM PT
Register here

See you there!

posted Thursday, October 21, 2004 1:43 PM by ktegels

XQuery and those darn "mysteriously empty" rowsets.

Consider the following query:

create table t1 (x xml)
go

insert into t1 values('<a b="1"/>')
insert into t1 values('<a b="2"/>')
insert into t1 values('<a b="1"/>')
insert into t1 values('<a b="2"/>')
insert into t1 values('<a b="1"/>')
insert into t1 values('<a b="2"/>')
go
 
select x.query('/a[@b=1]') from t1
go

How many records would you expected in the result set?

If you said 6, you don't need to keep reading.

If you said something other than 6, you do.

Go try that query. Go ahead. I'll wait. Oh, see, you have one row with a result and one row with not.

Here's the trick: empty sequences are generating empty rows. It's a different behavior that we're used to with T-SQL. The reason that this happens is that the query method returns an empty sequence for those rows where @b="2" and an the expected <a b="1" /> single element sequence for the matching rows. Empty sequences aren't the same thing as null, which is what they'd have to be excluded from the result (take a look that the actual executation plan and you see a left outer join near the end...)

So if you really want only what if you want only those elements where @b = 1?

Well, ideally, you could write:

select x.query('/a[@b=1] except empty()') from t1

instead, but the except() operation isn't supported in SQL Server 2005's Query in the B2 or 10/2004 CTP bits. So you need to do it the the harder way:

select x.query('/a[@b=1]')
from t1
where x.exist('/a[@b="1"]')=1
go

Oh Bother!

Thanks to Dr. Han for showing me the light.

posted Thursday, October 21, 2004 12:50 PM by ktegels




Powered by Dot Net Junkies, by Telligent Systems