SQL: How can I get the value of an attribute in XML datatype?
Use XQuery:
declare @xml xml ='<email> <account language="en" /></email>'select @xml.value('(/email/account/@language)[1]', 'nvarchar(max)')declare @t table (m xml)insert @t values ('<email><account language="en" /></email>'), ('<email><account language="fr" /></email>')select m.value('(/email/account/@language)[1]', 'nvarchar(max)')from @tOutput:
enfr
This should work:
DECLARE @xml XMLSET @xml = N'<email><account language="en" /></email>'SELECT T.C.value('@language', 'nvarchar(100)')FROM @xml.nodes('email/account') T(C)
It depends a lot on how you're querying the document. You can do this, though:
CREATE TABLE #example ( document NText);INSERT INTO #example (document)SELECT N'<email><account language="en" /></email>';WITH XmlExample AS ( SELECT CONVERT(XML, document) doc FROM #example)SELECT C.value('@language', 'VarChar(2)') langFROM XmlExample CROSS APPLY XmlExample.doc.nodes('//account') X(C);DROP TABLE #example;EDIT after changes to your question.