Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Parse xml / 14 сообщений из 14, страница 1 из 1
23.01.2020, 17:22
    #39918012
medoed
medoed
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Parse xml
Что то туплю под вечер, не могу распарсить
Код: 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:33
    #39918020
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Parse xml
Код: 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
    #39918021
medoed
medoed
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Parse xml
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
| Ответить | Цитировать | Написать  
06.02.2020, 14:12
    #39923421
medoed
medoed
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Parse xml
medoed,

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

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

Код: 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, 12:11
    #39923857
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Parse xml
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:15
    #39923864
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Parse xml
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:17
    #39923865
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Parse xml
invm,

выбрасывать prepare и remove конечно не честно :) и они достаточно медленные, но если большой xml то скорее выигрывать будет этот вариант, хотя мне всегда казхалось что он очень тормознутый
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.02.2020, 12:18
    #39923868
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Parse xml
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:20
    #39923870
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Parse xml
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:22
    #39923871
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Parse xml
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:25
    #39923874
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Parse xml
TaPaK
выбрасывать prepare и remove конечно не честно :)
Ну remove можно. А prepare да, мой косяк.
alexeyvg
Может, они кардинально поправили, и нужно это теперь учитывать?
Похоже на то. В 2008R2 xpath - это смерть.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Parse xml / 14 сообщений из 14, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 156ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1686530]:
 0ms
get settings:
 8ms
get forum list:
 11ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 51ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 50ms
get tp. blocked users:
 1ms
others: 14ms
total:  156ms
x
x
Закрыть


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