| Author |
 Topic  |
|
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
 Posted - 09/14/2012 : 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
India
52326 Posts |
Posted - 09/14/2012 : 14:52:28
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 09/14/2012 : 15:18:27
|
| thankx V |
|
|
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 09/14/2012 : 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
India
52326 Posts |
Posted - 09/14/2012 : 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 - 09/14/2012 : 16:51:32
|
propertyid 2 = fname ; propertyid3 = lastname
result:
Rose Cosma
Alex Sunny
Louis White |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 09/14/2012 : 17:09:16
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 09/27/2012 : 20:01:43
|
| Thanks@! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 09/27/2012 : 22:52:00
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
xml value method