Handling xml in sql /converting xml to varchar

Question

--------+-------------------------------------------------------
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

marc_s · Accepted Answer · 2011-04-26 05:17:24Z

1

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.

answered Apr 26, 2011 at 5:17

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Handling xml in sql /converting xml to varchar

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related