Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XQuery: Get the value only, and not the child node values Expand / Collapse
Author
Message
Posted Thursday, November 8, 2012 6:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:26 PM
Points: 386, Visits: 622
Hi Guys,

If I have the following XML

<ROOT>
<SPECIALNEEDS>
INCLUDE
<NEED>
BLIND
</NEED>
<NEED>
BRAILLE AUDIO
</NEED>
</SPECIALNEEDS>
</ROOT>

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)

Post #1382462
Posted Thursday, November 8, 2012 6:44 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062

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)


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:

'<ROOT>
<SPECIALNEEDS>INCLUDE
<NEED>
BLIND
</NEED>
<NEED>
BRAILLE AUDIO
</NEED>
</SPECIALNEEDS>
</ROOT>'



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1382465
Posted Thursday, November 8, 2012 7:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:26 PM
Points: 386, Visits: 622
Thanks 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 XML

Post #1382510
Posted Thursday, November 8, 2012 8:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
Use CROSS APPLY


DECLARE @xml XML =
'<ROOT>
<SPECIALNEEDS>
INCLUDE
<NEED>
BLIND
</NEED>
<NEED>
BRAILLE AUDIO
</NEED>
</SPECIALNEEDS>
</ROOT>'

SELECT @xml AS xmlCol
INTO #temptable

SELECT TD.D.value('./text()[1]','Varchar(400)')
FROM #temptable
CROSS APPLY xmlCol.nodes('/ROOT/SPECIALNEEDS') AS TD(D)



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1382535
Posted Thursday, November 8, 2012 9:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2014 6:57 AM
Points: 29, Visits: 989
LIVING AND LEARNING!!!!!!!
Post #1382574
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse