简单的几个 T-SQL 操作 XML 的小例子

XML 就是一种数据类型，应用场景必须要有相应的编程语言，我们以 TSQL 为例，讨论下如何装载 XML 数据，以及如何解析 XML 数据.

XML 数据，完全的表现形式，是 element , element attributes, sub-element, sub-element attribute & content 。那么其实就有两种表现形式:
1 元素形式, 只有元素和元素内容： element + subelement content

    <order>              <orderid> 0001</orderid>              <amount> 20000</amount>    </oreder>

2 属性的形式，只有元素和属性：element + attributes

    <orederid="0001" amount=2000 />

TSQL 中，装载最简单的 XML 数据，用 select for xml 就可以实现了。这里简单的介绍下几个应用例子，方便日后查询。

应用一： 针对只有元素与内容
1 用指令 elements

 select * from sales_history for xml, elements

应用二：针对属性元素
1 不带任何参数的 RAW，Auto 模式都将列转化成为元素

 selec * from sales_history for xml raw select * from sales_history for xml auto

2 Path 模式下，在列前面加@符号，将列映射成为属性

select orderid as "@orderid", order_totals from sales_history for xml path('orders')

同样，如果要解析这 XML 数据，可以选择的方法是 value (), query()

应用一： 针对只有元素与内容
1. value(‘path’,’type’)

应用二：针对属性元素
1. value(‘/xpath/@attributename’,’type’)

MSDN 是这样对装载 XML 数据做总结的：

select ...for xml:  RAW  AUTO  EXPLICIT  PATH

其中， RAW， AUTO 比较简单直观，将每一行的数据，分别以 row 和 表名或者其他复合对象名（heuristic) 作为元素，将每一个列作为属性集合起来。后两者比较难，EXPLICIT 特别是作为重武器应用的。

模式后面带指令，模式及指令都可以自带参数：

select * from salesfor xml  Raw('order'),  Type, Root('sales')

一个简单的返回子存储过程运行结果的例子:

ALTER procedure dbo.innerInsertionOne@ResultMessage xml output as begin BEGIN TRY     insert into dbo.region(regionId,regionName) values(100,'JiangSu-Wuxi') ;    set @ResultMessage = (    select 'InnerInsertionOne' as EntityPart    , 'Success' as Result    , 'Successfully Inserted' as ErrorMessage    for XML PATH('WorkPart'),ROOT('ResultSet'), TYPE,ELEMENTS ) ;END TRY BEGIN CATCH    IF @@TRANCOUNT>0 ROLLBACK TRANSACTION ;    SET @ResultMessage = (    select 'InnerInsertionOne' as EntityPart    , 'Failure' as Result    , 'Failed to be Inserted' as ErrorMessage    for XML PATH('WorkPart'),ROOT('ResultSet'), TYPE,ELEMENTS ) ;END CATCH ENDGO 

ALTER procedure dbo.innerInsertionTwo@ResultMessage xml output as begin BEGIN TRY     insert into dbo.region(regionId,regionName) values('1-100X','JiangSu-Wuxi') ;    set @ResultMessage = (    select 'InnerInsertionTwo' as EntityPart    , 'Success' as Result    , 'Successfully Inserted' as ErrorMessage    for XML PATH('WorkPart'),ROOT('ResultSet'), TYPE,ELEMENTS ) ;END TRY BEGIN CATCH    IF @@TRANCOUNT>0 ROLLBACK TRANSACTION ;    SET @ResultMessage = (    select 'InnerInsertionTwo' as EntityPart    , 'Failure' as Result    , 'Failed to be Inserted' as ErrorMessage    for XML PATH('WorkPart'),ROOT('ResultSet'), TYPE,ELEMENTS ) ;END CATCH ENDGO 

ALTER PROCEDURE dbo.MainInsertASBEGIN BEGIN TRY     DECLARE @RESULTMESSAGE XML ;    DECLARE @RESULTRETURN INT = -1 ;    DECLARE @WORKFLOW TABLE(Entity varchar(100), ProcessDetail XML) ;    EXEC @RESULTRETURN = dbo.innerInsertionOne @ResultMessage = @RESULTMESSAGE output ;    SELECT @RESULTRETURN;    INSERT INTO @WORKFLOW(Entity,ProcessDetail)VALUES('dbo.innerInsertionOne',@RESULTMESSAGE);    EXEC @RESULTRETURN = dbo.innerInsertionTwo @ResultMessage = @RESULTMESSAGE OUTPUT ;    INSERT INTO @WORKFLOW(Entity,ProcessDetail)VALUES('dbo.innerInsertionTwo',@RESULTMESSAGE);    IF EXISTS(SELECT TOP 1 1 FROM (SELECT ProcessDetail,            ProcessDetail.value('(/ResultSet/WorkPart/Result)[1]','VARCHAR(MAX)')AS RESULT    FROM @WORKFLOW ) RES WHERE RESULT = 'Failure')    BEGIN         IF EXISTS(SELECT TOP 1 1 FROM (        SELECT ProcessDetail.value('(/ResultSet/WorkPart/Result)[1]','VARCHAR(MAX)')AS RESULT        ,ProcessDetail.value('(/ResultSet/WorkPart/EntityPart)[1]','VARCHAR(MAX)')AS EntityPart        ,ProcessDetail.value('(/ResultSet/WorkPart/ErrorMessage)[1]','VARCHAR(MAX)')AS ErrorMessage        FROM @WORKFLOW         WHERE ProcessDetail.value('(/ResultSet/WorkPart/Result)[1]','VARCHAR(MAX)') ='Failure')tmp        WHERE EntityPart = 'InnerInsertionTwo' )         BEGIN             RAISERROR('FatalProcessFailed',16,1) ;        END        RETURN 10 ;    ENDEND TRY BEGIN CATCH     IF @@TRANCOUNT> 0 ROLLBACK TRANSACTION ;    set @ResultMessage = (    select 'MainTransaction' as EntityPart    , 'Failure' as Result    , ERROR_MESSAGE() as ErrorMessage    for XML PATH('WorkPart'),ROOT('ResultSet'), TYPE,ELEMENTS ) ;    INSERT INTO @WORKFLOW(Entity,ProcessDetail)VALUES('MainProcess',@RESULTMESSAGE);        SELECT ProcessDetail.value('(/ResultSet/WorkPart/Result)[1]','VARCHAR(MAX)')AS RESULT        ,ProcessDetail.value('(/ResultSet/WorkPart/EntityPart)[1]','VARCHAR(MAX)')AS EntityPart        ,ProcessDetail.value('(/ResultSet/WorkPart/ErrorMessage)[1]','VARCHAR(MAX)')AS ErrorMessage        FROM @WORKFLOW         WHERE ProcessDetail.value('(/ResultSet/WorkPart/Result)[1]','VARCHAR(MAX)') ='Failure'        AND ProcessDetail.value('(/ResultSet/WorkPart/ErrorMessage)[1]','VARCHAR(MAX)') <>'FatalProcessFailed';        RETURN 999 ;END CATCH END GO