Алексей

select 
      g.gid, 
      g.name
from  groups g
inner join  groupmembers gm ON gm.memberId = 1 AND g.gid = gm.groupId

select 
      g.name, 
   COUNT(gm.id) as CountGM
from  groups g
inner join  groupmembers gm ON gm.memberId = 1 AND g.gid = gm.groupId
group by g.name

select 
[ id пользователя]
 ISNULL(U.ID, system.id] as [ID сообщения]
 from tbl_notice  N
left join user  U ON  U.ID=N.[id сообщения] and type="user"
left join Systems  S ON  S.ID=N.[id сообщения] and type="System"

declare @xdat xml 

-- получаем xml-файл
SET @xdat  = (SELECT [DataXml]  FROM [TaskXml]  where TaskDocGUID='1A91AED3-B5DF-4526-81E9-D54C16BB82A7')

if object_id('TempDb..#AllDataTable') > 0 drop table #AllDataTable
SELECT Product.ref.value('declare namespace pref="http://fsrar.ru/WEGAIS/ProductRef";  (pref:Type/text())[1]', 'nvarchar(50)') AS [Type]
	,Product.ref.value('declare namespace   pref="http://fsrar.ru/WEGAIS/ProductRef";  (pref:FullName/text())[1]', 'nvarchar(255)') AS FullName
	,Product.ref.value('declare namespace   pref="http://fsrar.ru/WEGAIS/ProductRef";  (pref:ShortName/text())[1]', 'nvarchar(64)') AS ShortName
	,Product.ref.query('declare namespace   pref="http://fsrar.ru/WEGAIS/ProductRef";   pref:Producer[1]') AS Producer
	,Producer.ref.value('declare namespace  oref="http://fsrar.ru/WEGAIS/ClientRef";   (oref:ClientRegId/text())[1]', 'nvarchar(50)') AS ClientRegIdProducer
	,Producer.ref.value('declare namespace  oref="http://fsrar.ru/WEGAIS/ClientRef";   (oref:INN/text())[1]', 'nvarchar(255)') AS INNProducer
INTO #AllDataTable
FROM @xdat.nodes('declare namespace rap="http://fsrar.ru/WEGAIS/ReplyAP";
				  declare namespace ns="http://fsrar.ru/WEGAIS/WB_DOC_SINGLE_01";
				  /ns:Documents/ns:Document/ns:ReplyAP/rap:Products/rap:Product') AS Product(ref)
CROSS APPLY Product.ref.nodes('declare namespace  pref="http://fsrar.ru/WEGAIS/ProductRef";  pref:Producer') AS Producer(ref)

 SELECT * FROM #AllDataTable

-- формируем докумет
DECLARE @xdat XML = (
		SELECT TOP 5 GUID
			,StatusDoc
		FROM V_EGAIS.dbo.ActChargeOnHeader
		FOR XML path('List')
			,ROOT('Document')
			,ELEMENTS
			,type
		)

-- ниже пример того что получилось
--  <Document>
--  <List>
--    <GUID>7F705894-441F-4B59-B6C4-0003579AE9BA</GUID>
--    <LDM>2016-02-04T15:58:01.650</LDM>
--  </List>
--  <List>
--    <GUID>D6EADAEB-AD45-4EB9-962D-0003B82CB431</GUID>
--    <LDM>2016-02-04T15:59:51.723</LDM>
--  </List>
--  <List>
--    <GUID>84BA1332-7C1F-4BAB-9923-0003D4B2CD7A</GUID>
--    <LDM>2016-02-04T15:58:57.443</LDM>
--  </List>
--  <List>
--    <GUID>A56EF787-3549-43DD-99E8-00043ED70C11</GUID>
--    <LDM>2016-02-04T15:56:59.223</LDM>
--  </List>
--  <List>
--    <GUID>16939553-7927-4221-B260-00044EBC225F</GUID>
--    <LDM>2016-02-04T15:57:29.297</LDM>
--  </List>
--</Document>
-- теперь производим выборку
SELECT Document.ref.value('(GUID/text())[1]', 'nvarchar(255)') AS [GUID]
	     ,Document.ref.value('(StatusDoc/text())[1]', 'int') AS StatusDoc
FROM @xdat.nodes('/Document/List') AS Document(ref) -- тут мы показываем с какого раздела можно начинать брать данные.

void ToExcel(DataTable dt1)
        {
            try
            {
                Excel.Application EoXL;
                Excel._Workbook EoWB;
                Excel._Worksheet EoSheet;
                Excel.Range excelRange;
                EoXL = new Excel.Application();
                EoXL.Visible = false;
                EoWB = EoXL.Workbooks.Add(Type.Missing);

                int TabRows = 1;

                EoSheet = (Excel.Worksheet)EoWB.Worksheets.get_Item(1);//ссылка на лист excel
                EoSheet.Name = "Отчет о кодах возвратных накладных";
                EoSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;

                int row = dt1.Rows.Count;
                int col = dt1.Columns.Count;


                EoSheet.Cells[1, 1] = "Префиксы возвратных накладных и счетов фактур подразделений";
                EoSheet.Cells[1, 1].VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                EoSheet.Cells[1, 1].Font.Bold = true;
                EoSheet.Cells[1, 1].Font.Size = 16;

              

                // передаем первую таблицу, заполняем ее в памяти и передаем целиком
                object[,] dataExport = new object[row, col];

                for (int i = 0; i < row; i++)
                {
                    for (int j = 0; j < col; j++)
                    {
                        dataExport[i, j] = dt1.Rows[i][j];
                    }

                }


                excelRange = EoSheet.Range[EoSheet.Cells[2 + TabRows, 1], EoSheet.Cells[row + 1 + TabRows, col]];
                excelRange.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, dataExport);
                excelRange.Borders.ColorIndex = 0;

//этот кусок в качестве примера указания типа данных в ячейках
                // excelRange = EoSheet.Range[EoSheet.Cells[2 + TabRows, 8], EoSheet.Cells[row + 1 + TabRows, 10]];
                // excelRange.NumberFormat = "#,##0.00";

                // формируем заголовок
                ArrayList displayColumnExsel = new ArrayList();


                foreach (DataColumn c in dt1.Columns)
                {

                    displayColumnExsel.Add(c.ColumnName);
                }

                object[] dataExportH = new object[col];
                for (int i = 0; i < col; i++)
                    dataExportH[i] = displayColumnExsel[i];

                excelRange = EoSheet.Range[EoSheet.Cells[1 + TabRows, 1], EoSheet.Cells[1 + TabRows, col]];
                excelRange.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, dataExportH);
                excelRange.Font.Bold = true;
                excelRange.WrapText = true;
                excelRange.Borders.ColorIndex = 0;
                excelRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                excelRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                      

               EoXL.Visible = true;
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message, "Ошибка метода переноса таблиц", MessageBoxButton.OK, MessageBoxImage.Error);
            }


        }