HELP Using XQuery to get a URL value Stored

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

HELP Using XQuery to get a URL value Stored

Author

Topic

yaf23
Starting Member

9 Posts

Posted - 2009-11-18 : 00:14:44

Hello,

This is a continiuance in a way to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=135760

However my question is that I have a URL value stored in XML like this:

<AudioFile><a title="TEST AUDIO 1" href="test.aspx?ItemID=242">TEST AUDIO 1</a></AudioFile>

However when I retrieve it using XQuery like this:

DECLARE @sql nvarchar(4000) SELECT @sql = content_html FROM content WHERE content_id = 244 declare @x as xml set @x= @sql select x.i.value('.','varchar(4000)') as Audio from @x.nodes('//root/AudioFile')x(i)

I get the return of just the title no hyperlink which is a problem for me cause I want the behavior of having the link to be able to be used later in an application.

Any help Pleaaaassseee??

yaf23
Starting Member

9 Posts

Posted - 2009-11-18 : 10:57:51

The issue is that the return value strips out the <a tags and just shows the text as my result type in this case:

TEST AUDIO 1

quote:
Originally posted by yaf23

Hello,

This is a continiuance in a way to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=135760

However my question is that I have a URL value stored in XML like this:

<AudioFile><a title="TEST AUDIO 1" href="test.aspx?ItemID=242">TEST AUDIO 1</a></AudioFile>

However when I retrieve it using XQuery like this:

DECLARE @sql nvarchar(4000) SELECT @sql = content_html FROM content WHERE content_id = 244 declare @x as xml set @x= @sql select x.i.value('.','varchar(4000)') as Audio from @x.nodes('//root/AudioFile')x(i)

I get the return of just the title no hyperlink which is a problem for me cause I want the behavior of having the link to be able to be used later in an application.

Any help Pleaaaassseee??

yaf23
Starting Member

9 Posts

Posted - 2009-11-23 : 10:57:50

So I found my answer after doing some research just sharing it with others incase they may need something similar:

DECLARE @sql XML
SELECT @sql = '<root><AudioFile><a title="TEST AUDIO 1" href="test.aspx?ItemID=242">TEST AUDIO 1</a></AudioFile></root>'
FROM content
WHERE content_id = 244
SELECT '<a title="'+x.i.value('@title','nvarchar(4000)')+'" href="'+
x.i.value('@href','nvarchar(1000)')+'" >'+
x.i.value('.','nvarchar(1000)')+'</a>' as Audio
FROM @sql.nodes('//root/AudioFile/a')x(i)

of course the variable @sql can be your actual column name

this now gets me the full link not just the title.

yaf23
Starting Member

9 Posts

Posted - 2009-11-23 : 14:02:21

A friend of mine(Phil Cutler) saw this and found a cleaner solution below:

DECLARE @sql nvarchar(4000)
SELECT @sql = '<root><AudioFile><a title="TEST AUDIO 1" href="test.aspx?ItemID=242">TEST AUDIO 1</a></AudioFile></root>'
FROM content
WHERE content_id = 244
declare @x as xml
set @x= @sql
select cast(x.i.query('a') as varchar(max)) as Audio
from @x.nodes('//root/AudioFile')x(i)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources