Handling xml in sql /converting xml to varchar

--------+-------------------------------------------------------
int     |xml
--------+-------------------------------------------------------
2       |<items>
        |    <item>
        |         <description>item 21 </description>
        |    </item>
        |    <item>
        |      <description>item 22</description>
        |    </item>
        |</items>
--------+-------------------------------------------------------
3       |<items>
        |    <item>
        |         <description>item 31 </description>
        |    </item>
        |<开发者_运维知识库/items>
--------+-------------------------------------------------------
4       |<items>
        |    <item>
        |         <description>item 31 </description>
        |    </item>
        |    <item>
        |      <description>item 32</description>
        |    </item>
        |    <item>
        |      <description>item 33</description>
        |    </item>
        |</items>
--------+-------------------------------------------------------

I have a table as in the above structure and type, I have to convert it to the structure and type as below

--------+-------------------------------------------------------
int     |varhcar
--------+-------------------------------------------------------
2       |     item 21 
--------+-------------------------------------------------------  
2       |     item 22  
--------+-------------------------------------------------------
3       |     item 31   
--------+-------------------------------------------------------
4       |     item 41  
--------+-------------------------------------------------------
4       |     item 42   
--------+-------------------------------------------------------
4       |     item 43
--------+-------------------------------------------------------

Any help is appreciated plz

Try this:

SELECT
    ID, 
    Item.value('(description)[1]', 'varchar(50)') AS 'DescText'
FROM 
    dbo.YourTable
CROSS APPLY 
    xml.nodes('/items/item') AS Node(Item)

That should select the ID's (or int) from your table, and grab all the contents of the <description> nodes from the XML.