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

Откуда:
Сообщений: 32
Добрый день, имеется 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'
14 ноя 20, 12:50    [22232001]     Ответить | Цитировать Сообщить модератору
 Re: Как написать SQL запрос для извлечения определенных данных из XML  [new]
court
Member

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

вот так можно получить все, и, потом, условием в where оставить только тот, что тебе нужен (в принципе, это то, что ты и просил)
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_130512
Name_230445

но,имхо, условием в xpath-запросе было бы лучше, но как это сделать не соображу ... :)
14 ноя 20, 13:19    [22232014]     Ответить | Цитировать Сообщить модератору
 Re: Как написать SQL запрос для извлечения определенных данных из XML  [new]
court
Member

Откуда:
Сообщений: 2160
court
но,имхо, условием в xpath-запросе было бы лучше
воо :)
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
14 ноя 20, 13:42    [22232028]     Ответить | Цитировать Сообщить модератору
 Re: Как написать SQL запрос для извлечения определенных данных из XML  [new]
Tavocer
Member

Откуда:
Сообщений: 32
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
14 ноя 20, 14:00    [22232036]     Ответить | Цитировать Сообщить модератору
 Re: Как написать SQL запрос для извлечения определенных данных из XML  [new]
court
Member

Откуда:
Сообщений: 2160
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

так, как-то, если правильно понял ...
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
14 ноя 20, 14:13    [22232043]     Ответить | Цитировать Сообщить модератору
 Re: Как написать SQL запрос для извлечения определенных данных из XML  [new]
Tavocer
Member

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

Ура, получилось, спасибо огромное! Теперь бы еще разобраться, как это работает ))))
14 ноя 20, 14:51    [22232056]     Ответить | Цитировать Сообщить модератору
 Re: Как написать SQL запрос для извлечения определенных данных из XML  [new]
invm
Member

Откуда: Москва
Сообщений: 9489
court
но,имхо, условием в xpath-запросе было бы лучше, но как это сделать не соображу ... :)
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);
14 ноя 20, 16:04    [22232076]     Ответить | Цитировать Сообщить модератору
 Re: Как написать SQL запрос для извлечения определенных данных из XML  [new]
court
Member

Откуда:
Сообщений: 2160
invm
court
но,имхо, условием в xpath-запросе было бы лучше, но как это сделать не соображу ... :)
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 хоть сколько-нибудь большой, - будет получше моих "эплаев" )
14 ноя 20, 16:30    [22232086]     Ответить | Цитировать Сообщить модератору
 Re: Как написать SQL запрос для извлечения определенных данных из XML  [new]
Tavocer
Member

Откуда:
Сообщений: 32
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);
14 ноя 20, 16:31    [22232087]     Ответить | Цитировать Сообщить модератору
 Re: Как написать SQL запрос для извлечения определенных данных из XML  [new]
court
Member

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

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);
14 ноя 20, 16:46    [22232091]     Ответить | Цитировать Сообщить модератору
 Re: Как написать SQL запрос для извлечения определенных данных из XML  [new]
Tavocer
Member

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

спасибо, очень помогли
14 ноя 20, 16:55    [22232095]     Ответить | Цитировать Сообщить модератору
 Re: Как написать SQL запрос для извлечения определенных данных из XML  [new]
invm
Member

Откуда: Москва
Сообщений: 9489
В общем, если не мудрить, то так
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 объемный и надо быстро, то так
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;
14 ноя 20, 18:13    [22232106]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить