0

I'm generating xml from tables with queries like this

 SELECT * 
   FROM Rp 
FOR XML AUTO

 SELECT * 
   FROM Ind 
FOR XML AUTO

I would like to combine a bunch of these and generate a single xml file probably something I can automate in VS2010

any pointers?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
AppDeveloper
  • 927
  • 11
  • 21

3 Answers3

0
    SELECT ID,SUBSTRING(
    (
    SELECT ',' + [Name]
    FROM MyTable C
    WHERE P.ID=C.ID
    ORDER BY [Name]
    FOR XML PATH('')
    ),2,2000) as [Name]

FROM MyTable P
GROUP BY ID
Mou
  • 15,673
  • 43
  • 156
  • 275
0

found it, this works

Select 
   ( SELECT * FROM Rp FOR XML AUTO, TYPE)
,
   ( SELECT * FROM Ind FOR XML AUTO, TYPE)

  FOR XML PATH('Root')
AppDeveloper
  • 927
  • 11
  • 21
-1

combine how? you could simply use UNION

SELECT * FROM Rp  
FOR XML AUTO 
UNION 
SELECT * FROM Ind  
FOR XML AUTO
Mladen Prajdic
  • 15,457
  • 2
  • 43
  • 51
  • 1
    I get this error running this query it seems you can't do union on FOR XML AUTO selects – AppDeveloper Nov 11 '10 at 04:39
  • This is the error "Incorrect syntax near the keyword 'UNION'" pressed enter too soon :) – AppDeveloper Nov 11 '10 at 04:40
  • also to answer your question about how I want to get all of the data from each of the tables (Rp, Ind and several others) but I want to combine all the resultant xml fragments into one xml – AppDeveloper Nov 11 '10 at 04:41