Парсим XML в MS SQL

Один из вариантов разбора XML в Microsoft SQL. Вытащим из сообщения детальные записи (два списка), которые потом объединим по ключу и добавим поля из заголовка.

with q as (select 1 as submit_id, cast(
'<xsubmission>
  <global>
    <location>location1</location>
  </global>
  <global>
    <location_property>some text</location_property>
  </global>
  <repeat_labor>
    <labor_technician>technician1</labor_technician>
    <labor_total>10.0</labor_total>
  </repeat_labor>
  <repeat_labor>
    <labor_technician>technician2</labor_technician>
    <labor_total>5.0</labor_total>
  </repeat_labor>
  <repeat_expense>
    <expense_technician>technician1</expense_technician>
    <expense_total>23500.45</expense_total>
  </repeat_expense>
  <repeat_expense>
    <expense_technician>technician3</expense_technician>
    <expense_total>40670.00</expense_total>
  </repeat_expense>
</xsubmission>'
 as XML) submit_xml
)


, ql as --subquery for labors
(select q.submit_id
  , repeat.value('labor_technician[1]', 'varchar(250)') labor_technician
  , repeat.value('labor_total[1]', 'varchar(250)') labor_total
from q CROSS APPLY submit_xml.nodes('/xsubmission/repeat_labor') r(repeat)
)


, qe as --sub
(select q.submit_id
  , repeat.value('expense_technician[1]', 'varchar(250)') expense_technician
  , repeat.value('expense_total[1]', 'varchar(250)') expense_total
from q CROSS APPLY submit_xml.nodes('/xsubmission/repeat_expense') r(repeat)
)


, qle as -- join labors and expenses
(select isnull(ql.submit_id, qe.submit_id) submit_id
     , isnull(ql.labor_technician, qe.expense_technician) technician
     , ql.labor_total, qe.expense_total
from ql full join qe on (ql.submit_id = qe.submit_id and ql.labor_technician = qe.expense_technician)
)




select q.submit_id
  , q.submit_xml.value('(/xsubmission/global/location)[1]', 'varchar(250)') location
  , q.submit_xml.value('(/xsubmission/global/location_property)[1]', 'varchar(250)') location_property
  , qle.technician
  , qle.labor_total
  , qle.expense_total
from q left join qle on (q.submit_id = qle.submit_id)
;

Результат:

Поиск подстроки по всем таблицам в MS SQL

Иногда нужно узнать в какой таблице может храниться некоторое значение. Можно организовать поиск по всем таблицам таким способом:

declare @table_name varchar(250);

declare @field_name varchar(250);

declare @sql_search nvarchar(4000);

declare @search_string varchar(250) = '%search_string%';  

--use % to search for substring

declare user_tables cursor fast_forward for

      select t.name table_name, c.name field_name

      from sys.tables t, sys.columns c, sys.types tp, sysobjects ao

      where ao.id = t.object_id and c.object_id = t.object_id

      and tp.system_type_id = c.system_type_id

      and ao.xtype = 'U' --user tables

      and tp.name in ('char', 'text', 'varchar', 'nchar', 'nvarchar', 'ntext')

      order by t.name;

open user_tables;

fetch next from user_tables into @table_name, @field_name;

while @@FETCH_STATUS = 0

begin

  declare @result int;

  set @sql_search = 'declare search_cursor cursor fast_forward for

    select count(*) from [' + @table_name + ']

      where [' + @field_name + '] like ''' + @search_string + '''';

  exec sp_executesql @sql_search;

  open search_cursor;

  fetch next from search_cursor into @result;

  if @@FETCH_STATUS = 0 and @result > 0

  begin

      print(@table_name + '.' + @field_name + ':' + convert(varchar, @result));

  end

  close search_cursor;

  deallocate search_cursor;

 

  fetch next from user_tables into @table_name, @field_name;

end

close user_tables;

deallocate user_tables;