SQL 2005 HAS BETTER OPTIONS FOR PRODUCING THE RESULTS

THE "QUERY FROM HELL" IS ROUGH AROUND THE EDGES. i HAVE WRITTEN 5 OF THESE, WHICH RUN IN A PRODUCTION ENVIRONMENT.

BUT WE ARE CONVERTING ALL OF THEM TO THE XML PATH
STATEMENT IN SQL 2005.

here is the error I got
sql 2005 Msg 1086 for xml path using derived table syntax

Msg 1086, Level 15, State 1, Line 72
The FOR XML clause is invalid in views, inline functions, derived
tables, and subqueries when they contain a set operator. To work
around, wrap the SELECT containing a set operator using derived table
syntax and apply FOR XML on top of it.


and here is the work around I found:
--------------------------------------------------------
-- #1 WORKS JUST FINE
--------------------------------------------------------
-- select 'dude' as 'GovernmentId'
-- union select 'dude2' as 'GovernmentId'
-- for XML PATH('')
--
-- produces:
-- <GovernmentId>dude</GovernmentId>
-- <GovernmentId>dude2</GovernmentId>
--
----------- EXAMPLE #2>
--
-- select
-- '1' as 'GovernmentId/@id',
-- 'dude' as 'GovernmentId'
-- union select
-- '2' as 'GovernmentId/@id',
-- 'dude2' as 'GovernmentId'
-- for XML PATH('')
--
-- produces:
-- <GovernmentId id="1">dude</GovernmentId>
-- <GovernmentId id="2">dude2</GovernmentId>
--
----------- EXAMPLE #3>
--
-- select '' as 'PersonName',
-- (select
-- PersonId as 'Identity/@Id',
-- PersonName as 'Identity'
-- from
-- (select
-- '1' as PersonId,
-- 'dude' as PersonName
-- union select
-- '2' as PersonId,
-- 'dude2' as PersonName
-- ) as t1
-- for xml Path(''), Type
-- ) as 'Person'
-- for xml PATH('Person')
--
-- PRODUCES:
-- <Person>
-- <PersonName></PersonName>
-- <Person>
-- <Identity Id="1">dude</Identity>
-- <Identity Id="2">dude2</Identity>
-- </Person>
-- </Person>
--
----------- EXAMPLE #4>
--
-- select '' as 'Person/PersonName',
-- (select
-- PersonId as 'Identity/@Id',
-- PersonName as 'Identity'
-- from
-- (select
-- '1' as PersonId,
-- 'dude' as PersonName
-- union select
-- '2' as PersonId,
-- 'dude2' as PersonName
-- ) as t1
-- for xml Path(''), Type
-- ) as 'Person'
-- for xml PATH('')
-- PRODUCES:
--<Person>
-- <PersonName></PersonName>
-- <Identity Id="1">dude</Identity>
-- <Identity Id="2">dude2</Identity>
--</Person>
--------------------------------------------------------


Although, Thanks for the tips using sqlxml:

Also here is a good reference (just was not enough for me)..

http://technet.microsoft.com/en-us/library/ms345137(SQL.90).aspx

Thanks,

Doug Lubey of Louisiana
www.DougLubey.com