Hello! I'm new to MSSQL. We have 15 tables with a total number of columns 500 pieces. Tell me how to combine the results of XML into one document? UNION ALL does not help.

I added a table with the MSSQL server addresses and their IDs to the database. Therefore, it became necessary to exclude it from the results. Took request:

Select( SELECT 'TABLE1' as 'name' , --выводит в тэге XML имя таблицы ( SELECT * FROM 'TABLE1' --таблица 1 FOR XML RAW ('Columns') ,type ) --формат выгрузки данных FOR XML RAW, type --формат выгрузки данных SELECT 'TABLE2' as 'name' , ( SELECT * FROM 'TABLE2' FOR XML RAW ('Columns') ,type ) FOR XML RAW, type)

But it cannot be combined with UNION. The point is to have column headers in the upload. And the results were written in one file.

Help me please.

For 2 days I could not win ... I would be grateful for any help and examples.

Before that I used:

 SET NOCOUNT ON DECLARE @CMD varchar(max) = '' DECLARE @AllTablesXML table (XMLData XML) SELECT @CMD += ';SELECT '''+TABLE_NAME+''' as ''@name'' , ( SELECT * FROM ' + QUOTENAME(T.TABLE_SCHEMA) + '.' + QUOTENAME(T.TABLE_NAME) + 'FOR XML RAW (''Columns'') ,type ' + CHAR(10) + ' ) FOR XML RAW, type' FROM INFORMATION_SCHEMA.TABLES T --WHERE T.TABLE_NAME in ('your List of tables') Uncomment if you need to extract specific tables --SELECT @CMD --Exec (@CMD) INSERT INTO @AllTablesXML EXEC (@CMD) SELECT XMLData FROM @AllTablesXML FOR XML RAW
  • Give an example of the data in the tables and the XML you want to get at the output. You can fix the question on the "edit" link in question. And delete the previous similar question - Mike

1 answer 1

I solved this question:

 SELECT 'TABLE1' as 'name' , ( SELECT * FROM 'TABLE1' FOR XML RAW ('Columns') ,type ) --- нужно добавить в () (скобки) Итоговый результат кода: (SELECT 'TABLE1' as 'name' , (SELECT * FROM 'TABLE1' FOR XML RAW ('Columns') ,type)) UNION ALL SELECT 'TABLE2' as 'name' , (SELECT * FROM 'TABLE2' FOR XML RAW ('Columns') ,type)) FOR XML RAW, type

As a result, we obtain:

 <row name="TABLE1"> <Columns ID="5" iSPCModuleNo="22" iMidx="100" " /> <Columns ID="4" iSPCModuleNo="255" iMidx="4" /> </row> <row name="TABLE2"> <Columns ID="133" iBSIModuleNo="133" iLocalIPAddr1="10" /> <Columns ID="134" iBSIModuleNo="134" iLocalIPAddr1="10" /> </row>

I repeat that with the select from the question, the Studio gives 2 files with the results, but one is needed.

Here is an example: Example of normal output