Deserialize XML data object in T-SQL [duplicate]

问题:

Possible Duplicate:
Deserialize XML object in T-SQL

I got an XML object, and i want to deserialize him into a table using T-SQL.

<Params>
    <paramtype type = '1'>
        <value> abc</value>
    </paramtype>

    <paramtype type = '2'>
        <value> 123</value>
    </paramtype>
</Params>

How can i store all this data into a table like this:

回答1:

You can get the values like this

select  
    x.v.value('@type','int') as [type],
    x.v.value('.','varchar(50)') as [value]
from    
    @x.nodes('/Params/paramtype') x(v)

where @x is your XML object.

and insert them into a table with an identity for the ID (or use row_number() to generate one)