XML常用方法

<!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery> <w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:SpaceForUL/> <w:BalanceSingleByteDoubleByteWidth/> <w:DoNotLeaveBackslashAlone/> <w:ULTrailSpace/> <w:DoNotExpandShiftReturn/> <w:AdjustLineHeightInTable/> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!-- [if !mso]> <object classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui> </object> <style> st1/:*{behavior:url(#ieooui) } </style> <![endif]--><!-- [if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:表格內文; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->

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

*/