xml常用方法 -ag凯发k8国际
examples:
--1、表連接條件查詢
declare @names xml
set @names = '
'
select
a.id,
name = @names.value('(/root/row[@id=sql:column("a.id")])[1]', 'varchar(10)')
from(
select id = 1 union all
select id = 2
)a
/*
id name
1 aa
2 bb
*/
--2、變量傳參
go
declare @a xml
set @a = '
'
declare @id int
set @id = 2
select @a.value('(/root/row[@id=sql:variable("@id")])[1]', 'varchar(10)')
--3、條件exist用法
if object_id('tempdb..#t') is not null
drop table #t
create table #t (
productid int primary key,
catalogdescription xml)
go
insert into #t values(1,'
go
select productid,
catalogdescription.value('(/productdescription/@productname)[1]', 'varchar(40)') as pname,
t.catalogdescription.exist('/productdescription[@productname="somename"]') as isexists
from #t as t
where t.catalogdescription.exist('/productdescription[@productname="somename"]')=1
/*
productid pname isexists
1 somename 1
*/
go
--4、xml的nodes cross apply 應用
declare @x xml
set @x ='