selecting from sql

<suggestions>
 <suggestion>
  <description>plate.</description> 
 </suggestion>
 <suggestion>
 <description>snack.</description> 
 </suggestion>
</suggestions>

DECLARE @Suggestions TABLE (
     [Description] VARCHAR(800)
   )

I have above xml in a XML type varible in my stored procedure how can i insert each text between 开发者_开发技巧description tags in to the table @Suggestions

---

Try this:

DECLARE @input XML = '<suggestions>
 <suggestion>
  <description>plate.</description> 
 </suggestion>
 <suggestion>
 <description>snack.</description> 
 </suggestion>
</suggestions>'

DECLARE @Suggestions TABLE ([Description] VARCHAR(800))

INSERT INTO @Suggestions
    SELECT
        Sugg.value('(description)[1]', 'varchar(800)')
    FROM
        @input.nodes('/suggestions/suggestion') AS Tbl(Sugg)

SELECT * FROM @Suggestions

The @input.nodes() expression basically turns each <suggestion> node into a row in the "pseudo" table called Tbl(Sugg). From those "rows" (each basically another XML fragment), you then select out the value you're interested in - here the contents of the <description> XML element inside that XML fragment.

---

You can use LINQ-to-XML to get all suggestions, then you can insert that data into the table.