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

Откуда: Москва
Сообщений: 188
сервер mssql2005
вопрос в следующем
я получаю извне (из vbscript) строку (фрагмент) xml формата
'<Manager><User Name="Иванофф" id="23"><Type>Куратор</Type></User><User Name="Петрофф" id="11"><Type>Менеджер</Type></User><User Name="Сидорофф" id="75"><Type>Менеджер</Type></User></Manager>'
более наглядно
select convert (xml,'<Manager>...
<Manager>
  <User Name="Иванофф" id="23">
    <Type>Куратор</Type>
  </User>
  <User Name="Петрофф" id="11">
    <Type>Менеджер</Type>
  </User>
  <User Name="Сидорофф" id="75">
    <Type>Менеджер</Type>
  </User>
</Manager>
не подскажите как ОДНИМ запросом получить из исходной строки - строку, например вида
'Иванофф,Петрофф,Сидорофф'
или
'Куратор,Менеджер,Менеджер'
если одним запросом нельзя, то как минимально оформить это в виде функции?
30 мар 10, 13:21    [8553066]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
declare @xml xml
set @xml =
'<Manager>
  <User Name="Иванофф" id="23">
    <Type>Куратор</Type>
  </User>
  <User Name="Петрофф" id="11">
    <Type>Менеджер</Type>
  </User>
  <User Name="Сидорофф" id="75">
    <Type>Менеджер</Type>
  </User>
</Manager>'

declare @result nvarchar(max)

select @result = isnull(@result + ',' + name, name)
  from (select r.value('@Name[1]', 'nvarchar(255)') as name
          from @xml.nodes('/Manager/User') as t(r)
        ) t
        
print @result
30 мар 10, 13:34    [8553204]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
По второму вопросу
select @result = isnull(@result + ',' + name, name)
  from (select r.value('text()[1]', 'nvarchar(255)') as name
          from @xml.nodes('/Manager/User/Type') as t(r)
        ) t
30 мар 10, 13:36    [8553234]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
ef1
Member

Откуда: Москва
Сообщений: 188
Спасибо!
если не сложно - а по тегам выборка - какой синтаксис?
30 мар 10, 13:37    [8553240]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

select convert (xml,N'<Manager>
   <User Name="Иванофф" id="23">
     <Type>Куратор</Type>
   </User>
   <User Name="Петрофф" id="11">
     <Type>Менеджер</Type>
   </User>
   <User Name="Сидорофф" id="75">
     <Type>Менеджер</Type>
   </User>
</Manager>').query('for $s in /Manager/User/@Name return string($s)').value('text()[1]', 'nvarchar(max)')

Posted via ActualForum NNTP Server 1.4

30 мар 10, 13:37    [8553248]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
ef1
Member

Откуда: Москва
Сообщений: 188
Синхронно получилось ;-) Еще раз спасибо!
Т.е. лучше все-таки делать отдельную функцию - и передавать туда строку и условия выборки? чем формировать динамический запрос? скорость роли не играет - фрагменты до 2000символов
30 мар 10, 13:42    [8553313]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
daw,
круто!

позволю себе немножко дополнить
'...return concat(string($s), ",")'...
а как с последней запятой быть уж не знаю
30 мар 10, 13:42    [8553315]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
ef1
Member

Откуда: Москва
Сообщений: 188
все! ;-) опять вместе ;-)

Всем СПАСИБО за ответы!!!!!
30 мар 10, 13:43    [8553326]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
ef1
Member

Откуда: Москва
Сообщений: 188
еще вопрос, пока тема не 'остыла'
не покажите синтаксис для получения результата в виде колонки, например для атрибутов
 ФИО
Иванофф
Петрофф
Сидорофф
ps
результат запроса в виде строки - уйдет для визуального отображения на формах или в секцию in динамического запроса, а в виде колонки в recordset для VBScript...хотя там можно разобрать напрямую - но так для полноты картины... если не сложно конечно
30 мар 10, 14:00    [8553493]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
declare @xml xml
set @xml =
'<Manager>
  <User Name="Иванофф" id="23">
    <Type>Куратор</Type>
  </User>
  <User Name="Петрофф" id="11">
    <Type>Менеджер</Type>
  </User>
  <User Name="Сидорофф" id="75">
    <Type>Менеджер</Type>
  </User>
</Manager>'

/*declare @result nvarchar(max)*/

/*select @result = isnull(@result + ',' + name, name)
  from (*/select r.value('@Name[1]', 'nvarchar(255)') as name
          from @xml.nodes('/Manager/User') as t(r)
        /*) t*/
        
/*print @result*/
30 мар 10, 14:03    [8553524]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
ef1
или в секцию in динамического запроса
Если это единственная причина, по которой запрос пришлось сделать динамическим,
то рекомендую сделать его статическим
30 мар 10, 14:04    [8553543]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
ef1
Member

Откуда: Москва
Сообщений: 188
в двух словах - у нас система докоборота (LotsiaPDMPlus на базе mssql) - т.е. их макросы (их sql запросы) + наши sql запросы + наши VBScript(или JScript) с подключением к БД + их формы с вычисляемыми полями (на их статических запросах или на наших динамических, в этом случае запросах - текст запроса(и сам запрос соответственно) только один или вызов наших функций или хп БД) + отсутствие типа данных xml как такового - в общем открытая система - ищу пути оптимизации хранения/отображения/обработки данных в базовых(предложенных) типах (строка,число,дата-время)

еще раз СПАСИБО за полноценные ответы!
30 мар 10, 14:17    [8553673]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
ef1
Member

Откуда: Москва
Сообщений: 188
почитал XQuery, попробовал динамически сформировать сам query через sql:variable - в смысле вставить в строковый литерал запроса переменные переданные в функцию (фрагмент поиска по тегам xml) ничего не получилось...
динамический запрос в хп формировать тоже не хочется, формировать несколько статических запросов в функции или хп тоже не вариант, сама наша система, как оказалось, строковые литералы, в данном случае, вообще корректно не передает никак.. - засада полная кругом
в связи с чем вопрос
не подскажите как - сам этот фрагмент xml вернуть простой таблицей - а ее я уже обработаю дальше...
никак не въеду как изменить предложенный вариант
select r.value('@Name[1]', 'nvarchar(255)') as name
          from @xml.nodes('/Manager/User') as t(r)
для всех данных xml т.е. из строки
declare @xml xml
set @xml =
'<Manager>
  <User Name="Иванофф" id="23">
    <Type>Куратор</Type>
  </User>
  <User Name="Петрофф" id="11">
    <Type>Менеджер</Type>
  </User>
  <User Name="Сидорофф" id="75">
    <Type>Менеджер</Type>
  </User>
</Manager>'
получить таблицу вида
  Name   id  Type 
Иванофф  23 Куратор
Петрофф  11 Менеджер
Сидорофф 75 Менеджер
можно без заголовков и все типа string...
не подскажите?
31 мар 10, 12:49    [8558409]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
select r.value('@Name[1]', 'nvarchar(255)') as name
      ,r.value('@id[1]', 'int') as id
      ,r.value('(Type/text())[1]', 'nvarchar(255)') as [Type]
 from @xml.nodes('/Manager/User') as t(r)
31 мар 10, 13:15    [8558624]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
ef1
Member

Откуда: Москва
Сообщений: 188
СУПЕР!!!!!!
то что нужно! Спасибо
31 мар 10, 13:17    [8558648]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
ef1
Member

Откуда: Москва
Сообщений: 188
наверно это не кончится ;-) проблема выявилась неожиданно - наша система не смогла принять сформированную таблицу!!!
судя по всему оказалось что если в функции встречаются обращения к XQuery - мы умираем!!!???
причем в таком виде - умираем просто (наша система падает без предупреждения)
Insert	@Ret 
select r.value('@Name[1]', 'varchar(100)') from @xml.nodes('//User') as t(r)
думал может - во временную таблицу и из нее - как то так
Insert Into @tbl Select r.value('@Name[1]', 'varchar(100)') from @xml.nodes('//User') as t(r)
Insert	@Ret 
Select * from @tbl
падаем точно также
переделал функцию на хп
уже не падаем но получаем странное сообщение
автор
SQLSTATE=42000
Microsoft OLE DB Provider for SQL Server
Ошибка SELECT, так как следующие параметры SET настроены неправильно: "ARITHABORT". Убедитесь, что указанные параметры SET можно использовать с методами индексированных представлений, с методами...

чем отличаются процедуры и функции которые возвращают одно и тоже
select * from lsdbo.Ric_Get_fXmlToTable()
exec lsdbo.Ric_Get_pXmlToTable
в данном конкретном случае так и не понял

заработало когда - поместил результат в постоянную таблицу БД и читал уже оттуда - типа 'убил' объект XQuery который все 'портил' с точки зрения нашей системы наверно...
а может и еще в чем дело - не знаю пока за что зацепиться - но в любом случае постоянная таблица не подходит - нужен разбор 'на лету'

может дело в уровне совместимости...
не будет большой наглости... - если я попрошу показать код
select r.value('@Name[1]', 'nvarchar(255)') as name
      ,r.value('@id[1]', 'int') as id
      ,r.value('(Type/text())[1]', 'nvarchar(255)') as [Type]
 from @xml.nodes('/Manager/User') as t(r)
но в синтаксисе MSSQL2000 типа FOR XML (без XQuery) - может это решит проблему?

ps
а так вроде вот оно - уже пощупать можно - а взять нельзя, да и техподдержка с разработчиками закончилась - вся надежда только на форум ...
1 апр 10, 12:32    [8564454]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
ef1
получаем странное сообщение
Ну так установите ARITHABORT в положение ON, Вам же сервер об этом говорит
1 апр 10, 12:38    [8564524]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
ef1
Member

Откуда: Москва
Сообщений: 188
упс
работает...
Спасибо - а в чем, точнее в какой момент xQuery в данном случае происходит переполнение или деление на ноль? тут вроде в запросе ничего такого нет...
1 апр 10, 12:50    [8564656]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
ef1
тут вроде в запросе ничего такого нет...
в тексте ошибки вроде* написано что это нужно для работы с xml вообще,
ни про какие конкретные запросы речь не идет

*вы текст ошибки недоцитировали
1 апр 10, 12:52    [8564686]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
ef1
Member

Откуда: Москва
Сообщений: 188
ок
сообщение показывает наша система... показывает не полностью обрезает кусок, а где посмотреть полностью не нашел, в профайлере - все чисто - вызывается хп и все...
хотя в принципе - результат есть и это главное
1 апр 10, 13:00    [8564769]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
ef1
Member

Откуда: Москва
Сообщений: 188
а нашел - вот полностью
автор
Ошибка SELECT, так как следующие параметры SET настроены неправильно: "ARITHABORT". Убедитесь, что указанные параметры SET можно использовать с методами индексированных представлений, с методами индексов по вычисляемым столбцам, с методами уведомлений о запросах или с методами XML-данных.

вроде и процедура достаточно 'чистая'
ALTER procedure [LSDBO].[Ric_Get_pXmlToTable]
as 
Begin

Declare @xml	xml
set @xml=convert(xml,
          '<Manager>
             <Filial Name="РПК-Москва" id="0">
               <User Name="Терещенко Елена Игоревна" id="72">
                 <Type>Куратор</Type>
                 <Set>Текущий</Set>
                 <Add>2010-03-30</Add>
                 <Del/>
               </User>
               <User Name="Вольнов Илья Николаевич" id="51">
                 <Type>Менеджер</Type>
                 <Set>Текущий</Set>
                 <Add>2010-03-30</Add>
                 <Del/>
               </User>
             </Filial>
           </Manager>')

--SET ARITHABORT ON
select r.value('@Name[1]', 'varchar(100)') from @xml.nodes('//User') as t(r)
--SET ARITHABORT OFF
end

ладно - вопрос снят - это уже проблемы интерпретации нашей системы
Спасибо еще раз!
1 апр 10, 13:09    [8564869]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: запрос к xml фрагменту  [new]
oleg12345
Member

Откуда:
Сообщений: 30
Паганель
declare @xml xml
set @xml =
'<Manager>
  <User Name="Иванофф" id="23">
    <Type>Куратор</Type>
  </User>
  <User Name="Петрофф" id="11">
    <Type>Менеджер</Type>
  </User>
  <User Name="Сидорофф" id="75">
    <Type>Менеджер</Type>
  </User>
</Manager>'

/*declare @result nvarchar(max)*/

/*select @result = isnull(@result + ',' + name, name)
  from (*/select r.value('@Name[1]', 'nvarchar(255)') as name
          from @xml.nodes('/Manager/User') as t(r)
        /*) t*/
        
/*print @result*/



Уважаемые форумчане, подскажите пожалуйста, можно ли данный скрипт применить не к конкретному xml, а к целому столбцу, содержащему xml? Т.е. параметр @xml как-то задать динамически (что-то вроде @xml IN (select xml from ...)). Если возможно, то как? У меня с ходу не получилось найти. Заранее спасибо!!
19 мар 18, 21:28    [21269615]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
oleg12345
Member

Откуда:
Сообщений: 30
забыл добавить - Sql Server 2012 )
19 мар 18, 21:29    [21269618]     Ответить | Цитировать Сообщить модератору
 Re: запрос к xml фрагменту  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
oleg12345,

declare @xml table (x_field xml)
Insert into @xml values(
'<Manager>
  <User Name="Иванофф" id="23">
    <Type>Куратор</Type>
  </User>
  <User Name="Петрофф" id="11">
    <Type>Менеджер</Type>
  </User>
  <User Name="Сидорофф" id="75">
    <Type>Менеджер</Type>
  </User>
</Manager>'),
('<Manager>
  <User Name="Иванов" id="24">
    <Type>Куратор</Type>
  </User>
  <User Name="Петров" id="12">
    <Type>Менеджер</Type>
  </User>
  <User Name="Сидоров" id="78">
    <Type>Менеджер</Type>
  </User>
</Manager>')

Select 
t.r.value('@id[1]', 'int') ,
t.r.value('@Name[1]', 'nvarchar(255)')
From @xml a
outer apply a.x_field.nodes('/Manager/User') as t(r)
20 мар 18, 05:06    [21269992]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить