XML 在SQL server中常用方法

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

*/