使用变量提取XML中CURCVY1-4节点值插入SQL表的问题咨询

问题根因 #

• @tvar使用char(100)固定长度类型，拼接节点名后会自动补空格，和XML实际节点名无法匹配，导致取值返回null
• 存储过程sp_insert_to_my_table未定义输入参数，内部声明的@amount无法接收外部传入的值，同时存在类型拼写错误vachar→varchar
• 初始的query().value()写法逻辑错误，直接调用sql:variable("@tvar")返回的是变量的字符串值而非对应节点的内容

修正步骤 #

1. 修正存储过程定义 #

ALTER PROCEDURE sp_insert_to_my_table
@amount varchar(max) -- 定义为输入参数，修正拼写错误
AS
BEGIN
    INSERT INTO myTable (column1)
    VALUES (@amount)
END

2. 修正业务执行代码 #

DECLARE
    @tvar varchar(20), -- 改用可变长度字符类型，避免补空格
    @cnt INT = 1,
    @cnt_total int =5, 
    @xmldata xml='<NewDataSet>
    <ROWS>
     <POLIAGIN>PRE-AUTHORIZED</POLIAGIN>
     <CURCVY01>0.00</CURCVY01>
     <CURCVY02>253.00</CURCVY02>
     <CURCVY03>1523.50</CURCVY03>
     <CURCVY04>2815.50</CURCVY04>
     <CURCVY05>4129.00</CURCVY05>
   </ROWS>
</NewDataSet>'

WHILE @cnt < @cnt_total
BEGIN
    SET @tvar = 'CURCVY0' + CAST(@cnt AS varchar(5))
    -- 你调整后的取值逻辑本身是正确的，修复变量类型后即可正常取值
    EXEC sp_insert_to_my_table @xmldata.value('(/NewDataSet/ROWS/*[local-name() = sql:variable("@tvar")])[1]','nvarchar(max)')
    SET @cnt = @cnt + 1; 
END;

优化方案（无需循环，性能更高） #

可以直接用XML的nodes方法一次性提取所有符合要求的节点，无需循环遍历：

DECLARE @xmldata xml='<NewDataSet>
<ROWS>
 <POLIAGIN>PRE-AUTHORIZED</POLIAGIN>
 <CURCVY01>0.00</CURCVY01>
 <CURCVY02>253.00</CURCVY02>
 <CURCVY03>1523.50</CURCVY03>
 <CURCVY04>2815.50</CURCVY04>
 <CURCVY05>4129.00</CURCVY05>
</ROWS>
</NewDataSet>'

INSERT INTO myTable (column1)
SELECT 
    t.c.value('.','nvarchar(max)')
FROM @xmldata.nodes('/NewDataSet/ROWS/*[substring(local-name(),1,6) = "CURCVY" and cast(substring(local-name(),7,2) as int) between 1 and 4]') t(c)

内容的提问来源于stack exchange，提问作者Cordero