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)
2 Responses to “SQL XML, select from xml using nodes() function”
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.
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