Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как написать SQL запрос для извлечения определенных данных из XML / 12 сообщений из 12, страница 1 из 1
14.11.2020, 12:50
    #40018284
Tavocer
Tavocer
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как написать SQL запрос для извлечения определенных данных из XML
Добрый день, имеется xml

DECLARE @xml XML

SELECT @xml = N'
<methodResponse>
<params>
<param>
<value><array><data>
<value><struct>
<member><name>NAME</name><value><string>Name_1</string></value></member>
<member><name>ID_MESSAGE</name><value><i4>30512</i4></value></member>
</struct></value>
<value><struct>
<member><name>ID_MESSAGE</name><value><i4>30445</i4></value></member>
<member><name>NAME</name><value><string>Name_2</string></value></member>
</struct></value>
</data></array></value>
</param>
</params>
</methodResponse>
'

тех секций <value><struct>.........</struct></value> там конечно больше

как при помощи sql запроса извлечь тот <value><i4> ID_MESSAGE, которому соответствует определенный NAME = 'Name_1' ?

что то типа того:

SELECT
dat.value('name[1]','varchar(max)') AS Name,
dat.value('value[1]','varchar(max)') AS Value
FROM @xml.nodes('/methodResponse/params/param/value/array/data/value/struct') col(dat)
WHERE dat.value('value[1]','varchar(max)')='Name_1'
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.11.2020, 13:19
    #40018291
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как написать SQL запрос для извлечения определенных данных из XML
Tavocer,

вот так можно получить все, и, потом, условием в where оставить только тот, что тебе нужен (в принципе, это то, что ты и просил)
Код: 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.
DECLARE @xml XML

SELECT @xml = N'
<methodResponse>
<params>
<param>
<value><array><data>
<value><struct>
<member><name>NAME</name><value><string>Name_1</string></value></member>
<member><name>ID_MESSAGE</name><value><i4>30512</i4></value></member>
</struct></value>
<value><struct>
<member><name>ID_MESSAGE</name><value><i4>30445</i4></value></member>
<member><name>NAME</name><value><string>Name_2</string></value></member>
</struct></value>
</data></array></value>
</param>
</params>
</methodResponse>'

--select @xml

select 
	t1.c1.value('text()[1]', 'varchar(100)')
	,t2.c2.value('text()[1]', 'varchar(100)')
from @xml.nodes('methodResponse/params/param/value/array/data/value') as t(c)
outer apply t.c.nodes('struct/member/value/string') as t1(c1)
outer apply t.c.nodes('struct/member/value/i4') as t2(c2)
-- where t1.c1.value('text()[1]', 'varchar(100)') = 'Name_1'


(No column name)(No column name)Name_130512Name_230445
но,имхо, условием в xpath-запросе было бы лучше , но как это сделать не соображу ... :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.11.2020, 13:42
    #40018300
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как написать SQL запрос для извлечения определенных данных из XML
court
но,имхо, условием в xpath-запросе было бы лучше
воо :)
Код: sql
1.
2.
3.
4.
5.
6.
select 
	t1.c1.value('text()[1]', 'varchar(100)')
	,t2.c2.value('text()[1]', 'varchar(100)')
from @xml.nodes('methodResponse/params/param/value/array/data/value[struct/member/value/string="Name_1"]') as t(c)
outer apply t.c.nodes('struct/member/value/string') as t1(c1)
outer apply t.c.nodes('struct/member/value/i4') as t2(c2)


(No column name)(No column name)Name_130512
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.11.2020, 14:00
    #40018304
Tavocer
Tavocer
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как написать SQL запрос для извлечения определенных данных из XML
court,

спасибо большое, на самом деле, xml немного сложнее, есть еще один элемент с <i4>:

<value><struct>
<member><name>ID_MESSAGE</name><value><i4>30445</i4></value></member>
<member><name>NAME</name><value><string>Name_2</string></value></member>
<member><name>USER_LIST</name><value><i4>39159</i4></value></member>
</struct></value>

поэтому ваш sql запрос находит оба <i4>

прошу прощения, что я изначально упростил задачу, как переписать запрос, чтобы нашло только те значения ID_MESSAGE <value><i4> в узле, где
NAME = Name_2
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.11.2020, 14:13
    #40018308
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как написать SQL запрос для извлечения определенных данных из XML
Tavocer
court,

спасибо большое, на самом деле, xml немного сложнее, есть еще один элемент с <i4>:

<value><struct>
<member><name>ID_MESSAGE</name><value><i4>30445</i4></value></member>
<member><name>NAME</name><value><string>Name_2</string></value></member>
<member><name>USER_LIST</name><value><i4>39159</i4></value></member>
</struct></value>

поэтому ваш sql запрос находит оба <i4>

прошу прощения, что я изначально упростил задачу, как переписать запрос, чтобы нашло только те значения ID_MESSAGE <value><i4> в узле, где
NAME = Name_2

так, как-то, если правильно понял ...
Код: 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.
DECLARE @xml XML

SELECT @xml = N'
<methodResponse>
<params>
<param>
<value><array><data>
<value><struct>
<member><name>NAME</name><value><string>Name_1</string></value></member>
<member><name>ID_MESSAGE</name><value><i4>30512</i4></value></member>
</struct></value>
<value><struct>
<member><name>ID_MESSAGE</name><value><i4>30445</i4></value></member>
<member><name>NAME</name><value><string>Name_2</string></value></member>
<member><name>USER_LIST</name><value><i4>39159</i4></value></member>
</struct></value>
</data></array></value>
</param>
</params>
</methodResponse>'

select 
	t1.c1.value('text()[1]', 'varchar(100)')
	,t2.c2.value('text()[1]', 'varchar(100)')
from @xml.nodes('methodResponse/params/param/value/array/data/value[struct/member/value/string="Name_2"]') as t(c)
outer apply t.c.nodes('struct/member/value/string') as t1(c1)
outer apply t.c.nodes('struct/member/value/i4[../../name="ID_MESSAGE"]') as t2(c2)


(No column name)(No column name)Name_230445
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.11.2020, 14:51
    #40018318
Tavocer
Tavocer
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как написать SQL запрос для извлечения определенных данных из XML
court,

Ура, получилось, спасибо огромное! Теперь бы еще разобраться, как это работает ))))
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.11.2020, 16:04
    #40018327
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как написать SQL запрос для извлечения определенных данных из XML
court
но,имхо, условием в xpath-запросе было бы лучше , но как это сделать не соображу ... :)
Код: sql
1.
2.
3.
4.
5.
select
 t.n.value('(member[name = "NAME"]/value/string)[1]', 'varchar(100)'),
 t.n.value('(member[name = "ID_MESSAGE"]/value/i4)[1]', 'int')
from
 @xml.nodes('/methodResponse/params/param/value/array/data/value/struct[member[name = "NAME" and value[string = "Name_2"]]]') t(n);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.11.2020, 16:30
    #40018337
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как написать SQL запрос для извлечения определенных данных из XML
invm
court
но,имхо, условием в xpath-запросе было бы лучше , но как это сделать не соображу ... :)
Код: sql
1.
2.
3.
4.
5.
select
 t.n.value('(member[name = "NAME"]/value/string)[1]', 'varchar(100)'),
 t.n.value('(member[name = "ID_MESSAGE"]/value/i4)[1]', 'int')
from
 @xml.nodes('/methodResponse/params/param/value/array/data/value/struct[member[name = "NAME" and value[string = "Name_2"]]]') t(n);

Красиво ! Спасибо

Tavocer, обратите внимание
Если xml хоть сколько-нибудь большой, - будет получше моих "эплаев" )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.11.2020, 16:31
    #40018338
Tavocer
Tavocer
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как написать SQL запрос для извлечения определенных данных из XML
invm,

Абсолютно красиво!

А можно еще через переменную?
Вот так не получается (The argument 1 of the XML data type method "nodes" must be a string literal.):

DECLARE @xml XML,
@Nazvanie nvarchar(max)='Name_1';

SELECT @xml = N'...nash xml...'

select
t.n.value('(member[name = "NAME"]/value/string)[1]', 'varchar(100)'),
t.n.value('(member[name = "ID_MESSAGE"]/value/i4)[1]', 'int')
from
@xml.nodes('/methodResponse/params/param/value/array/data/value/struct[member[name = "NAME" and value[string = "'+@Nazvanie +'"]]]') t(n);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.11.2020, 16:46
    #40018341
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как написать SQL запрос для извлечения определенных данных из XML
Tavocer,

Код: sql
1.
2.
3.
4.
5.
6.
7.
DECLARE @Nazvanie nvarchar(max)='Name_1';

select
 t.n.value('(member[name = "NAME"]/value/string)[1]', 'varchar(100)'),
 t.n.value('(member[name = "ID_MESSAGE"]/value/i4)[1]', 'int')
from
 @xml.nodes('/methodResponse/params/param/value/array/data/value/struct[member[name = "NAME" and value[string = sql:variable("@Nazvanie")]]]') t(n);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.11.2020, 16:55
    #40018343
Tavocer
Tavocer
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как написать SQL запрос для извлечения определенных данных из XML
court,

спасибо, очень помогли
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.11.2020, 18:13
    #40018353
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как написать SQL запрос для извлечения определенных данных из XML
В общем, если не мудрить, то так
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
with t (name, id_message)
as
(
 select
  t.n.value('(member[name = "NAME"]/value/string)[1]', 'varchar(100)'),
  t.n.value('(member[name = "ID_MESSAGE"]/value/i4)[1]', 'int')
 from
  @xml.nodes('/methodResponse/params/param/value/array/data/value/struct') t(n)
)
select * from t where name = @name;


Если xml объемный и надо быстро, то так
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
declare @h int;

exec sys.sp_xml_preparedocument @h output, @xml;

select
 *
from
 openxml(@h, '/methodResponse/params/param/value/array/data/value/struct')
with
 (
  name varchar(100) 'member[name = "NAME"]/value/string',
  id_messaage int 'member[name = "ID_MESSAGE"]/value/i4'
 )
where
 name = @name;

exec sys.sp_xml_removedocument @h;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как написать SQL запрос для извлечения определенных данных из XML / 12 сообщений из 12, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 166ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1685408]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 61ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 46ms
get tp. blocked users:
 1ms
others: 15ms
total:  166ms
x
x
Закрыть


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