read xml values from string - 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)

read xml values from string

Author

Topic

purushotham216
Starting Member

14 Posts

Posted - 2009-12-09 : 04:17:07

hi to all ..thanks in advance
i have one table s.no(int),data(string)

i am inserting xml value as string into data.

xml value is like this
<StudyDeleteExtendedInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" type="ClearCanvas.ImageServer.Services.WorkQueue.DeleteStudy.Extensions.StudyDeleteExtendedInfo, ClearCanvas.ImageServer.Services.WorkQueue, Version=2.0.12026.35784, Culture=neutral, PublicKeyToken=null">
<ServerInstanceId>Host=ms0001xp/Pid=1400</ServerInstanceId>
<UserId>admin</UserId>
<UserName>admin</UserName> </StudyDeleteExtendedInfo>
how to get s.no,UserName,UserId from that table

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-09 : 04:42:03

hi,

from above sno means which values u have to return.

Thanks,
vikky.

purushotham216
Starting Member

14 Posts

Posted - 2009-12-09 : 05:05:05

1,2,3...

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-09 : 05:14:24

hi,

declare @xml xml
select @xml = '<StudyDeleteExtendedInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" type="ClearCanvas.ImageServer.Services.WorkQueue.DeleteStudy.Extensions.StudyDeleteExtendedInfo, ClearCanvas.ImageServer.Services.WorkQueue, Version=2.0.12026.35784, Culture=neutral, PublicKeyToken=null">
<ServerInstanceId>Host=ms0001xp/Pid=1400</ServerInstanceId>
<UserId>admin</UserId>
<UserName>admin</UserName> </StudyDeleteExtendedInfo>'

declare @tab table( id int identity(1,1), UserId varchar(255),UserName varchar(255))

insert into @tab
select
T.DATA.value('UserId[1]','varchar(20)') as UserId,
T.DATA.value('UserName[1]','varchar(20)') as UserName
from @xml.nodes('StudyDeleteExtendedInfo') as T(DATA)

select * from @tab

Thanks
vikky.

purushotham216
Starting Member

14 Posts

Posted - 2009-12-09 : 05:17:01

Thank you somuch vinky

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-09 : 05:39:58

welcome

purushotham216
Starting Member

14 Posts

Posted - 2009-12-09 : 05:40:19

thanks for reponse vinkey..
i have some doubt..
How can i pass mutiple strings(data)..?
because i need to dispaly multiple results

1 user user
2 admin admin
3 user1 user1

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-09 : 06:12:22

[code]declare @MyTable table ([s.no] int, data varchar(max))

insert @MyTable
select 1,
'<StudyDeleteExtendedInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" type="ClearCanvas.ImageServer.Services.WorkQueue.DeleteStudy.Extensions.StudyDeleteExtendedInfo, ClearCanvas.ImageServer.Services.WorkQueue, Version=2.0.12026.35784, Culture=neutral, PublicKeyToken=null">
<ServerInstanceId>Host=ms0001xp/Pid=1400</ServerInstanceId>
<UserId>user </UserId>
<UserName>user </UserName> </StudyDeleteExtendedInfo>'
union all select 2,
'<StudyDeleteExtendedInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" type="ClearCanvas.ImageServer.Services.WorkQueue.DeleteStudy.Extensions.StudyDeleteExtendedInfo, ClearCanvas.ImageServer.Services.WorkQueue, Version=2.0.12026.35784, Culture=neutral, PublicKeyToken=null">
<ServerInstanceId>Host=ms0001xp/Pid=1400</ServerInstanceId>
<UserId>admin</UserId>
<UserName>admin</UserName> </StudyDeleteExtendedInfo>'
union all select 3,
'<StudyDeleteExtendedInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" type="ClearCanvas.ImageServer.Services.WorkQueue.DeleteStudy.Extensions.StudyDeleteExtendedInfo, ClearCanvas.ImageServer.Services.WorkQueue, Version=2.0.12026.35784, Culture=neutral, PublicKeyToken=null">
<ServerInstanceId>Host=ms0001xp/Pid=1400</ServerInstanceId>
<UserId>user1</UserId>
<UserName>user1</UserName> </StudyDeleteExtendedInfo>'

; with t1 as (select [s.no], cast(data as xml) as xml from @MyTable)
select [s.no],
c.value('UserId[1]', 'varchar(20)') as UserId,
c.value('UserName[1]', 'varchar(20)') as UserName
from t1 cross apply xml.nodes('StudyDeleteExtendedInfo') t(c)[/code]Why don't you use the xml data type for your data column?

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-09 : 07:21:43

hi,

u can follow this one also.

select t.[s.no],
x.UserId,
x.UserName
from (
select [s.no],cast(data as xml) as data from @MyTable ) t
cross apply( select
T.DATA.value('UserId[1]','varchar(20)') as UserId,
T.DATA.value('UserName[1]','varchar(20)') as UserName
from data.nodes('StudyDeleteExtendedInfo') as T(DATA) )as x

Thanks,
vikky.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources