Select multiple columns of a result set as XML

Question

I have a simple statement SELECTthat selects multiple columns from a single table:

SELECT id, name, phone, address 
FROM tmp_user

Can I change the query to only idand nameare in the list, and the remaining part - in a xml node?

I expected that the output of this choice should be

id  name        extra data
1   Shreedhar   <data><phone>...</phone><address>...</address></data>
2   John Doe    <data><phone>...</phone><address>...</address></data>
3   Jane Doe    <data><phone>...</phone><address>...</address></data>

The last column of the returned table should have an XML type with the required data. I know how the entire result set can be converted to XML using FOR XML. However, I am only looking for part of the columns that need to be converted. Is it possible?

Answer 1

Of course! No problem - try something like this:

SELECT 
    id, name,
    (SELECT phone, address
     FROM dbo.tmp_user u2
     WHERE u2.id = u1.id
     FOR XML PATH('data')) AS 'ExtraData'
FROM    
   dbo.tmp_user u1

, , .