Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 сложный for xml  [new]
AnaceH
Member

Откуда:
Сообщений: 109
Здравствуйте. Пытаюсь сформировать excel-xml.
Запрос
+

;WITH XMLNAMESPACES('urn:schemas-microsoft-com:office:spreadsheet' as ss, 
 default 'urn:schemas-microsoft-com:office:spreadsheet',
 'urn:schemas-microsoft-com:office:office' as o,
  'urn:schemas-microsoft-com:office:excel' as x,
 'http://www.w3.org/TR/REC-html40' as html)

 select 'Лист' as "@ss:Name",
        (
         select 
                (select 'String' as "Data/@ss:Type", a.Data1 as Data for xml PATH('Cell') ,TYPE ) Row, 
			    (select 'String' as "Data/@ss:Type", a.Data2 as Data for xml PATH('Cell') ,TYPE ) Row
           from (
                 select 'a' as Data1, 'b' as Data2
                 union all 
                 select 'C' as Data1, 'D' as Data2
		        ) a
		    for xml PATH (''),root ('Table'), type 
		 ) for xml  PATH ('Worksheet'),root ('Workbook'), type 

возвращает валидный XML
+
<Workbook xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
  <Worksheet ss:Name="Лист">
    <Table xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
      <Row>
        <Cell xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
          <Data ss:Type="String">a</Data>
        </Cell>
        <Cell xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
          <Data ss:Type="String">b</Data>
        </Cell>
      </Row>
      <Row>
        <Cell xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
          <Data ss:Type="String">C</Data>
        </Cell>
        <Cell xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
          <Data ss:Type="String">D</Data>
        </Cell>
      </Row>
    </Table>
  </Worksheet>
</Workbook>

, но вот незадача, перечисление нэймспэйсов в каждом <Cell >
Очевидно, это происходит из-за того, что Cell формируется в подзапросе.
Может есть возможность как-то решить задачу без подзапроса? Гуглил, но ответ так и не нашел, все упирается в то, что тэги Cell одинаковые.
Подскажите пожалуйста, как сформировать необходимый xml без нэймспэйсов в каждой ноде?
14 сен 12, 13:35    [13164531]     Ответить | Цитировать Сообщить модератору
 Re: сложный for xml  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
7958157 или ниже
14 сен 12, 15:19    [13165619]     Ответить | Цитировать Сообщить модератору
 Re: сложный for xml  [new]
AnaceH
Member

Откуда:
Сообщений: 109
Mnior,

Спасибо, помогло.
14 сен 12, 18:24    [13167003]     Ответить | Цитировать Сообщить модератору
 Re: сложный for xml  [new]
AnaceH
Member

Откуда:
Сообщений: 109
Mnior, подскажите пожалуйста, а есть ли способ сфомировать так же круто xml на нескольких листах? Не пойму как слить одним разом сразу много выборок. Пробовал modify insert делать - без результата, хотя если без нэймспэйсов - работает.
23 окт 12, 21:00    [13365328]     Ответить | Цитировать Сообщить модератору
 Re: сложный for xml  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Worksheet - это и есть лист. Нужно запрос переписать для списка объектов.
Было бы время выложил бы готовый скрипт - автоматизация отчётов, с шапками, футерами и настройками листов, паковка, отсылка...
23 окт 12, 23:12    [13365668]     Ответить | Цитировать Сообщить модератору
 Re: сложный for xml  [new]
AnaceH
Member

Откуда:
Сообщений: 109
Mnior,

вот что получилось.
+
create function [dbo].[fnExcelQuery](@Objects nVarChar(max)) 
returns nvarchar(max) 
as 
begin
declare @result nvarchar(max)
;with cte as (
select (
 select case
        when C.column_id = 1 then ' ' 
        else '
	,'  end + 'NULL AS [text()],' + 
        case 
        when C.system_type_id IN (48,52,56,59,60,62,106,108,122,127) then '''Number''  '
        when C.system_type_id IN (58,61) then '''DateTime'''
        when C.system_type_id = 104	 then '''Boolean'' '
        else '''String''  '
        end + 'AS [Cell/Data/@s:Type],' + QuoteName(C.name) + Space(Max(Len(C.name))OVER() - Len(C.name)) + ' AS [Cell/Data/text()]' AS [Row/text()], 
        IsNull('<Column s:StyleID="' + 
        case
        when C.system_type_id IN (48,52,56,59,62,106,108,127) then 'Number'
        when C.system_type_id IN (60,122) then 'Currency'
        when C.system_type_id IN (58,61) then 'DateTime'
        when C.system_type_id = 104 then 'Boolean'
        end + '" />','<Column />') AS [Style/text()],
        '<Cell><Data s:Type="String">' + C.name + '</Data></Cell>' AS [Name/text()],
        case 
        when C.column_id = 1 then replace(N.X.value('text()[1]','VarChar(max)'), ';', '') 
        else '' 
        end as [Sheet],
        case 
        when C.column_id = 1 
        then '(' + stuff((select ', ' + name as 'text()' from   sys.parameters p where p.[object_id] = Object_ID(replace(N.X.value('text()[1]','VarChar(max)'), ';', '')) ORDER BY p.parameter_id for xml path('')), 1, 2, '') + ')' 
        else '' 
        end as [Params]

   from sys.columns C
  where C.[object_id] = Object_ID(replace(N.X.value('text()[1]','VarChar(max)'), ';', ''))
  order by C.column_id
    for xml Path(''),Type).query('(<Row>{/Row/text()}</Row>,<Style>{/Style/text()}</Style>,<Name>{/Name/text()}</Name>,<Sheet>{/Sheet/text()}</Sheet>,<Params>{/Params/text()}</Params>)').value('fn:concat("
select IsNull((
 select ",(/Row/text())[1],"
   from ",(/Sheet/text())[1]," ",(/Params/text())[1],"
    for xml Path(''Row''),Root(''Table''),Type),'''').query(''
 <Worksheet s:Name=""",(/Sheet/text())[1],""" xmlns=""urn:schemas-microsoft-com:office:spreadsheet"" xmlns:s=""urn:schemas-microsoft-com:office:spreadsheet"">
  <Table>
   ",(/Style/text())[1],"
   <Row>",(/Name/text())[1],"</Row>
   {/Table/Row}
  </Table>
 </Worksheet>'')")','NVarChar(max)') as data
  from (select Convert(xml,'<s>' + replace(@Objects,';','</s><s>') + '</s>'))S(X)
 cross apply S.X.nodes('/s')N(X)
       )
 
 select @result = ';WITH XMLNAMESPACES(Default ''urn:schemas-microsoft-com:office:spreadsheet'',''urn:schemas-microsoft-com:office:spreadsheet'' AS s)
 select N''<?xml version="1.0" encoding="UTF-16"?><?mso-application progid="Excel.Sheet"?>'' + Convert(NVarChar(max), (select * from( '+stuff((select '

 union all
' + data + ' Row' as 'text()' from cte t2 for xml path(''), type).value('.','varchar(max)'), 1, 18, '') + '
) asd for xml Path(''''),Root(''Table''),Type ).query(''
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:s="urn:schemas-microsoft-com:office:spreadsheet">
 <Styles>
  <Style s:ID="Boolean"><NumberFormat s:Format="True/False" /></Style>
  <Style s:ID="Currency"><NumberFormat s:Format="Currency" /></Style>
  <Style s:ID="Number"><NumberFormat s:Format="General Number" /></Style>
  <Style s:ID="DateTime"><NumberFormat s:Format="General Date" /></Style>
 </Styles>
 {/Table/Row/Worksheet}
</Workbook>''))
' 
return @result
end

Есть стойкое ощущение, что можно было сделать лучше. Так что буду ждать замечаний.
24 окт 12, 17:53    [13370383]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить