T-Sql Select Parse XML - 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)

T-Sql Select Parse XML

Author

Topic

ljp099
Yak Posting Veteran

79 Posts

Posted - 2007-12-03 : 21:13:26

I have an nvarchar column with data marked up as XML. Im trying to run a t-sql query to parse the xml and return a set of records.

I have this query running:
select * from content where content.folder_id=30 AND CHARINDEX('<IsViewableField1>true</IsViewableField1>', content_html) > 1 AND CHARINDEX('<Webinar>Case Study</Webinar>', content_html) > 1

the issue Im having is that I only want to see the most recent 5 records. So the query should be something like:

select TOP 5 * from content where content.folder_id=30 AND CHARINDEX('<IsViewableField1>true</IsViewableField1>', content_html) > 1 AND CHARINDEX('<Webinar>Case Study</Webinar>', content_html) > 1 ORDER BY ('<PublishDate>', content_html)

I dont know how I could/would use a t-sql function in the ORDER BY Clause to parse the publish date (<PublishDate>2003-05-01</PublishDate>) and return the 5 most recently published documents.

Any help would be appreciated.

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2007-12-04 : 11:18:26

are you using sql 2005? if so you can use the .nodes() and .query() functions.