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)

实例二：

--declare @Words XML
 --set @Words = N'<StringList><Item>A</Item><Item>B</Item></StringList>'
 declare @InputWord table(Word nvarchar(255),Term nvarchar(255))
 insert into @InputWord
 select T.Word.value('.', 'nvarchar(255)') --当前节点
    ,dbo.ufn_GetOptimalMatch(T.Word.value('.', 'nvarchar(255)'))
 from @Words.nodes('//Item') T(Word)