ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Parse xml
14 сообщений из 14, страница 1 из 1
Parse xml
    #39918012
Фотография medoed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
medoed
Участник
Что то туплю под вечер, не могу распарсить
Код: xml
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
declare @pXml xml= 
 '<result>
  <items limit="65000" offset="0" total="1">
    <item id="157">
      <property name="id" type="int" primary-key="1">1575</property>
      <property name="emit" type="int">16445</property>
      <property name="emit.em_inn" type="varchar">777</property>
    </item>
    <item id="158">
      <property name="id" type="int" primary-key="1">155</property>
      <property name="emit" type="int">16448</property>
      <property name="emit.em_inn" type="varchar">788</property>
    </item>
  </items>
</result>'



Пробую так, но что то упускаю:
Код: sql
1.
2.
3.
select  t.c.value('(./name/text())[1]', 'nvarchar(100)') as Name
	,t.c.value('text()[1]', 'varchar(300)')
from @pXml.nodes('result/items/item/property') as t(c)



Надо получить в табличном виде:
157,1575,16445,777
158,155,16448,788

Подскажите плиз.
...
Рейтинг: 0 / 0
23.01.2020, 17:22
| Ответить | Цитировать | Написать  
Parse xml
    #39918020
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
select
 t.n.value('@id', 'int'),
 t.n.value('property[@name = "id"][1]', 'int'),
 t.n.value('property[@name = "emit"][1]', 'int'),
 t.n.value('property[@name = "emit.em_inn"][1]', 'int')
from
 @pXml.nodes('/result/items/item') t(n);
...
Рейтинг: 0 / 0
23.01.2020, 17:33
| Ответить | Цитировать | Написать  
Parse xml
    #39918021
Фотография medoed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
medoed
Участник
invm
Код: sql
1.
2.
3.
4.
5.
6.
7.
select
 t.n.value('@id', 'int'),
 t.n.value('property[@name = "id"][1]', 'int'),
 t.n.value('property[@name = "emit"][1]', 'int'),
 t.n.value('property[@name = "emit.em_inn"][1]', 'int')
from
 @pXml.nodes('/result/items/item') t(n);


Спасибо!
...
Рейтинг: 0 / 0
23.01.2020, 17:36
| Ответить | Цитировать | Написать  
Parse xml
    #39923421
Фотография medoed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
medoed
Участник
medoed,

Подниму свою же темку, XML 5 мегабайт, порядка 7 тысяч item. Xml тотже и запрос долго фетчится, порядка 5-ти минут на Production сервере,на тестовом - 10 секунд.
Как можно ускорить , может индекс накатить или закешировать план или как то запрос переписать?
Подскажите плиз, спасибо!
...
Рейтинг: 0 / 0
06.02.2020, 14:12
| Ответить | Цитировать | Написать  
Parse xml
    #39923439
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
medoed,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
declare @pXml xml= 
 '<result>
  <items limit="65000" offset="0" total="1">
    <item id="157">
      <property name="id" type="int" primary-key="1">1575</property>
      <property name="emit" type="int">16445</property>
      <property name="emit.em_inn" type="varchar">777</property>
    </item>
    <item id="158">
      <property name="id" type="int" primary-key="1">155</property>
      <property name="emit" type="int">16448</property>
      <property name="emit.em_inn" type="varchar">788</property>
    </item>
  </items>
</result>';

declare @h int;

exec sys.sp_xml_preparedocument @h output, @pXml;

select
 *
from
 openxml(@h, '/result/items/item')
with
(
 id int '@id',
 [property/id] int 'property[@name = "id"][1]',
 [property/name] int 'property[@name = "emit"][1]',
 [property/emit.em_inn] int 'property[@name = "emit.em_inn"][1]'
);

exec sys.sp_xml_removedocument @h;
...
Рейтинг: 0 / 0
06.02.2020, 14:29
| Ответить | Цитировать | Написать  
Parse xml
    #39923453
Фотография medoed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
medoed
Участник
invm,

Снова стало работать за секунды, спасибо огромнейшее, я Ваш должник!
...
Рейтинг: 0 / 0
06.02.2020, 14:50
| Ответить | Цитировать | Написать  
Parse xml
    #39923847
Sergey Syrovatchenko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergey Syrovatchenko
Гость
Как вариант можно еще и так:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
DECLARE @pXml XML = N'
<result>
  <items limit="65000" offset="0" total="1">
    <item id="157">
      <property name="id" type="int" primary-key="1">1575</property>
      <property name="emit" type="int">16445</property>
      <property name="emit.em_inn" type="varchar">777</property>
    </item>
    <item id="158">
      <property name="id" type="int" primary-key="1">155</property>
      <property name="emit" type="int">16448</property>
      <property name="emit.em_inn" type="varchar">788</property>
    </item>
  </items>
</result>'

SELECT [id]
     , [id] = MAX(CASE WHEN nm = 'id' THEN val END)
     , [emit] = MAX(CASE WHEN nm = 'emit' THEN val END)
     , [emit.em_inn] = MAX(CASE WHEN nm = 'emit.em_inn' THEN val END)
FROM (
    SELECT id = t.c.value('@id', 'int')
         , nm = t2.c2.value('@name', 'SYSNAME')
         , val = t2.c2.value('text()[1]', 'SYSNAME')
    FROM @pXml.nodes('/result/items/item') t(c)
    CROSS APPLY t.c.nodes('property') t2(c2)
) t
GROUP BY id


Но в теории вариант с OPENXML должен быть чуть быстрее
...
Рейтинг: 0 / 0
07.02.2020, 11:53
| Ответить | Цитировать | Написать  
Parse xml
    #39923857
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Sergey Syrovatchenko
Но в теории вариант с OPENXML должен быть чуть быстрее
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
declare @pXml xml= 
 '<result>
  <items limit="65000" offset="0" total="1">
    <item id="157">
      <property name="id" type="int" primary-key="1">1575</property>
      <property name="emit" type="int">16445</property>
      <property name="emit.em_inn" type="varchar">777</property>
    </item>
    <item id="158">
      <property name="id" type="int" primary-key="1">155</property>
      <property name="emit" type="int">16448</property>
      <property name="emit.em_inn" type="varchar">788</property>
    </item>
  </items>
</result>';

declare @h int, @x xml, @items xml = @pXml.query('/result/items');

select
 @x = t.x
from
 (
  select top (1000)
   @items as [*]
  from
   master.dbo.spt_values a cross join
   master.dbo.spt_values b
  for xml path(''), type, root('result')
 ) t(x);

exec sys.sp_xml_preparedocument @h output, @x;

declare @id int, @p__id int, @p__emit int, @p__inn int;

set statistics time on;

select
 @id = id, @p__id = [property/id], @p__emit = [property/name], @p__inn = [property/emit.em_inn]
from
 openxml(@h, '/result/items/item')
with
(
 id int '@id',
 [property/id] int 'property[@name = "id"][1]',
 [property/name] int 'property[@name = "emit"][1]',
 [property/emit.em_inn] int 'property[@name = "emit.em_inn"][1]'
);

SELECT @id = [id]
     , @p__id = MAX(CASE WHEN nm = 'id' THEN val END)
     , @p__emit = MAX(CASE WHEN nm = 'emit' THEN val END)
     , @p__inn = MAX(CASE WHEN nm = 'emit.em_inn' THEN val END)
FROM (
    SELECT id = t.c.value('@id', 'int')
         , nm = t2.c2.value('@name', 'SYSNAME')
         , val = t2.c2.value('text()[1]', 'SYSNAME')
    FROM @x.nodes('/result/items/item') t(c)
    CROSS APPLY t.c.nodes('property') t2(c2)
) t
GROUP BY id;

set statistics time off;

exec sys.sp_xml_removedocument @h;


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
 2008R2 
SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 45 ms.

 SQL Server Execution Times:
   CPU time = 8438 ms,  elapsed time = 8610 ms.

 2019 
 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 69 ms.

 SQL Server Execution Times:
   CPU time = 63 ms,  elapsed time = 64 ms.
...
Рейтинг: 0 / 0
07.02.2020, 12:11
| Ответить | Цитировать | Написать  
Parse xml
    #39923864
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
invm,

Ага, значит, время sp_xml_preparedocument не учитываем, Ок :-)

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
declare @pXml xml= 
 '<result>
  <items limit="65000" offset="0" total="1">
    <item id="157">
      <property name="id" type="int" primary-key="1">1575</property>
      <property name="emit" type="int">16445</property>
      <property name="emit.em_inn" type="varchar">777</property>
    </item>
    <item id="158">
      <property name="id" type="int" primary-key="1">155</property>
      <property name="emit" type="int">16448</property>
      <property name="emit.em_inn" type="varchar">788</property>
    </item>
  </items>
</result>';

declare @h int, @x xml, @items xml = @pXml.query('/result/items');

select
 @x = t.x
from
 (
  select top (1000)
   @items as [*]
  from
   master.dbo.spt_values a cross join
   master.dbo.spt_values b
  for xml path(''), type, root('result')
 ) t(x);

declare @id int, @p__id int, @p__emit int, @p__inn int;

set statistics time on;

exec sys.sp_xml_preparedocument @h output, @x;

--set statistics time on;

select
 @id = id, @p__id = [property/id], @p__emit = [property/name], @p__inn = [property/emit.em_inn]
from
 openxml(@h, '/result/items/item')
with
(
 id int '@id',
 [property/id] int 'property[@name = "id"][1]',
 [property/name] int 'property[@name = "emit"][1]',
 [property/emit.em_inn] int 'property[@name = "emit.em_inn"][1]'
);

SELECT @id = [id]
     , @p__id = MAX(CASE WHEN nm = 'id' THEN val END)
     , @p__emit = MAX(CASE WHEN nm = 'emit' THEN val END)
     , @p__inn = MAX(CASE WHEN nm = 'emit.em_inn' THEN val END)
FROM (
    SELECT id = t.c.value('@id', 'int')
         , nm = t2.c2.value('@name', 'SYSNAME')
         , val = t2.c2.value('text()[1]', 'SYSNAME')
    FROM @x.nodes('/result/items/item') t(c)
    CROSS APPLY t.c.nodes('property') t2(c2)
) t
GROUP BY id;

set statistics time off;

exec sys.sp_xml_removedocument @h;

SQL2016

 SQL Server Execution Times:
   CPU time = 109 ms,  elapsed time = 111 ms.

 SQL Server Execution Times:
   CPU time = 94 ms,  elapsed time = 95 ms.

 SQL Server Execution Times:
   CPU time = 109 ms,  elapsed time = 105 ms.

...
Рейтинг: 0 / 0
07.02.2020, 12:15
| Ответить | Цитировать | Написать  
Parse xml
    #39923865
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
invm,

выбрасывать prepare и remove конечно не честно :) и они достаточно медленные, но если большой xml то скорее выигрывать будет этот вариант, хотя мне всегда казхалось что он очень тормознутый
...
Рейтинг: 0 / 0
07.02.2020, 12:17
| Ответить | Цитировать | Написать  
Parse xml
    #39923868
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
alexeyvg
Ага, значит, время sp_xml_preparedocument не учитываем, Ок :-)
Код: plaintext
1.
2.
 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 51 ms.
...
Рейтинг: 0 / 0
07.02.2020, 12:18
| Ответить | Цитировать | Написать  
Parse xml
    #39923870
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
invm
alexeyvg
Ага, значит, время sp_xml_preparedocument не учитываем, Ок :-)
Код: plaintext
1.
2.
 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 51 ms.
Выше привёл результат для 2016, суммарно время с openxml в 2 раза больше времени с FROM @x.nodes
Может, они кардинально поправили, и нужно это теперь учитывать?
...
Рейтинг: 0 / 0
07.02.2020, 12:20
| Ответить | Цитировать | Написать  
Parse xml
    #39923871
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
alexeyvg,

+
 
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 94 ms.

SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 89 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

-----------------------------------------------------

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 110 ms, elapsed time = 117 ms.
...
Рейтинг: 0 / 0
07.02.2020, 12:22
| Ответить | Цитировать | Написать  
Parse xml
    #39923874
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
TaPaK
выбрасывать prepare и remove конечно не честно :)
Ну remove можно. А prepare да, мой косяк.
alexeyvg
Может, они кардинально поправили, и нужно это теперь учитывать?
Похоже на то. В 2008R2 xpath - это смерть.
...
Рейтинг: 0 / 0
07.02.2020, 12:25
| Ответить | Цитировать | Написать  
14 сообщений из 14, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Parse xml
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&fpage=70&tid=1686530]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 53ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 50ms
get tp. blocked users:
 2ms
others: 326ms
total:  477ms
созд. / загр.: 477ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]