利用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)

 结果是：

id         name       company    phone
---------- ---------- ---------- ----------
11         CS Tester  EEE        555-555-12
22         CS Tester  EEE        555-555-12