XML常用方法
Examples:
?
--1、表連接條件查詢(xún)
?
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 應(yīng)用
?
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的排序號(hào)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
*/
轉(zhuǎn)載于:https://www.cnblogs.com/Roy_88/archive/2011/01/04/5463077.html
總結(jié)
