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,'<ProductDescriptionProductID="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)
/*
Databsetbls 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 Databsetbls 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
*/
