sqlserver XML查询 字符串中传入变量

以下脚本演示了通过sql:variable()在解析字符串中传入变量，将xml中times为0、1、2、3下的数据依次解析并插入到测试表。

DECLARE @A XML ,
    @b INT ,
    @c INT;
SELECT  @c = 0;
SELECT  @A = '<roots>
<root times="0">
    <cam0>0</cam0>
    <cam1>1</cam1>
    <cam2>2</cam2>
    <cam3>3</cam3>
    <棉流效率>99.95</棉流效率>
</root>
<root times="1">
    <cam0>0</cam0>
    <cam1>1</cam1>
    <cam2>2</cam2>
    <cam3>3</cam3>
    <棉流效率>99.96</棉流效率>
</root>
<root times="2">
    <cam0>0</cam0>
    <cam1>1</cam1>
    <cam2>2</cam2>
    <cam3>3</cam3>
    <棉流效率>99.97</棉流效率>
</root>
<root times="3">
    <cam0>0</cam0>
    <cam1>1</cam1>
    <cam2>2</cam2>
    <cam3>3</cam3>
    <棉流效率>99.98</棉流效率>
</root>
<root times="4">
    <cam0>99</cam0>
    <cam1>98</cam1>
    <cam2>78</cam2>
    <cam3>878</cam3>
    <棉流效率>99.98</棉流效率>
</root>
</roots>
';

SET @b = ( SELECT   MAX(L.Y.value('@times', 'int'))
           FROM     @A.nodes('/roots/root') AS L ( Y )
         );
WHILE @c <= @b
    BEGIN
;
        WITH  CTE AS ( SELECT   @A.query('//root[@times=sql:variable("@c")]') AS BB
                     )
            INSERT  INTO dbo.测试
                    ( times ,
                      cam0 ,
                      cam1 ,
                      cam2 ,
                      cam3 ,
                      棉流效率
                    )
                    SELECT  @c AS times ,
                            L.Y.value('cam0[1]', 'int') AS cam0 ,
                            L.Y.value('cam1[1]', 'int') AS cam1 ,
                            L.Y.value('cam2[1]', 'int') AS cam2 ,
                            L.Y.value('cam3[1]', 'INT') AS cam3 ,
                            L.Y.value('棉流效率[1]', 'decimal(5,2)') AS 棉流效率
                    FROM    CTE
                            CROSS APPLY CTE.BB.nodes('/root') AS L ( Y );
        SET @c = @c + 1;
    END;

最后的查询结果：