1

My SQL query is:

select MedicalHeightValue, MedicalWeightValue 
from TableA

My column names & values are: 

MedicalHeightValue(67), MedicalWeightValue(220)

Output should be like:

<HealthAttribute>
   <Identifier>MedicalHeightValue</Identifier>
   <Value>67</Value>
</HealthAttribute>
<Healtttribute>
   <Identifier>MedicalWeightValue</Identifier>
   <Value>220</Value>
</HealthAttribute>
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
Poornima
  • 11
  • 1

2 Answers2

0

Try something like this:

;WITH CteData AS
(
    SELECT  
        'MedicalHeightValue' AS 'Identifier', MedicalHeightValue AS 'Value' 
    FROM dbo.TableA

    UNION ALL

    SELECT  
       'MedicalWeightValue' AS 'Identifier', MedicalWeightValue AS 'Value' 
    FROM dbo.TableA
)
SELECT *
FROM CteData
FOR XML PATH('HealthAttribute')
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Here MedicalHeightValue ,MedicalweightValue both are column names. So both the column names should come between identifier as shown in format above. – Poornima Oct 21 '13 at 11:00
0
select C.Value, C.Identifier
from TableA
    outer apply (values
        ('MedicalHeightValue', MedicalHeightValue),
        ('MedicalWeightValue', MedicalWeightValue)
    ) as C(Identifier, Value)
for xml path('HealthAttribute')

you can also do this without explicitly specifying columns:

with cte(data) as (
    select * from TableA for xml path(''), type
), cte2 as(
    select
        T.C.value('.', 'nvarchar(max)') as Value,
        T.C.value('local-name(.)', 'nvarchar(max)') as Identifier
    from cte as c
        outer apply c.data.nodes('*') as T(C)
)
select *
from cte2
for xml path('HealthAttribute')

but I think it's a bit overkill for your task

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197