Lets play with XML

Below is a nested XML which contains Customer info and Order Details.I need to get the Order Details for each customer in the XML.One way to do it was using OPENXML and sp_xml_preparedocument.

With new enhancements for XML datatype from SQL 2005 and beyond it can be easily done by using Xquery.This is how it can be done.

declare @xmlData XML
set @XMLData =
'<root><row>
<custid>1</custid>
<custname>name1</custname>
<order>
<orderdetail orderid = "1" orderdate = "2008-01-01" />
<orderdetail orderid = "2" orderdate = "2008-01-02" />
</order>
</row>
<row>
<custid>2</custid>
<custname>name2</custname>
<order>
<orderdetail orderid = "3" orderdate = "2008-01-03" />
<orderdetail orderid = "4" orderdate = "2008-01-04" />
</order>
</row>
</root> '

select @xmldata

select x.i.value('../../custid[1]','varchar(40)') as custid,
x.i.value('../../custname[1]','varchar(40)')as custname,
x.i.value('@orderid','int')as orderid,
x.i.value('@orderdate','date')as orderdate
from @xmldata.nodes('/root/row/order/orderdetail')x(i)

The resultset for the above query is this.

Getting numeric or character values from an alphanumeric string

Sometimes it becomes necessary to get all the numeric or all the character values from a alphanumeric string.
The idea in the solution I have posted is to convert all the characters from the string into rows in a derived table
using a look table @tbl & then concating the charater values excluding the numeric values in my example below.
If you need to get the numeric values from the string simply replace like '[a-z]' to like '[0-9]'

Declare @str As Varchar(50)
Select @str='klasdjfkl034ioerut'
Declare @value As Varchar(40)=''

Declare @tbl As Table(number Int Identity)
Insert Into @tbl Default Values
While SCOPE_IDENTITY()< len(@str)
Insert Into @tbl Default Values

Select @value=@value + '' + value From
(
Select number,SUBSTRING(@str,number,1)As value From @tbl Where number Between 1 And LEN(@str)
)t Where value like '[a-z]'

Select @value

Difference between 2 dates in Hours

Sometimes it is necessary to show the difference between 2 dates in hours.The query can be modified to be used as an function accepting 2 parametrs Date1 and Date2
The accuracy can be checked hereto compare the results. Here is the query

Declare @dt1 datetime='12-May-2010 09:00:00'
Declare @dt2 datetime='13-May-2010 04:01:00'

Select
RIGHT('0'+CONVERT(varchar(2),DATEDIFF(mi,@dt1,@dt2)/60),2)+ ':' +
RIGHT('0'+CONVERT(varchar(2),DATEDIFF(mi,@dt1,@dt2)%60),2)+ ':' +
RIGHT('0'+CONVERT(varchar(2),DATEDIFF(SECOND,@dt1,@dt2)%60),2) As DiffInHours

Recently one of my friend had asked me a query by which he could get all such vendors
who sell all the ItemTypes present in the table.Of course for sake of illustration purpose I have de normalised the table as my blog is not for sake of teaching the fundamentals of database but rather to provide tweaks for non trivial problems a developer faces.
Here is the table structure

In the above condition the o/p should be vendor id 1 and 2
To my surprise on first glance I found the pivot query working much faster than other queries.
Will post more about the performance implications later.But as for now here is the query.

declare @tbl as table(vendor int,ItemType varchar(40))

insert into @tbl
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'a' union all
select 2,'b' union all
select 2,'c' union all
select 3,'c'

select * from @tbl

--Method 1
select vendor from
(
select vendor,ItemType from @tbl
)u
pivot
(max(ItemType) for ItemType in ([a],[b],[c]))v
where a is not null and b is not null and c is not null

--Method 2
select vendor from @tbl where ItemType in('a','b','c')
group by vendor
having COUNT(case ItemType when 3 then 1 else 0 end)=3

--Method 3
select distinct vendor from
(
select vendor,(select COUNT(distinct ItemType)as rid from @tbl t2 where t1.vendor=t2.vendor and t2.ItemType in ('a','b','c'))as rid
from @tbl t1
)T where T.rid=3

--Method 4
select vendor from @tbl where ItemType in ('a','b','c')
group by vendor
having COUNT(distinct ItemType)=3

Here I needed to get the sum of values for intervals of every 5 minutes.First thing that came to my mind was to use the evil "Cursors" to achieve it.But somehow I found a different way of doing it.Here is the sample data

The o/p should be 7,13 & 3 as slot for which I needed to take the sum of values was every 5 minutes.Here is the query.

declare @tbl as table(dt datetime,value int)
insert into @tbl
select '03-03-2009 10:15:10',5 union all
select '03-03-2009 10:17:10',2 union all
select '03-03-2009 10:22:25',3 union all
select '03-03-2009 10:24:00',10 union all
select '03-03-2009 10:29:00' ,3

select * from @tbl

declare @slot int=5
select SUM(value)as SumOfSlots from @tbl group by DATEPART(minute,dt)/@slot*@slot

You can set the value of the @slot variable to value in minutes for which you want to have the Sum.