xml parsing for filtering help needed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

xml parsing for filtering help needed

Author

Topic

deanvanrooyen
Starting Member

2 Posts

Posted - 2007-11-18 : 15:49:59

hi,

I can filter basic xml checks but need help with this one....
Server MS SQL 2005
In my table I have a few columns, with one being of type XML.

This is basic schema for the table

ID NAME PACKAGEXML
1 DB1 <typeinfo><type>Continuous</type></typeinfo>
2 DB2 <typeinfo><type>Recurring</type><days><day>Sunday</day></days></typeinfo>
3 DB3 <typeinfo><type>Recurring</type><days><day>Monday</day><day>Wednesday</day></days></typeinfo>

I need to filter my select statement on the xml column. I take in input parameters which is a start and end date. If the difference between the 2 is less than 7 days then I need to parse the XML column. Only if the column is type Recurring then I need to get the days of the week from the start to end date parameters and check if they exist in the recurring days. If it is type Continuous then return true.

so if start date = 2007-11-01 and end date is 2007-01-18 then dont need to check the xml column
if start date = 2007-11-17 and end date is 2007-01-18 then check xml and this would return row id 2 as the 2007-01-18 is a Sunday and this is in the days for ID 2.

I was thinking of doing this sort of filter

select * from table
where 1 = case when DATEDIFF(dd,@startdate,@endDate) < 7
THEN
--get days of week from between @startdate and @endDate
--check if these days occur in the xml if type Recurring
ELSE 1 END

I need help with the part
--get days of week from between @startdate and @endDate
--check if these days occur in the xml

Please help! If this is not the best where clause let me know, I am open to suggestions thanks!!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-19 : 03:54:47

The following procedure takes two dates, find days between them inclusive of start & end and returns corresponding xml id that includes those days:-

CREATE PROC TestProc
@StartDate datetime,
@EndDate datetime
AS
BEGIN
DECLARE @Day varchar(10)
WHILE DATEDIFF(d,@StartDate,@EndDate) >0
BEGIN

SELECT t2.id,t2.col FROM(
SELECT t1.id,CAST(PackageXml.query('data(/typeinfo[type="Recurring"]/days/day)') AS VARCHAR(20)) AS 'col'
FROM t1) t2
WHERE t2.col Like '%' + DATENAME(dw,@StartDate) + '%'

SET @StartDate=DATEADD(d,1,@StartDate)
END
END

deanvanrooyen
Starting Member

2 Posts

Posted - 2007-11-19 : 04:13:14

thanks - I will try that but it looks like exactly what I need - I will get the rowcount and return that as a bit 0 or 1 if it does return rows thanks!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources