目的:统一接口,当数据结构发生变化时,前后端业务接口不发生变化,由业务具体解析结构。

规则:确定的接口用参数表(多行提交),不确定的参数用XML

 

  1.     (
  2.       HardwareNo INT ,
  3.       emvno INT ,
  4.       DATA XML
  5.     )
  6. VALUES ( 1, 1,
  7.           '<d><k>meter</k><v>1</v></d><d><k>run</k><v>100</v></d><d><k>work</k><v>80</v></d><d><k>dead</k><v>20</v></d>' ),
  8.         ( 2, 1,
  9.           '<d><k>meter</k><v>21</v></d><d><k>run</k><v>100</v></d><d><k>work</k><v>80</v></d><d><k>dead</k><v>20</v></d>' ),
  10.         ( 3, 1,
  11.           '<d><k>meter</k><v>31</v></d><d><k>run</k><v>100</v></d><d><k>work</k><v>80</v></d><d><k>dead</k><v>20</v></d><d><k>good</k><v>210</v></d>' )
  12.  
  13.  
  14. FROM @r
  15.  
  16.  
  17. DECLARE @xml XML
  18. SELECT @xml = data
  19. FROM @r
  20. SELECT T.c.value('k[1]', 'varchar(20)') k ,
  21.         T.c.value('v[1]', 'varchar(20)') v
  22. FROM @xml.nodes('/d') T ( c )
  23.  
  24.  
  25.  
  26. SELECT HardwareNo ,
  27.         T.c.value('k[1]', 'varchar(20)') k ,
  28.         T.c.value('v[1]', 'varchar(20)') v
  29. FROM @r
  30.         CROSS APPLY data.nodes('/d') AS T ( c )

 

输出结果:

 

  1. (3 行受影响)
  2. HardwareNo emvno DATA
  3. 1 1 <d><k>meter</k><v>1</v></d><d><k>run</k><v>100</v></d><d><k>work</k><v>80</v></d><d><k>dead</k><v>20</v></d>
  4. 2 1 <d><k>meter</k><v>21</v></d><d><k>run</k><v>100</v></d><d><k>work</k><v>80</v></d><d><k>dead</k><v>20</v></d>
  5. 3 1 <d><k>meter</k><v>31</v></d><d><k>run</k><v>100</v></d><d><k>work</k><v>80</v></d><d><k>dead</k><v>20</v></d><d><k>good</k><v>210</v></d>
  6.  
  7. (3 行受影响)
  8.  
  9. k v
  10. -------------------- --------------------
  11. meter 31
  12. run 100
  13. work 80
  14. dead 20
  15. good 210
  16.  
  17. (5 行受影响)
  18.  
  19. HardwareNo k v
  20. ----------- -------------------- --------------------
  21. 1 meter 1
  22. 1 run 100
  23. 1 work 80
  24. 1 dead 20
  25. 2 meter 21
  26. 2 run 100
  27. 2 work 80
  28. 2 dead 20
  29. 3 meter 31
  30. 3 run 100
  31. 3 work 80
  32. 3 dead 20
  33. 3 good 210
  34.  
  35. (13 行受影响)

 

 

 

 

 

 

05-02 00:19