Xml exist function with datatypes

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

General SQL Server Forums

New to SQL Server Programming

Xml exist function with datatypes

Author

Topic

afzaal820
Starting Member

19 Posts

Posted - 2011-06-11 : 04:33:08

declare @Xml xml

set @Xml = '<demo>
<nodes>
<node>
<age>12</age>
<info>test1</info>
<dob>2011-01-01</dob>
</node>
<node>
<age>14</age>
<info>test2</info>
<dob>2011-01-02</dob>
</node>
</nodes>
</demo>'

Create table #TempTable
(
rowid int identity(1,1),
xmldata xml
)

INSERT #TempTable(xmldata) VALUES (@Xml)

-- Data type conversion ----
select * FROM #TempTable
WHERE xmldata.exist('/demo/nodes/node/dob[(text() cast as xs:date?) = xs:date("2011-01-02")]') = 1

Drop table #TempTable

Query give error saying that

"Cannot explicitly convert from 'xdt:untypedAtomic *' to 'xs:date ?'"

Can anybody help in fixing the problem.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-11 : 08:55:42

Are you trying to pick out only the dob, or just the node, or the row where you have a dob = 2011-01-02? See if this works for you, if not can you post the output you want to get?


select * FROM #TempTable 
--WHERE xmldata.exist('/demo/nodes/node/dob[(text() cast as xs:date?) = xs:date("2011-01-02")]') = 1 
WHERE xmldata.exist('/demo/nodes/node[dob = xs:date("2011-01-02")]') = 1

afzaal820
Starting Member

19 Posts

Posted - 2011-06-11 : 12:20:47

Thanks a lot, it worked, meanwhile I got it worked with my syntax as well

select * FROM #TempTable
WHERE xmldata.exist('/demo/nodes/node/dob[(text()[1] cast as xs:date?) = xs:date("2011-01-02")]') = 1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-11 : 13:34:48

Intuitively, I would suspect that the way I had written it may be ever so slightly faster. Regardless of which method you use, I hope your intention is that the query should return both the node elements - test1 and test2, because that is what both of them will do.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources