利用XML转换为table实现在SQL参数中传递表结构
SQL 2005中，通常会用到在SQL参数中传递表结构，最简单的办法是利用XML转换为table
代码DECLARE @h INT,
@XML VARCHAR(8000),
@2k5 XML
SELECT @XML = '
<jrt>
<item>
<id>11</id>
<name>CS Tester</name>
<company>EEE</company>
<phone>555-555-1234</phone>
</item>
<item>
<id>22</id>
<name>CS Tester</name>
<company>EEE</company>
<phone>555-555-1234</phone>
</item>
</jrt>
',
@2k5 = @XML
SELECT T.c.query('id').value('.[1]', 'varchar(100)') as id,
T.c.query('name').value('.[1]', 'varchar(50)') as name,
T.c.query('company').value('.[1]', 'varchar(50)') as company,
T.c.query('phone').value('.[1]', 'nvarchar(50)') as phone
FROM @2k5.nodes('/jrt/item') AS T(c)
@XML VARCHAR(8000),
@2k5 XML
SELECT @XML = '
<jrt>
<item>
<id>11</id>
<name>CS Tester</name>
<company>EEE</company>
<phone>555-555-1234</phone>
</item>
<item>
<id>22</id>
<name>CS Tester</name>
<company>EEE</company>
<phone>555-555-1234</phone>
</item>
</jrt>
',
@2k5 = @XML
SELECT T.c.query('id').value('.[1]', 'varchar(100)') as id,
T.c.query('name').value('.[1]', 'varchar(50)') as name,
T.c.query('company').value('.[1]', 'varchar(50)') as company,
T.c.query('phone').value('.[1]', 'nvarchar(50)') as phone
FROM @2k5.nodes('/jrt/item') AS T(c)
结果是：
id name company phone
---------- ---------- ---------- ----------
11 CS Tester EEE 555-555-12
22 CS Tester EEE 555-555-12
