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 的排序號 DENSE_RANK
DECLARE @x2 XML
SET @x2 = '<a><b><c>abc</c><c>def</c></b><b><c>abc</c><c>def</c></b></a>'
SELECT
b_nodes. unique_b_node,
c_node. value( '(./text())[1]' , 'varchar(50)' ) AS c_val
FROM
(
SELECT
b_node. query( '.' ) AS b_xml,
b_node. value( 'for $s in . return count(../*[. << $s]) + 1' , 'int' ) AS unique_b_node
FROM @x2. nodes( '/a/b' ) AS b ( b_node)
) b_nodes
CROSS APPLY b_nodes. b_xml. nodes( '/b/c' ) AS c ( c_node)
SELECT
DENSE_RANK () OVER ( ORDER BY b_node) AS unique_b_node,
c_node. value( '(./text())[1]' , 'varchar(50)' ) AS c_val
FROM @x2. nodes( '/a/b' ) AS b ( b_node)
CROSS APPLY b. b_node. nodes( './c' ) AS c ( c_node)
/*
unique_b_node c_val
1 abc
1 def
2 abc
2 def
*/