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

*/