October 28, 2011 at 8:08 am
Hi Guys
I'm new to xquery and am wiritng a query to retrieve alot of data from a xml field. In the where clause I need to limit the query based on the follow up date which is contained in the element of the xml. How can I do this? Your help is greatly appreciated.
e.g. I tried this but it doesn't work. I know it's a simple question but I can't figure it out.
select *
from table
where [Case].[Xml].value('(/CustomerQuestions/Questions[@group="Followup"]/Question[@type="FollowupDate"]/Answer/@data)[1]', 'datetime') between '2011-01-01' and '2011-10-01'
Thanks
G
October 28, 2011 at 8:15 am
I'd have to see the table definition and a sample of the XML in it before I could help much.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2011 at 8:27 am
This was removed by the editor as SPAM
October 28, 2011 at 8:31 am
Here is part of the xml - the whole xml field is massive so cannot provide it all
<CustomerQuestions>
<Question type="FollowupDate">
<Answer code="Text" data="2011-10-10" />
</CustomerQuestions>
PS> I'll try the xmlcolumn exist method too now - thanks
October 28, 2011 at 9:48 am
Here's a sample of how to do it with the exist() function.
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T ;
CREATE TABLE #T
(ID INT IDENTITY
PRIMARY KEY,
X XML) ;
INSERT INTO #T
(X)
VALUES ('<CustomerQuestions>
<Question type="FollowupDate">
<Answer code="Text" data="2011-10-10" />
</Question>
</CustomerQuestions>'),
('<CustomerQuestions>
<Question type="FollowupDate">
<Answer code="Text" data="2011-09-10" />
</Question>
</CustomerQuestions>') ;
SELECT *
FROM #T
WHERE X.exist('/CustomerQuestions/Question/Answer[(@data cast as xs:date?) ge xs:date("2011-01-01")
and (@data cast as xs:date?) lt xs:date("2011-10-01")]') = 1 ;
I have a table with 5.3M rows of XML data (as well as some other columns of relational data). I just ran a test of exist() where using value() in a Where clause.
There are Primary and Value XML indexes on the column being queried.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SET STATISTICS TIME, IO ON;
SELECT TOP 1000
*
FROM dbo.MyTable
WHERE CustomData.exist('/row[(@TextOffers cast as xs:string?) eq "4232372320"]') = 1 ;
SELECT TOP 1000
*
FROM dbo.MyTable
WHERE CustomData.value('(/row/@TextOffers)[1]', 'varchar(100)') = '4232372320' ;
SET STATISTICS TIME, IO OFF;
Here are the results:
(1 row(s) affected)
Table 'MyTable'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'xml_index_nodes_1170103209_32000'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
Table 'xml_index_nodes_1170103209_32000'. Scan count 5329977, logical reads 29258568, physical reads 14531, read-ahead reads 686708, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTable'. Scan count 1, logical reads 482899, physical reads 1715, read-ahead reads 478714, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 110906 ms, elapsed time = 440110 ms.
The exist() query took so little time the stats couldn't even quantify it. That bears out with repeated testing. The longest time I could get on an equality comparison with exist() was 6 milliseconds.
The value() = X method, took a minimum time of over 7 minutes, even with the same value repeated (cached data and plan available).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2011 at 9:51 am
WOW -thanks for that GSquared and for putting the effort into it. I'll give it a go..
Have a good weekend!
October 28, 2011 at 10:01 am
Holly *&?() *N"/ )!"(&*/ *$?"/)$( &* _ $/(& $)/"(é
That's the polite version! :hehe:
Tx for posting this!
November 1, 2011 at 9:50 am
Hi Guys
I can't get the exists method to work because the data in my xml does not specify timezone. At least this is what I think the problem is. I run the following to test the method and get..
declare @x xml
declare @f bit
set @x = ' <Answer code="Text" data="2011-10-25" />'
set @f = @x.exist('/Answer[(@data cast as xs:date?) eq xs:date("2011-10-25")]')
select @f
--Msg 9319, Level 16, State 1, Line 5
--XQuery [exist()]: Static simple type validation: Invalid simple type value '2011-10-25'.
But if I include the Z for Zulu time for example I get 0 (false) so it works correctly
declare @x xml
declare @f bit
set @x = '<root Somedate = "2002-01-01Z"/>'
set @f = @x.exist('/Answer[(@data cast as xs:date?) eq xs:date("2011-10-25Z")]')
select @f
--0
November 1, 2011 at 12:19 pm
Try this:
declare @x xml
declare @f bit
set @x = ' <Answer code="Text" data="2011-10-25" />'
set @f = @x.exist('.[(concat(string((/Answer/@data)[1]), "Z") cast as xs:date?) eq xs:date("2011-10-25Z")]');
select @f
You can also programmatically generate the date to compare using either the sql:variable() or sql:column() functions.
Looks like:
DECLARE @CompareDate VARCHAR(100);
declare @x xml
declare @f bit
set @x = ' <Answer code="Text" data="2011-10-25" />'
SET @CompareDate = '2011-10-25Z';
set @f = @x.exist('.[(concat(string((/Answer/@data)[1]), "Z") cast as xs:date?) eq xs:date("2011-10-25Z")]');
select @f
set @f = @x.exist('.[(concat(string((/Answer/@data)[1]), "Z") cast as xs:date?) eq xs:date(sql:variable("@CompareDate"))]');
select @f
Since you can't use SQL datetime in XQuery in SQL 2005, if you want a datetime input parameter or column, you'll need to use Convert and add a "Z" to the end of it to get it to work for you.
The first test script I wrote works in SQL 2008 R2, but not in SQL 2005. This version, with the "Z" on it, works in SQL 2005.
(Took a bit to research this one. Was interesting digging into it.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2011 at 10:48 am
I finally got my query working thanks to your help GSquared. 🙂
I had a problem getting it to work for ages after modifying it with the code you provided and discovered that I never declared the namespace at the top of the query. Once I did that it worked!!
November 2, 2011 at 11:02 am
Yep, namespace matters!
Glad I could help.
The documentation for XQuery is junk. I've been through a lot of headaches working it out, so anything I can do to help others avoid the same headaches, I try to help with.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply