xml value method - 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

General SQL Server Forums

New to SQL Server Programming

xml value method

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-09-14 : 14:47:54

How do i get the result:

Rose
Cosma
Alex
Sunny
Louis
White

select profileProperty.value,
profileProperty.value.value('(//string/@value)[1]', 'VARCHAR(15)')
from @profileProperty profileProperty

declare @profileProperty table(UserID int, PropertyID int, Value xml)

insert @profileProperty (userid, PropertyID, Value)
select 3, 2, '<string>Rose</string>' union all
select 3, 3, '<string>Cosma</string>' union all
select 1, 2, '<string>Alex</string>' union all
select 1, 3, '<string>Sunny</string>' union all
select 2, 2, '<string>Louis</string>' union all
select 2, 3, '<string>White</string>'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 14:52:28

[code]
declare @profileProperty table(userid int, PropertyID int, Value xml)
insert @profileProperty (userid, PropertyID, Value)
select 3, 2, '<string>Rose</string>' union all
select 3, 3, '<string>Cosma</string>' union all
select 1, 2, '<string>Alex</string>' union all
select 1, 3, '<string>Sunny</string>' union all
select 2, 2, '<string>Louis</string>' union all
select 2, 3, '<string>White</string>'

select Value.query('data(.)')
from @profileproperty

output
-------------------------------------
Rose
Cosma
Alex
Sunny
Louis
White

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-09-14 : 15:18:27

thankx V

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-09-14 : 15:34:44

Now any ideas how to concatenate first and last name

declare @users table (UserID int)
declare @profileProperty table(UserID int, PropertyID int, Value xml)
declare @entityList table (EntityName varchar(50), EntityID int, Name varchar(50))

insert @users
select 1 union all
select 2 union all
select 3

insert @profileProperty (userid, PropertyID, Value)
select 3, 2, '<string>Rose</string>' union all
select 3, 3, '<string>Cosma</string>' union all
select 1, 2, '<string>Alex</string>' union all
select 1, 3, '<string>Sunny</string>' union all
select 2, 2, '<string>Louis</string>' union all
select 2, 3, '<string>White</string>'

result:
Rose Cosma
Alex Sunny
Louis White

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 15:54:24

how would i know which is first and last name?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-09-14 : 16:51:32

propertyid 2 = fname ; propertyid3 = lastname

result:
Rose Cosma
Alex Sunny
Louis White

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 17:09:16

[code]
declare @profileProperty table(userid int, PropertyID int, Value xml)
insert @profileProperty (userid, PropertyID, Value)
select 3, 2, '<string>Rose</string>' union all
select 3, 3, '<string>Cosma</string>' union all
select 1, 2, '<string>Alex</string>' union all
select 1, 3, '<string>Sunny</string>' union all
select 2, 2, '<string>Louis</string>' union all
select 2, 3, '<string>White</string>'

select userid,
max(case when PropertyID = 2 THEN CAST(Value.query('data(.)') as varchar(100)) end) as FirstName,
max(case when PropertyID = 3 THEN CAST(Value.query('data(.)') as varchar(100)) end) as LastName
from @profileproperty
group by userid

output
--------------------------------------------------------
userid FirstName LastName
1 Alex Sunny
2 Louis White
3 Rose Cosma

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-09-27 : 20:01:43

Thanks@!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-27 : 22:52:00

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources