Retrieving XML Text - 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)

Retrieving XML Text

Author

Topic

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-30 : 02:10:09

Hi,

declare @str xml
set @str = '<Root><Name>Hello</Name></Root>'
select @str.query('/Root/Name');

when i query this i get the result as
<Name>Hello</Name>
my requirement is i need to get only 'Hello' and not the nodes

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-30 : 02:14:02

hi,

i got the answer . include text()

select @str.query('//title/text()')

Iam a slow walker but i never walk back

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-30 : 02:19:07

try this one
declare @str xml
set @str = '<Root><Name>Hello</Name></Root>'

SELECT node.query('./text()') AS NodeValue
FROM @str.nodes(N'//*') T(node)

SELECT
t.n.value('text()[1]', 'VARCHAR(100)') AS NodeText
FROM @str.nodes('/*/*') AS t(n)

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-30 : 02:44:13

thanks useful one

Iam a slow walker but i never walk back

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-30 : 02:46:48

quote:
Originally posted by dineshrajan_it

thanks useful one

Iam a slow walker but i never walk back

select @str.query('.//text()')

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources