ReSQL.ru
     
powered by simpleCommunicator - 2.0.52     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как написать SQL запрос для извлечения определенных данных из XML
12 сообщений из 12, страница 1 из 1
Как написать SQL запрос для извлечения определенных данных из XML
    #40018284
Tavocer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tavocer
Гость
Добрый день, имеется 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, 12:50
| Ответить | Цитировать | Написать  
Как написать SQL запрос для извлечения определенных данных из XML
    #40018291
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
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:19
| Ответить | Цитировать | Написать  
Как написать SQL запрос для извлечения определенных данных из XML
    #40018300
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
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, 13:42
| Ответить | Цитировать | Написать  
Как написать SQL запрос для извлечения определенных данных из XML
    #40018304
Tavocer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
...
Рейтинг: 0 / 0
14.11.2020, 14:00
| Ответить | Цитировать | Написать  
Как написать SQL запрос для извлечения определенных данных из XML
    #40018308
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
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:13
| Ответить | Цитировать | Написать  
Как написать SQL запрос для извлечения определенных данных из XML
    #40018318
Tavocer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tavocer
Гость
court,

Ура, получилось, спасибо огромное! Теперь бы еще разобраться, как это работает ))))
...
Рейтинг: 0 / 0
14.11.2020, 14:51
| Ответить | Цитировать | Написать  
Как написать SQL запрос для извлечения определенных данных из XML
    #40018327
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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);
...
Рейтинг: 0 / 0
14.11.2020, 16:04
| Ответить | Цитировать | Написать  
Как написать SQL запрос для извлечения определенных данных из XML
    #40018337
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
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:30
| Ответить | Цитировать | Написать  
Как написать SQL запрос для извлечения определенных данных из XML
    #40018338
Tavocer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tavocer
Гость
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:31
| Ответить | Цитировать | Написать  
Как написать SQL запрос для извлечения определенных данных из XML
    #40018341
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
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:46
| Ответить | Цитировать | Написать  
Как написать SQL запрос для извлечения определенных данных из XML
    #40018343
Tavocer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tavocer
Гость
court,

спасибо, очень помогли
...
Рейтинг: 0 / 0
14.11.2020, 16:55
| Ответить | Цитировать | Написать  
Как написать SQL запрос для извлечения определенных данных из XML
    #40018353
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
В общем, если не мудрить, то так
Код: 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
14.11.2020, 18:13
| Ответить | Цитировать | Написать  
12 сообщений из 12, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как написать SQL запрос для извлечения определенных данных из XML
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=40018341&tid=1685408]:
 0ms
get settings:
 9ms
get forum list:
 12ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 353ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 54ms
get tp. blocked users:
 1ms
others: 329ms
total:  774ms
созд. / загр.: 774ms
x
x
Закрыть


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