Разбор строки SQL Server xml в поле varchar

У меня есть столбец varchar в таблице, которая используется для хранения данных xml. Да, я знаю, что есть тип данных xml, который я должен использовать, но я думаю, что он был настроен до того, как тип данных xml стал доступен, поэтому сейчас мне нужно использовать varchar. :)

Сохраненные данные выглядят примерно так:

<xml filename="100100_456_484351864768.zip"  
     event_dt="10/5/2009 11:42:52 AM">
    <info user="TestUser" />
</xml>

Мне нужно проанализировать имя файла, чтобы получить цифры между двумя символами подчеркивания, которые в данном случае будут «456». Длина первой части имени файла «не должна» меняться, а средний номер изменится. Мне нужно решение, которое будет работать, если первая часть изменится по длине (вы знаете, что она изменится, потому что «не должно меняться» всегда означает, что она изменится).

Что касается того, что у меня есть на данный момент, я использую XQuery для извлечения имени файла, потому что я решил, что это, вероятно, лучше, чем прямые манипуляции со строками. Для этого я привел строку в xml, но я не эксперт по XQuery, поэтому, конечно, у меня возникают проблемы. Я нашел функцию для XQuery (substring-before), но не смог заставить ее работать (я даже не уверен, что эта функция будет работать с SQL Server). Может быть функция XQuery, чтобы сделать это легко, но если я не знаю об этом.

Итак, я получаю имя файла из таблицы с запросом, подобным следующему:

select CAST(parms as xml).query('data(/xml/@filename)') as p
from Table1

Исходя из этого, я бы предположил, что смогу вернуть это обратно в строку, а затем выполнить некоторую функцию instring или charindex, чтобы выяснить, где находятся символы подчеркивания, чтобы я мог инкапсулировать все это в функцию подстроки, чтобы выбрать часть Я нуждаюсь. Не заходя слишком далеко в это, я почти уверен, что в конечном итоге смогу сделать это таким образом, но я знаю, что должен быть более простой способ. Таким образом, в операторе SQL появилось бы огромное нечитаемое поле, которое, даже если бы я переместил его в функцию, все равно сбивало бы с толку, пытаясь понять, что происходит.

Я уверен, что это проще, чем это, поскольку это кажется простой манипуляцией со строками. Возможно, кто-то может указать мне в правильном направлении. Спасибо


sql sql-server tsql xquery
person Dusty    schedule 09.10.2009    source источник
comment
Извините, я только сейчас увидел этот комментарий. Сейчас мы используем SQL Server 2008.   -  person Dusty    schedule 13.10.2009

Ответы (3)


arrow_upward
7
arrow_downward

Вы можете использовать XQuery для этого - просто измените свой оператор на:

SELECT
   CAST(parms as xml).value('(/xml/@filename)[1]', 'varchar(260)') as p
FROM 
   dbo.Table1

Это дает вам VARCHAR(260), достаточно длинный, чтобы содержать любое действительное имя файла и путь - теперь у вас есть строка и вы можете работать с ней с помощью SUBSTRING и т.д.

Марк

person marc_s    schedule 10.10.2009
comment
Я ценю ваш ответ, но я смог получить это с помощью запроса в своем сообщении, используя .query вместо .value. Я искал лучший способ разобрать имя файла, как только я его получил. Однако теперь, когда мы подошли к этому вопросу, какой предпочтительный метод — использовать .query или .value? - person Dusty; 12.10.2009
comment
query() возвращает все дерево результатов XDM как экземпляр типа данных XML; value() требует, чтобы ваш запрос возвращал только одно значение XDM, и преобразует его в некоторый тип SQL. Таким образом, обычно вы выбираете первое, когда вам действительно нужно вернуть XML-документ или фрагмент или, по крайней мере, набор узлов, и второе, когда вам нужно вернуть только одно значение. - person Pavel Minaev; 12.10.2009
comment
Спасибо. Это имеет смысл. Хотя это не дает вам очков, я проголосовал за ваш комментарий. :) - person Dusty; 13.10.2009
comment
+1 Потому что я должен использовать .value вместо .query - person Dusty; 13.10.2009

arrow_upward
4
arrow_downward

Самый простой способ сделать это — использовать SUBSTRING и CHARINDEX. Предполагая (мудро или нет), что первая часть имени файла не меняет длину, но вы все еще хотите использовать XQuery для поиска имени файла, вот короткая реплика, которая делает то, что вы хотите:

declare @t table (
  parms varchar(max)
);
insert into @t values ('<xml filename="100100_456_484351864768.zip" event_dt="10/5/2009 11:42:52 AM"><info user="TestUser" /></xml>');

with T(fName) as (
  select cast(cast(parms as xml).query('data(/xml/@filename)') as varchar(100)) as p
  from @t
)
  select
    substring(fName,8,charindex('_',fName,8)-8) as myNum
  from T;

Существуют хитрые решения, использующие другие строковые функции, такие как REPLACE и PARSENAME или REVERSE, но ни одно из них, вероятно, не будет более эффективным или удобочитаемым. Одной из возможных возможностей является написание подпрограммы CLR, которая реализует обработку регулярных выражений в SQL.

Между прочим, если ваш xml всегда такой простой, я не вижу особых причин вообще использовать XQuery. Вот два запроса, которые извлекут нужное вам число. Второй безопаснее, если у вас нет контроля над дополнительными пробелами в вашей строке xml или над возможностью того, что первая часть имени файла изменит длину:

  select
    substring(parms,23,charindex('_',parms,23)-23) as myNum
  from @t;

  select
    substring(parms,charindex('_',parms)+1,charindex('_',parms,charindex('_',parms)+1)-charindex('_',parms)-1) as myNum
  from @t;
person Steve Kass    schedule 10.10.2009
comment
+1 Похоже, мне придется сделать то, что, как я думал, мне придется сделать, а именно использовать подстроку SQL Server для ее анализа. Я ценю ваш ответ и большую часть работы за меня. Я думаю, что сделаю функцию, которая делает что-то похожее на ваш первый пост, но в этой ситуации второй опубликованный вами пример кода будет работать, но я бы предпочел использовать XQuery для извлечения имени файла перед выполнением манипуляций со строками. Еще раз спасибо за помощь, и я отмечу это как ответ. - person Dusty; 12.10.2009

arrow_upward
1
arrow_downward

К сожалению, SQL Server не является совместимой реализацией XQuery — скорее, это довольно ограниченное подмножество черновой версии спецификации XQuery. У него нет не только fn:substring-before, но и fn:index-of, чтобы сделать это самостоятельно, используя fn:substring или fn:string-to-codepoints. Итак, насколько я могу судить, вы застряли здесь с SQL.

person Pavel Minaev    schedule 09.10.2009
comment
+1 Спасибо, я боялся, что в SQL Server есть ограниченное подмножество XQuery. Похоже, мне придется использовать функцию подстроки в SQL Server, чтобы сделать это так, как я думал, и как ответил Стив Касс. - person Dusty; 12.10.2009