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.
| Author |
Topic |
|
ljp099
Yak Posting Veteran
79 Posts |
 Posted - 2010-12-10 : 13:41:49
|
| I have an Ntext column that stores xml formatted data. I would like to retrieve the value of the href tag (bold below) that follows the <PDF_FIle> node. I have no idea how to find this substring in t-sql. Any help would be appreciated. <Research_Document_PDF_File> <PDF_FIle> <a title="The Corporate Learning Factbook: Benchmarks and Analysis of U.S. Corporate Learning & Development" href="/docs/factbook_v5_3.pdf">The Corporate Learning Factbook: Benchmarks and Analysis of U.S. Corporate Learning & Development</a> </PDF_FIle> </Research_Document_PDF_File> |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2010-12-10 : 16:18:50
|
| [code]select cast(cast(YOURCOLUMN as varchar(max)) as xml).value( '(/Research_Document_PDF_File/PDF_FIle/a/@href)[1]','VARCHAR(32)');[/code]All that casting is required because your column type is ntext. You might even be able to use[code]select cast(cast(YOURCOLUMN as varchar(max)) as xml).value('(//@href)[1]','varchar(100)');[/code]Also, remember that xml is case-sensitive regardless of the collation of your database/table. |
 |
|
|
|
|
|
Help with Select