OPENXML在存储过程中的应用

GO
/****** 对象:  StoredProcedure [dbo].[pro_role_operate]    脚本日期: 01/05/2010 14:56:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    XML格式：<root><role roleid="1" rolename="超级管理员" /></root>
-- =============================================
ALTER PROCEDURE [dbo].[pro_role_operate]
@pi_char_model        char(1),
@pi_nvch_xml_data        nvarchar(max), --数据部分XML
@pi_nvch_xml_para        nvarchar(max), --Where部分XML
@po_int_result        int output
AS
BEGIN
    declare @hdoc int;
    declare @status int;
    declare @sql_data nvarchar(max);
    declare @sql_para nvarchar(max);
    declare @role_id int;
    declare @role_name nvarchar(255);
    declare @sorting int;
    declare @para_role_id int;
    declare @para_role_name nvarchar(255);
    declare @para_sorting int;
    
    set @po_int_result=-1;
    set @sql_para=' where 1=1 ';

    if @pi_char_model = 'I' goto INS;
    if @pi_char_model = 'D' goto DEL;
    if @pi_char_model = 'U' goto UPD;
    if @pi_char_model = 'S' goto SEL;
    goto EXT;


--------------------------------------------------------
INS:
begin
    exec @status = sp_xml_preparedocument @hdoc output,@pi_nvch_xml_data;
    if @status = 0
    begin
        insert into dbo.tbl_role
        (role_name,sorting)
        select
            role_name, 
            sorting
        from openxml(@hdoc,'/root/role')
        with(
            role_name nvarchar(255) '@role_name',
            sorting int '@sorting'
        );
    end
    exec sp_xml_removedocument @hdoc;
    set @po_int_result=@@error;
    goto EXT;
end

--------------------------------------------------------
DEL:
begin
    if @pi_nvch_xml_para is not null and len(@pi_nvch_xml_para)>0
    begin
        exec @status = sp_xml_preparedocument @hdoc output,@pi_nvch_xml_para;
        if @status = 0
        begin
            select 
                @para_role_id = role_id, 
                @para_role_name = role_name,
                @para_sorting = sorting
            from openxml(@hdoc,'/root/role')
            with(
                role_id int '@role_id',
                role_name nvarchar(255) '@role_name',
                sorting int '@sorting'
            );
        end
        exec sp_xml_removedocument @hdoc;
    end
    --sql where
    if @para_role_id is not null and len(@para_role_id)>0
        set @sql_para = @sql_para + ' and role_id='+CONVERT(varchar(3),@para_role_id);

    if @para_role_name is not null and len(@para_role_name)>0
        set @sql_para = @sql_para + ' and role_name='''+@para_role_name+'''';

    if @para_sorting is not null and len(@para_sorting)>0
        set @sql_para = @sql_para + ' and sorting='+ CONVERT(varchar(3),@para_sorting);
    
    set @sql_data = 'delete from tbl_role'+@sql_para;
    exec(@sql_data);

    set @po_int_result=@@error;
    goto EXT;
end

--------------------------------------------------------
UPD:
begin
    --update
    exec @status = sp_xml_preparedocument @hdoc output,@pi_nvch_xml_data;
    if @status = 0
    begin
        select 
            @role_id=role_id, 
            @role_name=role_name, 
            @sorting=sorting
        from openxml(@hdoc,'/root/role')
        with(
            role_id int '@role_id',
            role_name nvarchar(255) '@role_name',
            sorting int '@sorting'
        );
    end
    exec sp_xml_removedocument @hdoc;

    --where
    if @pi_nvch_xml_para is not null and len(@pi_nvch_xml_para)>0
    begin
        exec @status = sp_xml_preparedocument @hdoc output,@pi_nvch_xml_para;
        if @status = 0
        begin
            select 
                @para_role_id = role_id, 
                @para_role_name = role_name,
                @para_sorting = sorting
            from openxml(@hdoc,'/root/role')
            with(
                role_id int '@role_id',
                role_name nvarchar(255) '@role_name',
                sorting int '@sorting'
            );
        end
        exec sp_xml_removedocument @hdoc;
    end

    --sql update
    set @sql_data = 'update tbl_role set';
    if @role_id is not null and len(@role_id)>0
        set @sql_data = @sql_data + ' role_id='+CONVERT(varchar(3),@role_id) +',';

    if @role_name is not null and len(@role_name)>0
        set @sql_data = @sql_data + ' role_name='''+@role_name +''',';

    if @sorting is not null and len(@sorting)>0
        set @sql_data = @sql_data + ' sorting='+CONVERT(varchar(3),@sorting) +',';
    
    --sql where
    if @para_role_id is not null and len(@para_role_id)>0
        set @sql_para = @sql_para + ' and role_id='+CONVERT(varchar(3),@para_role_id);

    if @para_role_name is not null and len(@para_role_name)>0
        set @sql_para = @sql_para + ' and role_name='''+@para_role_name+'''';

    if @para_sorting is not null and len(@para_sorting)>0
        set @sql_para = @sql_para + ' and sorting='+CONVERT(varchar(3),@para_sorting);

    select @sql_data = left(@sql_data,len(@sql_data)-1);
    set @sql_data = @sql_data +  @sql_para;
    exec(@sql_data);

    set @po_int_result=@@error;
    goto EXT;
end

--------------------------------------------------------
SEL:
begin
    if @pi_nvch_xml_para is not null and len(@pi_nvch_xml_para)>0
    begin 
        exec @status = sp_xml_preparedocument @hdoc output,@pi_nvch_xml_para;
        if @status = 0
        begin
            select 
                @para_role_id = role_id, 
                @para_role_name = role_name,
                @para_sorting = sorting
            from openxml(@hdoc,'/root/role')
            with(
                role_id int '@role_id',
                role_name nvarchar(255) '@role_name',
                sorting int '@sorting'
            );
        end
        exec sp_xml_removedocument @hdoc;
    end

    --sql where
    if @para_role_id is not null and len(@para_role_id)>0
        set @sql_para = @sql_para + ' and role_id='+CONVERT(varchar(3),@para_role_id);

    if @para_role_name is not null and len(@para_role_name)>0
        set @sql_para = @sql_para + ' and role_name='''+@para_role_name+'''';

    if @para_sorting is not null and len(@para_sorting)>0
        set @sql_para = @sql_para + ' and sorting='+CONVERT(varchar(3),@para_sorting);

    set @sql_data = 'select * from tbl_role'+@sql_para + ' order by sorting for XML RAW(''role''),root(''root''),elements';
    exec(@sql_data);
    
    set @po_int_result=@@error;
    goto EXT;
end

--------------------------------------------------------
EXT:
    return @po_int_result;

END

 

转自：http://houke2004.blog.163.com/blog/static/20863165200924205662/

SQL Server2005中使用XML－FOR XML(RAW模式查询)

 

 

ROW模式将查询结果中的每一行转换为带有通用标识符<row>（或提供的元素名）的XML元素。

1、以通用Row元素检索数据

1. select Cust.CustomerID CustID,CustomerType,   
2. SalesOrderID from Sales.Customer Cust   
3. join Sales.SalesOrderHeader [order]   
4. on Cust.CustomerID=[order].CustomerID   
5. order by Cust.CustomerID    
6. for XML RAW  

结果如下：

1. <row CustID="1" CustomerType="S" SalesOrderID="43860" />  
2. <row CustID="1" CustomerType="S" SalesOrderID="44501" />  
3. <row CustID="1" CustomerType="S" SalesOrderID="45283" />  
4. <row CustID="1" CustomerType="S" SalesOrderID="46042" />  

 

2、将数据作为元素检索

1. select Cust.CustomerID CustID,CustomerType,   
2. SalesOrderID from Sales.Customer Cust   
3. join Sales.SalesOrderHeader [order]   
4. on Cust.CustomerID=[order].CustomerID   
5. order by Cust.CustomerID    
6. for XML RAW,ELEMENTS  

结果如下：

1. <row>  
2.   <CustID>1</CustID>  
3.   <CustomerType>S</CustomerType>  
4.   <SalesOrderID>43860</SalesOrderID>  
5. </row>  
6. <row>  
7.   <CustID>1</CustID>  
8.   <CustomerType>S</CustomerType>  
9.   <SalesOrderID>44501</SalesOrderID>  
10. </row>  
11. <row>  
12.   <CustID>1</CustID>  
13.   <CustomerType>S</CustomerType>  
14.   <SalesOrderID>45283</SalesOrderID>  
15. </row>  

3、使用根元素和自定义的行元素名检索数据

1. select Cust.CustomerID CustID,CustomerType,   
2. SalesOrderID from Sales.Customer Cust   
3. join Sales.SalesOrderHeader [order]   
4. on Cust.CustomerID=[order].CustomerID   
5. order by Cust.CustomerID    
6. for XML RAW('Order'),Root('Orders')  

结果如下：

1. <Orders>  
2.   <Order CustID="1" CustomerType="S" SalesOrderID="43860" />  
3.   <Order CustID="1" CustomerType="S" SalesOrderID="44501" />  
4.   <Order CustID="1" CustomerType="S" SalesOrderID="45283" />  
5.   <Order CustID="1" CustomerType="S" SalesOrderID="46042" />  
6. </Orders>  

同时指定ELEMENTS选项：

1. select Cust.CustomerID CustID,CustomerType,   
2. SalesOrderID from Sales.Customer Cust   
3. join Sales.SalesOrderHeader [order]   
4. on Cust.CustomerID=[order].CustomerID   
5. order by Cust.CustomerID    
6. for XML RAW('Order'),Root('Orders'),ELEMENTS  

结果如下：

1. <Orders>  
2.   <Order>  
3.     <CustID>1</CustID>  
4.     <CustomerType>S</CustomerType>  
5.     <SalesOrderID>43860</SalesOrderID>  
6.   </Order>  
7.   <Order>  
8.     <CustID>1</CustID>  
9.     <CustomerType>S</CustomerType>  
10.     <SalesOrderID>44501</SalesOrderID>  
11.   </Order>  
12. </Orders>