How to return multiple tables as one XML?

  • A+

I have the following script:

DECLARE @columns TABLE (     Caption varchar(50),     Width int);  INSERT INTO @columns  VALUES ('Id', 0), ('Name', 100);  DECLARE @rows TABLE (     Id int,     [Name] varchar(50));  INSERT INTO @rows  VALUES (1, 'John'), (2, 'Steve');  SELECT *,     (SELECT *      FROM @rows      FOR XML PATH('Row'), ROOT('Rows'), TYPE, ELEMENTS) FROM @columns FOR XML PATH('Column'), ROOT('Results'), TYPE, ELEMENTS; 

And I need to return the following XML:

<Results>     <Columns>         <Column>             <Caption>Id</Caption>             <Width>0</Width>         </Column>         <Column>             <Caption>Name</Caption>             <Width>100</Width>         </Column>     </Columns>     <Rows>         <Row>             <Id>1</Id>             <Name>John</Name>         </Row>         <Row>             <Id>2</Id>             <Name>Steve</Name>         </Row>     </Rows> </Results> 

The idea is that I will convert the XML into a DataSet with 2 DataTables (one for Columns and the other for Rows). I will use this to populate a DataGridView.

However, my problem is that the XML I'm generating currently is malformed and isn't the same as I'm expecting.

What is the correct syntax to generate the XML as expected?


This gets you the result you want, based on the data we have:

SELECT (SELECT Caption,                Width         FROM @columns         FOR XML PATH('Column'),TYPE) AS [Columns],        (SELECT Id,                [Name]         FROM @rows         FOR XML PATH('Row'),TYPE) AS [Rows] FOR XML PATH ('Results'); 


:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: