1

I have a simple SELECT statement which selects few columns from a single table:

SELECT id, name, phone, address 
FROM tmp_user

Is it possible to change this query so that only id and name are in select and remaining details are in a xml node?

I expected output of this select 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 is of the returned table should be of XML type with required data. I know how the entire result set can be converted to XML using FOR XML. However I am looking only for part of the columns to be converted. Is it possible?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shreedhar Kotekar
  • 1,034
  • 10
  • 20

1 Answers1

1

Sure! 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

This gives me an output pretty exactly like the one you're looking for.

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 2
    Neat, works as required. Why didn't I think of this myself :( – Shreedhar Kotekar May 03 '12 at 20:25
  • I also need to add namespace to the xml. Something like ...
    ...
     Does anyone have idea how to do it? Since the select statement is nested I cannot use WITH XMLNAMESPACES.     – rageit Apr 25 '14 at 14:25