SQL to XML

Just came across this neat trick that uses SQL to generate XML on the fly:

CODE:
  1. DECLARE @x xml
  2. SET @x=(select geoAreaCode
  3. , geoLevelCode
  4. , geoLevelId
  5. , geoAreaId
  6. from tbl_geoLevel
  7. where uuid =
  8. for xml raw('geoArea'), root('geoAreas'), type)
  9. SELECT @x AS geoAreaInfo

The above will create an XML structure that has a root value of geoAreas and child elements of geoArea for each row found.


About this entry