SQL对XML类型数据的处理

一、读取XML类型的变量 

DECLARE @strxml XML
SET @strxml = '<Delete><ID>1</ID><ID>2</ID><ID>3</ID></Delete>'
SELECT  T.ID.value('.', 'int') AS ID
FROM    @strxml.nodes('/Delete/ID') AS T ( ID )

二、读取DataSet类型文件

DECLARE @Xml XML
SET @Xml = '
<DataSet>
  <Table><ID>1</ID><Count>2</Count></Table>
  <Table><ID>3</ID><Count>4</Count></Table>
  <Table><ID>5</ID><Count>6</Count></Table>
</DataSet>'
SELECT  T2.ID.value('.', 'int') AS ID ,
        T3.[Count].value('.', 'int') AS [Count]
FROM    ( SELECT    T.Records.query('ID') AS ID ,
                    T.Records.query('Count') AS [Count]
          FROM      @Xml.nodes('/DataSet/Table') AS T ( Records )
        ) AS T1
        CROSS APPLY T1.ID.nodes('ID') AS T2 ( ID )
        CROSS APPLY T1.[Count].nodes('Count') AS T3 ( [Count] )