以下脚本演示了通过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;
最后的查询结果: