SQL XML, select from xml using nodes() function

By admin - Last updated: Thursday, October 1, 2009 - Save & Share - 2 Comments

Very handy function that helps me move small data around.

DECLARE @myXml AS XML

SET @myXml =
‘<ArrayOfProduct>
        <Product id="1">
                <Description>test1</Description>
        </Product>
        <Product id="2">
                <Description>test2</Description>
        </Product>     
</ArrayOfProduct>

SELECT
        T.c.VALUE(‘@id[1]’, ‘int’) AS [id]
        , T.c.VALUE(‘Description[1]’, ‘nvarchar(max)’) AS [description]
FROM @myXml.nodes(‘/ArrayOfProduct/Product’) AS T(c)




Posted in SQL • Tags: Top Of Page

2 Responses to “SQL XML, select from xml using nodes() function”

Comment from PokeADonkey
Time September 7, 2011 at 10:08 pm

Apart from the keyword “VALUE” should be in lowercase (otherwise the sample produces an error), I found this a very useful example. Many thanks 🙂

Comment from admin
Time September 7, 2011 at 10:30 pm

the syntax highlighter seems uppercase all the SQL keyword. Thanks for pointing it out.

Write a comment


Captcha: 7 × = twenty eight