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

Откуда: Киров, Россия
Сообщений: 1091
народ, подскажите пожалуйста, как изящнее всего свести вот такой xml

<root>
	<info>
		<id>1</id>
		<detail>
			<id>1</id>
			<quality>1</quality>
			<quality>2</quality>
			<quality>3</quality>
		</detail>
	</info>
	<info>
		<id>2</id>
		<detail>
			<id>5</id>
			<quality>6</quality>
			<quality>7</quality>
			<quality>8</quality>
		</detail>
	</info>
</root>


к датасету с вот такими данными? т.е id сводим к колонкам, а все значения zub сворачиваем в строку через запятую

info_id, detail_id, quality
1, 1, [1,2,3]
2, 5, [6,7,8]
6 авг 18, 18:48    [21631994]     Ответить | Цитировать Сообщить модератору
 Re: хитрый запрос к xml данным  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4534
энди, странные желания, странный xml
+
declare @x xml = '<root>
	<info>
		<id>1</id>
		<detail>
			<id>1</id>
			<quality>1</quality>
			<quality>2</quality>
			<quality>3</quality>
		</detail>
	</info>
	<info>
		<id>2</id>
		<detail>
			<id>5</id>
			<quality>6</quality>
			<quality>7</quality>
			<quality>8</quality>
		</detail>
	</info>
</root>'
select 
 i.val.value('./id[1]', 'int')
,i.val.value('(./detail/id)[1]', 'int')
,i.val.query('for $pj in (./detail/quality) return string(concat($pj, '','' ))')
								 .value('.', 'nvarchar(max)')

from @x.nodes('/root/info') i(val)
6 авг 18, 18:57    [21632004]     Ответить | Цитировать Сообщить модератору
 Re: хитрый запрос к xml данным  [new]
энди
Member

Откуда: Киров, Россия
Сообщений: 1091
Ну xml я руками как пример набил, исходная портянка слишком велика.
Огромное спасибо, буду разбираться :)
6 авг 18, 19:13    [21632022]     Ответить | Цитировать Сообщить модератору
 Re: хитрый запрос к xml данным  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
declare @x xml = N'<root>
	<info>
		<id>1</id>
		<detail>
			<id>1</id>
			<quality>1</quality>
			<quality>2</quality>
			<quality>3</quality>
		</detail>
	</info>
	<info>
		<id>2</id>
		<detail>
			<id>5</id>
			<quality>6</quality>
			<quality>7</quality>
			<quality>8</quality>
		</detail>
	</info>
</root>';

select
 t.n.value('id[1]', 'int'),
 t.n.value('(detail/id)[1]', 'int'),
 replace(t.n.query('data(detail/quality)').value('.', 'varchar(max)'), ' ', ', ')
from
 @x.nodes('/root/info') t(n);
6 авг 18, 20:25    [21632119]     Ответить | Цитировать Сообщить модератору
 Re: хитрый запрос к xml данным  [new]
энди
Member

Откуда: Киров, Россия
Сообщений: 1091
грустно понимать что ты убог :)
где можно почитать хорошую доку по xquery для sql сервера?
6 авг 18, 22:32    [21632248]     Ответить | Цитировать Сообщить модератору
 Re: хитрый запрос к xml данным  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
энди, тут чуток есть

+
7 авг 18, 09:57    [21632503]     Ответить | Цитировать Сообщить модератору
 Re: хитрый запрос к xml данным  [new]
энди
Member

Откуда: Киров, Россия
Сообщений: 1091
Алан, спасибо конечно, честно попытался посмотреть ролик в ютубе, но непойму толи в ролике с звуком что-то не то, толи у меня глючит, минуты через 4 уши начали кровоточить :)

Ладно, пока тут сидят знающие люди спрошу еще про оптимизацию, я так понимаю обращение к родительскому значению элемента в xquery довольно медленное и при большом количестве записей все запрос с секунд переходит на минуты, как можно оптимизировать?

Сейчас запрос без обработки родителя отрабатывает за 3 секунды и возвращает около 25к записей.

SELECT 
--t.c.value('(../IDCASE)[1]','int') as IDCASE,
t.c.value('(IDSERV)[1]','int') as IDSERV,
t.c.value('(PF)[1]','int') as PF,
t.c.value('(KodProfilGG)[1]','int') as KodProfilGG,
t.c.value('(DopInfo)[1]','varchar(400)') as DopInfo
FROM @x.nodes('/DI_List/SluchDI/UslDI') t(c)


Но стоит только включить ../IDCASE как скорость просто умирает, как лучше поступить для того чтобы быстродействие не просаживалось так кардинально?
7 авг 18, 11:01    [21632589]     Ответить | Цитировать Сообщить модератору
 Re: хитрый запрос к xml данным  [new]
энди
Member

Откуда: Киров, Россия
Сообщений: 1091
Сам отвечу, мало ли кому пригодится :)

SELECT 
c.value('IDCASE[1]','int') as IDCASE,
d.value('./IDSERV[1]','int') as IDSERV,
d.value('./PF[1]','int') as PF,
d.value('./KodProfilGG[1]','int') as KodProfilGG,
d.value('./DopInfo[1]','varchar(400)') as DopInfo
FROM @x.nodes('/DI_List/SluchDI') AS SluchDI(c)
cross apply SluchDI.c.nodes('UslDI') as UslDI(d)


Скорость не падает :)
7 авг 18, 12:08    [21632699]     Ответить | Цитировать Сообщить модератору
 Re: хитрый запрос к xml данным  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
энди,

openxml вам поможет радикально:
declare @h int;

exec sp_xml_preparedocument @h output, @x;

select
 *
from
 openxml(@h, '/DI_List/SluchDI/UslDI', 2)
 with
 (
  IDCASE int '../IDCASE',
  IDSERV int 'IDSERV',
  PF int 'PF',
  KodProfilGG int 'KodProfilGG',
  DopInfo varchar(400) 'DopInfo'
 );

exec sys.sp_xml_removedocument @h;
7 авг 18, 12:16    [21632718]     Ответить | Цитировать Сообщить модератору
 Re: хитрый запрос к xml данным  [new]
энди
Member

Откуда: Киров, Россия
Сообщений: 1091
Я так понимаю у openxml скорость не падает за счет того что xml сначала разбирается в памяти и доступ уже прямой к любому элементу без лишнего парсинга на лету как в случае xpath/xquery?
7 авг 18, 12:46    [21632762]     Ответить | Цитировать Сообщить модератору
 Re: хитрый запрос к xml данным  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7383
энди,

выберите данные в таблицу, а затем агрегируйте колонки в строки, например CLR агрегатной функцией, в хелпе есть пример, в инете можно найти модификацию кода с разделителями. Я полный нуб в C#, но создал и протестировал ее за полчаса. В 2017 есть встроенный агрегатор.
7 авг 18, 13:14    [21632802]     Ответить | Цитировать Сообщить модератору
 Re: хитрый запрос к xml данным  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
энди
Я так понимаю у openxml скорость не падает за счет того что xml сначала разбирается в памяти и доступ уже прямой к любому элементу без лишнего парсинга на лету как в случае xpath/xquery?
Примерно так.
7 авг 18, 13:41    [21632843]     Ответить | Цитировать Сообщить модератору
 Re: хитрый запрос к xml данным  [new]
энди
Member

Откуда: Киров, Россия
Сообщений: 1091
Владислав Колосов
энди,

выберите данные в таблицу, а затем агрегируйте колонки в строки, например CLR агрегатной функцией, в хелпе есть пример, в инете можно найти модификацию кода с разделителями. Я полный нуб в C#, но создал и протестировал ее за полчаса. В 2017 есть встроенный агрегатор.


Да я в курсе что 2017 уже есть встроенный агрегатор строк, к сожалению у части клиентов версии более старые, и заставить их всех проапгрейдится до 2017 возможности нет
7 авг 18, 14:53    [21632984]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить