SELECT FOR XML, How to use?

Question

This statement below:

SELECT
    ....
FROM
    ....
WHERE
    ....
FOR XML PATH('Booking'), ROOT('doc')

This returns all the records in one XML string. I wish for each record to have its own XML string. Does this make sense? Is it possible. Does it make sense to do this?

I am using Microsoft SQL Server 2008

Answer 1

If I understand correctly, you want each row to produce a separate XML document (there is no such thing as an 'XML string'). You need to run each row through the FOR XML. For example, take each row from the table and use the CROSS APPLY operator to project a single row FOR XML TYPE. Use a row table value constructor for the join. Eg. using master..spt_values:

select t.*
 from master..spt_values s
 cross apply (
 select s.* 
 from (values (1) ) as t(c) 
 for xml path('Boookin'), root('Doc'), type) as t(x)

will return one XML document per row, with a structure like:

<Doc>
  <Boookin>
    <name>rpc</name>
    <number>1</number>
    <type>A  </type>
    <status>0</status>
  </Boookin>
</Doc>

Substitute master..spt_values with your table of choice.