Bezdepbonusu7Программирование: статьи и ответы
Меню

Вопрос

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

Автор вопроса: DustyОтветов: 3

У меня есть столбец 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 Server 2008. Dusty 13.10.2009

sqlsql-servertsqlxquery

Ответы

Ответ 1 · рейтинг 7 · marc_s

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

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

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

Марк

Ответ 2 · рейтинг 4 · Steve Kass

Самый простой способ сделать это - использовать 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;

Ответ 3 · рейтинг 1 · Pavel Minaev

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

Перевод вопроса и ответов сообщества Stack Overflow, лицензия CC BY-SA. Оригинал на Stack Overflow.

Похожие материалы