简单的几个 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 sales
for 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 
END
GO 

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 
END
GO 

ALTER PROCEDURE dbo.MainInsert
AS
BEGIN 
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 ;
    END
END 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