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

Откуда:
Сообщений: 662
Итак.
Предлагаю пару SP_шек для простого управления XML_ем любой сруктуры.
Существенным ограничением, imho, является то, что можно указывать индексы в пути только int_ами. Это связано с тем, что я не нашел способа получить реальный номер узла, когда индекс указан например так [@SomeAttribute=10].

Вспомогательная ф-я
-- =============================================
-- Author:	  Mike909
-- =============================================
ALTER FUNCTION [dbo].[ParamList] 
(
  @ParamList  nvarchar(max),
  @Splitter   nvarchar(50) = ','
)
RETURNS 
  @Res TABLE ( ID int IDENTITY(1,1), [name] nvarchar(512) )
AS
BEGIN
  DECLARE @wrd nvarchar(512),
          @emp bit,
          @idx int;

  set @emp = case Right(@ParamList, 1) when @Splitter then 1 else 0 end;

  while @ParamList is not NULL
  begin
    set @idx = CHARINDEX(@Splitter, @ParamList);
    
    if @idx = 0 begin
      insert into @Res Values( LTrim(RTrim(@ParamList)) )
      set @ParamList = NULL
    end else begin
      insert into @Res Values( LTrim(RTrim(Left(@ParamList, @idx - 1))) )
      set @ParamList = Right(@ParamList, Len(@ParamList)  - Len(@Splitter) - @idx + 1);
    end;
  end;
  
  if @emp = 1
    insert into @Res Values( '' )
	
  RETURN 
END

GO


Ключевая SP_шка, на которой все и основанно

-- =============================================
-- Author:		  mike909
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
ALTER PROCEDURE [dbo].[SetXMLInnerText]
	@xml              xml,
  @Path             nvarchar(1000) output,
  @ResultXML        xml output,
  @Value            nvarchar(max) = NULL,
  @CreateIfNotExist bit = NULL
/*
  @xml - исходный XML, может быть NULL_ом

  @Path format: 
    1) /NODE/NODE/NODE
    2) /NODE[1]/NODE[-1]/NODE[0]
    3) ((/NODE[1])/NODE[2])/NODE[0] = /NODE[1]/NODE[2]/NODE[0]
  Где "[индекс]" - ТОЛЬКО "INT" !!!
  [0] вставка нового узла в самое начало, 
  [1]..[Count(@xml.nodes())] замена узла по заданному индексу - если индекс указан для последнего узла пути, и
  позиционирование на конкретный узел - если индекс указан для промежуточного узла пути.
  Если индекс не указан, то при наличии узла(ов) !!! ВСЕГДА !!! изменяем/используем/удаляем последний узел
  Если нужно добавить еще один узел в конец, то индекс узла должен быть либо "Count(@xml.nodes()) + 1" либо = "-1"
  Если индекс больше чем Count(@xml.nodes()) + 1, то будут добавлены пустые узлы с тем-же именем
  В случае успеха операции изменения/добавления/удаления в @Path запишется реальный путь к 
  измененому/добавленному/удаленному узлу.

  @Value : Если задан произвольный текст, то будет добавлен узел с этим текстом. NULL - будет удален указанный узел

  @CreateIfNotExist : 
    "1"     - Недостающие узлы будут добавлены. 
    "0"     - Если какой либо узел отсутствует, то будет выдана ошибка
    "NULL"  - @CreateIfNotExist = CASE WHEN @Value is NULL THEN 0 ELSE 1 END
*/
AS
BEGIN
	SET NOCOUNT ON;
  declare @Res      int,
          @Cnt      int,
          @ID       int,
          @Idx      int,
          @LastID   int,
          @isExist  bit,
          @name     nvarchar(512),
          @Val      nvarchar(max),
          @stmt     nvarchar(max)

  declare @PE table(ID int, [name] nvarchar(512))

  set @Res = 0;
  set @Path = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Path,'(',''),')',''),char(13),''),char(10),''), ' ','');

  if Right(@Path, 1) = '/'
    set @Path = Left(@Path, Len(@Path)-1 );

  if Left(@Path, 1) = '/'
    set @Path = Right(@Path, Len(@Path)-1 );

  if Len(@Path) = 0
    set @Path = NULL;

  if @Path is NULL
    SELECT @Path = @xml.value('local-name(/*[last()])', 'varchar(1000)')
  
  insert into @PE
  SELECT [ID], [name]
  FROM dbo.ParamList(@Path, '/')

  SELECT @LastID = Max(ID)
  FROM @PE

  -- Защита от неоднозначности пути
  SELECT @Res = -1
  FROM @PE
  WHERE ([name] in ('..','.','')) or
        ((CHARINDEX('[', [name]) != 0) and 
        (ISNUMERIC( PARSENAME(RIGHT([name], Len([name]) - CHARINDEX('[',[name])+1),1) ) = 0))

  if @Res != 0
  begin
    print '@Path "' + @Path + '" not supported'
    RETURN @Res
  end;

  set @Path = NULL;

  if @Value is not NULL
  begin
    set @ResultXML = (select val from (select @Value as val) as R for xml auto, elements)
    set @Value = cast(@ResultXML.query( '/R/val/text()') as nvarchar(max))
  end;

  set @CreateIfNotExist = CASE WHEN @Value is NULL THEN 0
                               WHEN @CreateIfNotExist is NULL THEN 1
                               ELSE @CreateIfNotExist 
                          END;

  set @isExist = case when @xml is null then 0 else 1 end;
  if @isExist != 0
  begin -- check exists path elements and create if needed
    set @Path = NULL
    set @ResultXML = @xml;

    DECLARE c CURSOR LOCAL FAST_FORWARD
    FOR SELECT [ID], [name]
        FROM @PE
        ORDER BY ID ASC

    OPEN c
    FETCH NEXT FROM c into @Idx, @name
    WHILE (@@FETCH_STATUS = 0) 
    begin
      set @Cnt = CHARINDEX('[', @name)

      set @isExist = case when (@Cnt != 0) then 1 else 0 end;
      if @isExist = 1
      begin
        set @ID = CAST( PARSENAME( RIGHT(@name, Len(@name) - @Cnt + 1), 1 ) as int );
        set @name = Left(@name, @Cnt - 1);

        set @isExist = case when (@ID <= 0) then 0 else 1 end;
        if @isExist = 1
          set @stmt = N'select @isExist = @xml.exist(''' + ISNULL(@Path + '/','/') + @name +
                      N'[' + cast(@ID as nvarchar) + N']'')' + char(13) + char(10) +
                      N'select @Cnt = Count(*)' + char(13) + char(10) +
                      N'from @xml.nodes(''' + ISNULL(@Path + '/', '/') + @name + ''') T(c)'
        else if @ID < 0 
          set @stmt = N'select @Cnt = Count(*)' + char(13) + char(10) +
                      N'from @xml.nodes(''' + ISNULL(@Path + '/', '/') + @name + ''') T(c)' + char(13) + char(10) +
                      N'set @ID = @Cnt + 1'
        else
          set @stmt = N'select @Cnt = Count(*)' + char(13) + char(10) +
                      N'from @xml.nodes(''' + ISNULL(@Path + '/', '/') + @name + ''') T(c)'
      end
      else
        set @stmt = N'select @Cnt = Count(*)' + char(13) + char(10) +
                    N'from @xml.nodes(''' + ISNULL(@Path + '/', '/') + @name + ''') T(c)' + char(13) + char(10) +
                    N'set @isExist = case when 0 < @Cnt then 1 else 0 end' + char(13) + char(10) +
                    N'set @ID = @Cnt';

      exec sp_executesql @stmt=@stmt, @Params=N'@xml XML, @isExist bit output, @ID int output, @Cnt int output', 
                         @xml=@ResultXML, @isExist=@isExist output, @ID=@ID output, @Cnt=@Cnt output
      
      if @isExist = 0
      begin
        if @CreateIfNotExist = 1
        begin
          if @Path is not NULL
          begin
            if @Idx = @LastID
            begin
              if @Value is not NULL
                set @Val = replace(@Value, '''', '''''' )
              else
                break; -- Nothing to do 
            end
            else
              set @Val = char(0x87) + char(0x87) + char(0x87)

            if @ID = 0 
            begin -- добавление нового узла в начало
              if @Cnt = 0
                set @stmt = N'set @xml.modify(''insert <' + @name + N'>' + @Val + 
                            N'</' + @name + N'> into (' + @Path + N')'')'
              else
                set @stmt = N'set @xml.modify(''insert <' + @name + N'>' + @Val + N'</' + @name + 
                            N'> before (' + @Path + N'/' + @name + N')[1]'')'
              set @ID = 1;
            end
            else if @Cnt < @ID
            begin
              set @Cnt = @ID - @Cnt;
              if 1 < @Cnt
              begin -- вставка недостающих узлов
                set @stmt = N'set @xml.modify(''insert (<' + @name + N'/>'
                set @Cnt = @Cnt-1
                WHILE 1 < @Cnt
                begin 
                  set @stmt = @stmt + ',<' + @name + N'/>'
                  set @Cnt = @Cnt-1
                end;

                set @stmt = @stmt + ',<' + @name + N'>' + @Val + N'</' + @name + 
                            N'>) as last into (' + @Path + N')[1]'')'
              end
              else -- добавление нового узла в конец
                set @stmt = N'set @xml.modify(''insert <' + @name + N'>' + @Val + N'</' + @name + 
                            N'> as last into (' + @Path + N')[1]'')'
            end;

            exec sp_executesql @stmt=@stmt, @params=N'@xml XML OUTPUT', @xml=@xml output

            if @Idx < @LastID
            begin
              set @stmt = NULL
              SELECT @stmt = ISNULL(@stmt + '/', '/') + [name] 
              FROM @PE
              WHERE @IDX < [ID]

              exec [dbo].[SetXMLInnerText] NULL, @stmt output, @ResultXML output, @Value, 1
              
              set @ResultXML = CAST( REPLACE( CAST(@xml as nvarchar(max)), @Val,  
                                              CAST(@ResultXML as nvarchar(max))) as XML );
              set @Path = @Path + N'/' + @name + N'[' + cast(@ID as nvarchar) + N']' + @stmt;
            end
            else begin
              set @ResultXML = @xml;
              set @Path = @Path + N'/' + @name + N'[' + cast(@ID as nvarchar) + N']'
            end;
          end
          else begin -- добавление нового XML_я
            if (@Cnt != 0) and (@ID != 0)
            begin
              if @ID < 0
              begin
                set @ID = @Cnt + 1
                set @Path = '/' + @name + N'[1]';
              end
              else
                set @Path = '/' + @name + N'[' + CAST((@ID - @Cnt) as nvarchar) + N']';

              SELECT @Path = @Path + '/' + [name]
              FROM @PE
              WHERE @Idx < [ID]
              ORDER BY [ID] ASC
            end
            else 
              SELECT @Path = ISNULL(@Path + '/', '/') + [name]
              FROM @PE
              ORDER BY [ID] ASC

            exec [dbo].[SetXMLInnerText] NULL, @Path output, @ResultXML output, @Value, 1

            if @ID != 0 begin
              set @ResultXML = CAST( (CAST(@xml as nvarchar(max)) + CAST(@ResultXML as nvarchar(max))) as XML );
              if (@Cnt != 0) and (@ID != 0)
              begin
                set @name = @name + N'[' + cast(@ID as nvarchar) + N']'
                set @idx = CHARINDEX(']', @Path)
                set @Path = @name + Right(@Path, Len(@Path) - @idx )
              end;
            end
            else
              set @ResultXML = CAST( (CAST(@ResultXML as nvarchar(max)) + CAST(@xml as nvarchar(max))) as XML )
          end;
        end
        else begin
          print 'Path "' + ISNULL(@Path + '/','/') + @name + '[' + cast(@ID as nvarchar) + ']" is not exist'
          set @Res = -1;
        end;

        break;
      end
      else begin -- @isExist = 1
        if @Idx = @LastID
        begin
          if @Value is not NULL
            set @Value = replace(@Value, '''', '''''' );

          if @ID between 1 and @Cnt
          begin 
            set @stmt = N'set @xml.modify(''delete ('+ ISNULL(@Path + N'/', '/') + @name + 
                        N'['+ cast(@ID as nvarchar)+ N'])'')' 

            if @Value is not NULL
            begin
              if @ID = 1
                set @stmt = @stmt + char(13) + char(10) + N'set @xml.modify(''insert <' + @name + N'>' + @Value + 
                            N'</' + @name + N'> as first into (' + ISNULL(@Path, '/') + N')'')'
              else if @ID = @Cnt
                set @stmt = @stmt + char(13) + char(10) + N'set @xml.modify(''insert <' + @name + N'>' + @Value + 
                            N'</' + @name + N'> as last into (' + ISNULL(@Path, '/') + N')'')'

              else
                set @stmt = @stmt + char(13) + char(10) + N'set @xml.modify(''insert <' + @name + N'>' + @Value + 
                            N'</' + @name + N'> before (' + ISNULL(@Path + N'/', '/') + @name + N')[' +
                            cast(@ID as nvarchar) + N']'')'
            end
          end
          else begin
            set @stmt = N'set @xml.modify(''delete ('+ ISNULL(@Path + N'/', '/') + @name + 
                        N'['+ cast(@Cnt as nvarchar)+ N'])'')' 

            if @Value is not NULL
              set @stmt = @stmt + char(13) + char(10) + N'set @xml.modify(''insert <' + @name + N'>' + @Value + 
                          N'</' + @name + N'> as last into (' + ISNULL(@Path + N'/', '/') + N')'')'
          end;

          exec sp_executesql @stmt=@stmt, @params=N'@xml XML OUTPUT', @xml=@ResultXML output

          if (@Idx = 1) 
            if (Len(cast(@ResultXML as nvarchar(max))) = 0)
              set @ResultXML = NULL;
        end
      end;

      set @Path = ISNULL(@Path + '/', '/' ) + @name + N'[' + cast(@ID as nvarchar) + N']'
      FETCH NEXT FROM c into @Idx, @name
    end;
  end
  else if (@Value is not NULL) and (@CreateIfNotExist = 1) begin
    set @stmt = ''

    DECLARE c CURSOR LOCAL FAST_FORWARD
    FOR SELECT [name]
        FROM @PE
        ORDER BY ID ASC

    OPEN c
    FETCH NEXT FROM c INTO @name
    WHILE @@FETCH_STATUS = 0
    begin
      set @Cnt = CHARINDEX('[', @name)
      if @Cnt != 0
      begin
        set @ID = CAST( PARSENAME( RIGHT(@name, Len(@name) - @Cnt + 1), 1 ) as int );
        set @name = Left(@name, @Cnt - 1);

        if 0 < @ID
        begin
          set @Cnt = @ID-1
          WHILE 0 < @Cnt
          begin
            set @stmt = @stmt + N'<' + @name + N'/>'
            set @Cnt = @Cnt - 1;
          end;
        end
        else
          set @ID = 1
      end
      else
        set @ID = 1;

      set @stmt = @stmt + '<' + @name + '>' 
      set @Path = ISNULL( @Path + '/', '/' ) + @name + N'[' + cast(@ID as nvarchar) + N']'

      FETCH NEXT FROM c INTO @name
    end;

    CLOSE c
    DEALLOCATE c

    set @stmt = @stmt + @Value

    DECLARE c CURSOR LOCAL FAST_FORWARD
    FOR SELECT [name]
        FROM @PE
        ORDER BY [ID] DESC

    OPEN c
    FETCH NEXT FROM c INTO @name
    WHILE @@FETCH_STATUS = 0
    begin
      set @ID = CHARINDEX('[', @name)
      if @ID != 0
        set @name = Left(@name, @ID-1);

      set @stmt = @stmt + N'</' + @name + N'>'

      FETCH NEXT FROM c INTO @name
    end;

    CLOSE c
    DEALLOCATE c

    set @ResultXML = cast(@stmt as XML)
  end
  else
    set @Res = -1; -- Path not found

  if (@Res != 0)
  begin
    set @ResultXML = NULL;
    set @Path = NULl;
  end;

  RETURN @Res
END

И SP_шка - ради которой все и затевалось

-- =============================================
-- Author:		  Mike909
-- Description:	Вставка одного произвольного XML_я в другой XML
-- =============================================
ALTER PROCEDURE [dbo].[SetXML2XML] 
  @SrcXML XML,                         -- Что вставляем 
  @DstXML XML,                         -- Куда вставляем 
  @ResultXML XML OUTPUT,               -- as is
  @Path nvarchar(1000) = NULL output   -- as is
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @Res int,
              @Val nvarchar(3)

  if (@SrcXML is NULL) or (@DstXML is NULL)
    RETURN -1

  set @Val = char(0x87) + char(0x87) + char(0x87)

  if @Path is NULL
    set @Path = @DstXML.value('local-name(/*[last()])', 'varchar(1000)') + N'[-1]'

  exec @Res = dbo.SetXMLInnerText @DstXML, @Path output, @ResultXML output, @Val

  if @Res = 0
    set @ResultXML = CAST( REPLACE( CAST(@ResultXML as nvarchar(max)), @Val, CAST(@SrcXML as nvarchar(max))) as XML );

  RETURN @Res
END
23 июн 08, 11:13    [5833064]     Ответить | Цитировать Сообщить модератору
 Re: Чайникам XML_я от кофейника  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541
И что делают Ваши процедуры?
Не проще было процедуру в три строчки на CLR написать?
23 июн 08, 11:40    [5833227]     Ответить | Цитировать Сообщить модератору
 Re: Чайникам XML_я от кофейника  [new]
mike909
Member

Откуда:
Сообщений: 662
Roman S. Golubin
И что делают Ваши процедуры?
Не проще было процедуру в три строчки на CLR написать?


1) С помощью этих SP_шек можно очень просто сформировать XML любой структуры
Например:

declare @xml XML, @ResXML XML, @Path nvarchar(512), @Value nvarchar(max)

set @ResXML = N'<Root/>'
set @xml = NULL
set @Path = '/SQL[-1]'

set @Value = 'Select 1'
exec dbo.SetXMLInnerText @xml, @Path, @xml output, @Value
select @xml

set @Value = 'Select 2'
exec dbo.SetXMLInnerText @xml, @Path, @xml output, @Value
select @xml

set @Value = 'Select 3'
exec dbo.SetXMLInnerText @xml, @Path, @xml output, @Value
select @xml

set @Path = '/Root/Step'
exec dbo.SetXML2XML @xml, @ResXML, @ResXML output, @Path output

select @xml, @ResXML, @Path

2) Не проще, да и не три строчки получаться, а раза в два поменьше чем на TSQL_е если через DomDocument сделать. И не спортивно как-то ...
23 июн 08, 12:02    [5833387]     Ответить | Цитировать Сообщить модератору
 Re: Чайникам XML_я от кофейника  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
О.о.о.очередной монстр.
Roman S. Golubin
И что делают Ваши процедуры?
Не проще было процедуру в три строчки на CLR написать?
Почему, проще уж 2 строчки на TSQL. :)
Любят процедурномыслящие выпендриваться ...
А чё хороший пример ... как делать не надо.
23 июн 08, 15:09    [5834645]     Ответить | Цитировать Сообщить модератору
 Re: Чайникам XML_я от кофейника  [new]
mike909
Member

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

О.о.о.очередной монстр.


Когда у меня нарисовалась задачка передачи контекста выполнения на основе XML_я через Service Broker, я перерыл кучу сообщений в этом форуме и не нашел ничего похожего.
На вопрос "КАК ВСТАВИТЬ ПРОИЗВОЛЬНЫЙ XML В ДРУГОЙ ПРОИЗВОЛЬНЫЙ XML" по известному пути
ответа то-же не получил - был послан в направлении CLR или динамики. Куда и пошел...
И потом, если некий код (те самые пара строк) повторяется во множестве с различными вариациями
то imho имеет смысл оформить их в отделные SP_шки или ф-ии.

Mnior

Любят процедурномыслящие выпендриваться ...
А чё хороший пример ... как делать не надо.


А как надо делать - примерчики/ссылочки плз. или хотябы эти самые "пару строк" привели бы ...
Или Вы поклоник многократного копирования кусков кода ?
23 июн 08, 17:29    [5835803]     Ответить | Цитировать Сообщить модератору
 Re: Чайникам XML_я от кофейника  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
mike909
На вопрос "КАК ВСТАВИТЬ ПРОИЗВОЛЬНЫЙ XML В ДРУГОЙ ПРОИЗВОЛЬНЫЙ XML" по известному пути ответа то-же не получил
Чёта я не вижу темы. Ссылку бы указал.
CLR конечно изврат, но скорее задачу вы так вывернули. Скока не работал, особо приблем "таких" не встречал. Сделайте 3 шага назад (только не удартесь головой) и посмотрите на всю систему вцелом и в деталях - уверен, что где-то передёрнули ... и не раз.
24 июн 08, 01:14    [5837448]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Чайникам XML_я от кофейника  [new]
...аноним...
Guest
У меня была подобная необходимость (вставка одно хмл в другой), я поступил так.
DECLARE @xml as XML
DECLARE @msg as XML

SET @xml = '<data><value x="1"/><value x="2"/></data>'

SET @msg = (
  SELECT
    @xml.query('/*/*') AS [message],
    '<>""\ssdsg' AS text,
    1 AS ID
  FOR XML RAW ('source'), ELEMENTS, TYPE
)

SELECT @msg
@xml - хмл сформированный где-то там и который надо вставить.
@msg - хмл в который надо вставить.
Приведенный автором вариант конечно более универсален, но по опыту такие операции с хмл на стороне MS SQL крайне медленные.
7 сен 11, 12:58    [11240110]     Ответить | Цитировать Сообщить модератору
 Re: Чайникам XML_я от кофейника  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
DECLARE	 @Data	XML = '<data><value x="1"/><value x="2"/></data>'
	,@Sub	XML = '<value x="3"/>'
SET	 @Data.modify('insert sql:variable("@Sub") into /data[1]')
SELECT	 @Data	-- <data><value x="1" /><value x="2" /><value x="3" /></data>
Повторюсь:
Mnior
Сделайте 3 шага назад и посмотрите на всю систему вцелом и в деталях - уверен, что где-то передёрнули ... и не раз.
Не нужно создвать данные путём модификаций (многочисленных).
В маштабе всей системы должно быть одно формированние одних данных на основе других данных, т.е. один запрос создающий один набор.
(как у ...аноним...-а, без переменных)

KO
7 сен 11, 16:18    [11242214]     Ответить | Цитировать Сообщить модератору
 Re: Чайникам XML_я от кофейника  [new]
mike909
Member

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

Ээээх, каким кофейником я был...

P.S. А SP_шки то, до сих пор кое где у меня работают, хотя использую их все реже и реже....
7 сен 11, 18:50    [11243572]     Ответить | Цитировать Сообщить модератору
 Re: Чайникам XML_я от кофейника  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3422
Mnior
DECLARE	 @Data	XML = '<data><value x="1"/><value x="2"/></data>'
	,@Sub	XML = '<value x="3"/>'
SET	 @Data.modify('insert sql:variable("@Sub") into /data[1]')
SELECT	 @Data	-- <data><value x="1" /><value x="2" /><value x="3" /></data>
...
KO

Мой хрустальный шар подсказывает, что у вас 2008. В 2005 такой modify() не работает.
7 сен 11, 19:25    [11243728]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить