insert into Test1 values ( '<root> <data>ABCDEFG</data> <data>1234567</data> <data>12AB34FG</data> </root>' ) GO
select b.value('(text())[1]', 'varchar(100)') as datanode from Test1 a CROSS APPLY a.data.nodes('/root/data') AS Datanode(b) where b.value('(text())[1]', 'varchar(100)') like '%34%'
XML 형태에 따라 조금씩 다르지만
간단하게 샘플을 만들어 보았습니다.
create table Test1
(
data XML
)
GO
insert into Test1 values
(
'<root>
<data>ABCDEFG</data>
<data>1234567</data>
<data>12AB34FG</data>
</root>'
)
GO
select b.value('(text())[1]', 'varchar(100)') as datanode
from Test1 a
CROSS APPLY a.data.nodes('/root/data') AS Datanode(b)
where b.value('(text())[1]', 'varchar(100)') like '%34%'