利用XML转换为table实现在SQL参数中传递表结构

DECLARE    @h INT,    @XML VARCHAR(8000),    @2k5 XMLSELECT    @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 = @XMLSELECT    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 phoneFROM    @2k5.nodes('/jrt/item') AS T(c)

 

 

 结果是：

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

 

 

 

 

 

 

======================================

单列表

 

 

DECLARE @XMLDocument XML SELECT @XMLDocument='<Root><UserFlag>1002</UserFlag><UserFlag>1003</UserFlag><UserFlag>1005</UserFlag></Root>'

select   T.c.value('.[1]', 'int') as num from  @XMLDocument.nodes('/Root/UserFlag') as T(c)