criteria in xml structure - SQL Server Forums

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)

criteria in xml structure

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-06-28 : 02:44:12

Hi

I have this query...


SELECT t.u.value('(country/text())[1]', 'nvarchar(30)') as [country] ,
t.u.value('(address/text())[1]', 'nvarchar(30)') as [address],
t.u.value('(id/text())[1]', 'Int') as [id],
t.u.value('(zipcode/text())[1]', 'nvarchar(30)') as [zipcode],

tbl.DateAdded, ISNULL(tbl.BID, 0) AS BID, tbl.ID AS PID


FROM tbl_Test tbl
CROSS APPLY XmlData.nodes('/order/mycard')t(u) 
WHERE     (tbl.DateAdded BETWEEN CONVERT(DATETIME, '2009-05-20', 102) AND CONVERT(DATETIME, '2009-05-28', 102))

How can I add a modify the query, so that I also have it like this int the xml part...

AND zipcode LIKE '123 45'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-28 : 03:17:47

See here how to search XML data
http://weblogs.sqlteam.com/peterl/archive/2008/09/01/Updated-XML-search-test-case-with-variables.aspx

E 12°55'05.63"
N 56°04'39.26"