XML常用方法
Examples:
--1 、表連接條件查詢
DECLARE @names XML
SET @names = <root>
<row id="1">aa</row>
<row id="2">bb</row>
</root>
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 = <root>
<row id="1">aa</row>
<row id="2">bb</row>
</root>
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, <ProductDescription ProductID="1" ProductName="SomeName" /> )
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 =
<RelOp NodeId="10" PhysicalOp="Index Seek" LogicalOp="Index Seek" >
<OutputList>
<ColumnReference Column="Bmk1010" />
<ColumnReference Database="[DB1]" Schema="[dbo]" Table="[Tab2]" Alias="[Tab2]" Column="DateModified" />
<ColumnReference Database="[DB1]" Schema="[dbo]" Table="[Tab2]" Alias="[Tab2]" Column="FID" />
</OutputList>
<Object Database="[DB1]" Schema="[dbo]" Table="[Tab2]" Index="[IX_Tab2_1]" Alias="[Tab2]" />
<OutputList>
<ColumnReference Column="Bmk1010" />
<ColumnReference Database="[DB1]" Schema="[dbo]" Table="[Tab2]" Alias="[Tab2]" Column="DateModified" />
<ColumnReference Database="[DB1]" Schema="[dbo]" Table="[Tab2]" Alias="[Tab2]" Column="FID" />
</OutputList>
<Object Database="[DB1]" Schema="[dbo]" Table="[[Tab1]]" Index="[IX_Tab2_3]" Alias="[[Tab1]]" />
<OutputList>
<ColumnReference Column="Bmk1010" />
<ColumnReference Database="[DB1]" Schema="[dbo]" Table="[Tab2]" Alias="[Tab2]" Column="DateModified" />
<ColumnReference Database="[DB1]" Schema="[dbo]" Table="[Tab2]" Alias="[Tab2]" Column="FID" />
</OutputList>
</RelOp>
<RelOp NodeId="12" >
<OutputList>
<ColumnReference Column="Bmk1010" />
<ColumnReference Database="[DB1]" Schema="[dbo]" Table="[Tab2]" Alias="[Tab2]" Column="DateModified" />
<ColumnReference Database="[DB1]" Schema="[dbo]" Table="[Tab2]" Alias="[Tab2]" Column="FID" />
</OutputList>
<Object Database="[DB1]" Schema="[dbo]" Table="[Tab2]" Alias="[Tab2]" />
<OutputList>
<ColumnReference Column="Bmk1010" />
<ColumnReference Database="[DB1]" Schema="[dbo]" Table="[Tab2]" Alias="[Tab2]" Column="DateModified" />
<ColumnReference Database="[DB1]" Schema="[dbo]" Table="[Tab2]" Alias="[Tab2]" Column="FID" />
</OutputList>
</RelOp>
select
t. c. value( @Database , nvarchar(255) ) Databse
, t. c. value( @Table , nvarchar(255) ) tbls
, t. c. value( @Index , nvarchar(255) ) indxs
from @x. nodes( //Object ) t( c)
/*
Databse tbls indxs
[DB1] [Tab2] [IX_Tab2_1]
[DB1] [[Tab1]] [IX_Tab2_3]
[DB1] [Tab2] NULL
*/
Select
u. d. value( @NodeId , nvarchar(255) ) NodeId
From @x. nodes( //RelOp ) u( d)
/*
10
12
*/
SELECT
u. d. value( @NodeId , nvarchar(255) ) NodeId,
t. c. value( @Database , nvarchar(255) ) Databse,
t. c. value( @Table , nvarchar(255) ) tbls,
t. c. value( @Index , nvarchar(255) ) indxs
FROM @x. nodes( RelOp ) u( d)
CROSS APPLY u. d. nodes( Object ) t( c)
/*
NodeId Databse tbls indxs
10 [DB1] [Tab2] [IX_Tab2_1]
10 [DB1] [[Tab1]] [IX_Tab2_3]
12 [DB1] [Tab2] NULL
*/
--5 、 XML 的排序號
补充：综合编程 , 其他综合 ,
