InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 09:45:30 GMT20http://www.sqlservercentral.com/Forums/Topic1382462-392-1.aspxLIVING AND LEARNING!!!!!!! :-DThu, 08 Nov 2012 09:26:26 GMTcarlosaamaralhttp://www.sqlservercentral.com/Forums/Topic1382462-392-1.aspxUse CROSS APPLY[code="sql"]DECLARE @xml XML ='<ROOT><SPECIALNEEDS> INCLUDE <NEED> BLIND </NEED> <NEED> BRAILLE AUDIO </NEED></SPECIALNEEDS></ROOT>'SELECT @xml AS xmlColINTO #temptableSELECT TD.D.value('./text()[1]','Varchar(400)') FROM #temptableCROSS APPLY xmlCol.nodes('/ROOT/SPECIALNEEDS') AS TD(D)[/code]Thu, 08 Nov 2012 08:41:24 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1382462-392-1.aspxThanks Eugene, That was really helpful.Just because I am anal about these things ;-)How would you write the select statement if the data was in a #temptable with a column of type XMLThu, 08 Nov 2012 07:58:09 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1382462-392-1.aspx[code="sql"]DECLARE @xml XML ='<ROOT><SPECIALNEEDS> INCLUDE <NEED> BLIND </NEED> <NEED> BRAILLE AUDIO </NEED></SPECIALNEEDS></ROOT>'SELECT TD.D.value('./text()[1]','Varchar(400)') FROM @xml.nodes('/ROOT/SPECIALNEEDS') AS TD(D)[/code]Please note the whitespace preserved on the left of INCLUDE, as it's part of the node text. You can LTRIM it or make sure you XML formed appropriately eg:[code="sql"]'<ROOT><SPECIALNEEDS>INCLUDE <NEED> BLIND </NEED> <NEED> BRAILLE AUDIO </NEED></SPECIALNEEDS></ROOT>'[/code]Thu, 08 Nov 2012 06:44:59 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1382462-392-1.aspxHi Guys,If I have the following XML[code="sql"]<ROOT><SPECIALNEEDS> INCLUDE <NEED> BLIND </NEED> <NEED> BRAILLE AUDIO </NEED></SPECIALNEEDS></ROOT>[/code]How do I get the value "INCLUDE" only.xmltext.value('(ROOT/SPECIALNEEDS)[1]', nvarchar(max)) is giving me all the value contents (Include Blind Braille Audio) Thu, 08 Nov 2012 06:36:48 GMTaaron.reese