Using XML in SQL2008 to deal with parameters of an indeterminate structure

Objective: Unified interface, when the data structure changes, the front-end service interface does not change, by the business specific analytic structure.

Rules: The identified interfaces are presented with a parameter table (MultiRow), and the indeterminate parameters are in XML

?

2. DECLARE @r TABLE
4. ???? (
6. ?????? Hardwareno INT,
8. ?????? Emvno INT,
10. ?????? DATA XML
12. ????)
14. INSERT Into @r
16. VALUES (1, 1,
18. ??????????‘ <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>'),
20. ???????? (2, 1,
22. ??????????‘ <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>'),
24. ???????? (3, 1,
26. ??????????‘ <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> ‘ )
28. ?
30. ?
32. SELECT *
34. from @r
36. ?
38. ?
40. DECLARE @xml XML
42. SELECT @xml = Data
44. from @r
46. SELECT t.c.value ('k[1]', 'varchar') K,
48. ???????? T.c.value ('v[1]', 'varchar') v
50. from @xml. Nodes ('d') T (c)
52. ?
54. ?
56. ?
58. SELECT Hardwareno,
60. ???????? T.c.value ('k[1]', 'varchar') K,
62. ???????? T.c.value ('v[1]', 'varchar') v
64. from @r
66. ???????? Cross APPLY data.nodes ('d') as T (c)

?

Output Result:

?

2. (3 rows affected)
4. Hardwareno Emvno DATA
6. 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>
8. 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>
10. 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>
12. ?
14. (3 rows affected)
16. ?
18. Kv
20. -------------------- --------------------
22. Meter 31
24. Run 100
26. Work the
28. Dead 20
30. Good 210
32. ?
34. (5 rows affected)
36. ?
38. Hardwareno K V
40. ----------- -------------------- --------------------
42. 1 meter 1
44. 1 run 100
46. 1 Work 80
48. 1 dead 20
50. 2 Meter 21
52. 2 Run 100
54. 2 Work 80
56. 2 dead 20
58. 3 Meter 31
60. 3 Run 100
62. 3 Work 80
64. 3 dead 20
66. 3 Good 210
68. ?
70. (13 rows affected)

?

?

?

?

?

?

Using XML in SQL2008 to deal with parameters of an indeterminate structure