Насколько я знаю, в MS SQL Server Management Studio нет инструмента для загрузки XML в таблицу. Существует вариант, который включает комбинацию OPENROWSET и обработки XML, но требует, чтобы файлы находились в файловой системе сервера.
Мне нужно было загрузить в таблицу серию файлов журналов, созданных веб-приложением Java, но у меня не было доступа для их загрузки на сервер, у меня были журналы на моем локальном компьютере. Мне удалось загрузить данные в двухэтапном процессе, который не слишком громоздкий, но определенно слишком медленный для постоянного решения.
Я создал таблицу, состоящую из двух столбцов: первичный ключ с автонумерацией и varchar(max). Я использовал данные импорта для загрузки текстовых файлов в таблицу, чтобы каждая строка в файле была записью в таблице. Первичный ключ случайно представляет номер строки. Так что я мог бы написать что-то вроде:
select LineNumber, TextLine from [LogFile] order by LineNumber
Затем я подготовил еще одну таблицу со структурой, которая соответствовала записям в моем XML. В моих файлах XML была особенность, заключающаяся в том, что каждый тег «значение» находился в отдельной текстовой строке, а открывающий и закрывающий теги «записи» находились в отдельной строке.
Например:
<log>
<record>
<date>2018-07-27T09:54:20</date>
<millis>1532706860250</millis>
<sequence>13587</sequence>
<logger>registroweb.ServReg</logger>
<level>INFO</level>
<class>somepackage.someclass</class>
<method>methodname</method>
<thread>11153</thread>
<message>some very long text</message>
<param>another long text</param>
</record>
...
</log>
Это означало бы, что я мог бы выбрать все записи, где text_line = '‹log›' дал бы мне все открывающие теги записей, но, что наиболее важно, самообъединение таблицы с t2.line_number = t1.line_number + 1 всегда давало бы мне строку, содержащую тег даты, line_number+2 будет давать миллисекунды и так далее.
Итак, с помощью следующего запроса я смог преобразовать плоскую линейную таблицу в правильную таблицу:
insert into LogFileProcessed(
[date],
[millis],
[sequence],
[logger] ,
[level] ,
[class] ,
[method] ,
[thread] ,
[message],
[param]
)
select
--record.TextLine,
convert(datetime, replace(replace(ltrim(dte.TextLine), '<date>', ''), '</date>', ''), 126) [date],
convert(bigint, replace(replace(ltrim(mls.TextLine), '<millis>', ''), '</millis>', '')) [millis],
convert(bigint, replace(replace(ltrim(seq.TextLine), '<sequence>', ''), '</sequence>', '')) [sequence],
replace(replace(ltrim(logr.TextLine), '<logger>', ''), '</logger>', '') [logger],
replace(replace(ltrim(lvl.TextLine), '<level>', ''), '</level>', '') [level],
replace(replace(ltrim(cls.TextLine), '<class>', ''), '</class>', '') [class],
replace(replace(ltrim(mtd.TextLine), '<method>', ''), '</method>', '') [method],
replace(replace(ltrim(trd.TextLine), '<thread>', ''), '</thread>', '') [thread],
replace(replace(ltrim(msg.TextLine), '<message>', ''), '</message>', '') [message],
replace(replace(ltrim(prm.TextLine), '<param>', ''), '</param>', '') [param]
from LogFile record
left join LogFile dte on dte.LineNumber = record.LineNumber+1
left join LogFile mls on mls.LineNumber = record.LineNumber+2
left join LogFile seq on seq.LineNumber = record.LineNumber+3
left join LogFile logr on logr.LineNumber = record.LineNumber+4
left join LogFile lvl on lvl.LineNumber = record.LineNumber+5
left join LogFile cls on cls.LineNumber = record.LineNumber+6
left join LogFile mtd on mtd.LineNumber = record.LineNumber+7
left join LogFile trd on trd.LineNumber = record.LineNumber+8
left join LogFile msg on msg.LineNumber = record.LineNumber+9
left join LogFile prm on prm.LineNumber = record.LineNumber+10 and prm.TextLine <> '</record>' -- param is actually the only tag that is optional and some times is not present in the record.
where record.TextLine = '<record>'
order by 1, 2
Учитывая определенные ограничения, которые у меня были в то время, и структуру файлов, это работало достаточно хорошо для одноразовой задачи, позволяя мне выполнять регулярные запросы к данным без необходимости многократно запускать анализ XML или код обработки.
person
Jahaziel
schedule
23.08.2019