47

I have the following xml in my database:

<email>
  <account language="en" ... />
</email>

I am using something like this now: but still have to find the attribute value.

 SELECT convert(xml,m.Body).query('/Email/Account')
 FROM Mail

How can I get the value of the language attribute in my select statement with SQL?

Kirill Polishchuk
  • 54,804
  • 11
  • 122
  • 125
ThdK
  • 9,916
  • 23
  • 74
  • 101

4 Answers4

80

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 @t

Output:

en
fr
Kirill Polishchuk
  • 54,804
  • 11
  • 122
  • 125
  • 1
    this worked perfect! select @xml.value('(/email/account/@language)[1]', 'nvarchar(max)') THANK YOU! – ThdK Jan 10 '12 at 18:56
  • @KirillPolishchuk What if a root element contains an `xmlns` attribute? Say, ` – Interface Unknown Aug 07 '16 at 20:41
  • 2
    @InterfaceUnknown, this is called "Default namespace". Something like this: set namespace before `select` command like this: `WITH XMLNAMESPACES ('http://domain.com/ws/2016-08-07' as x )`. Then you can add reference to this namespace in the select query: `'(/x:email/x:account/@language)[1]'` – Kirill Polishchuk Aug 08 '16 at 00:08
16

This should work:

DECLARE @xml XML

SET @xml = N'<email><account language="en" /></email>'

SELECT T.C.value('@language', 'nvarchar(100)')
FROM @xml.nodes('email/account') T(C)
Bassam Mehanni
  • 14,796
  • 2
  • 33
  • 41
4

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)') lang
FROM XmlExample CROSS APPLY
     XmlExample.doc.nodes('//account') X(C);

DROP TABLE #example;

EDIT after changes to your question.

Yuck
  • 49,664
  • 13
  • 105
  • 135
  • I've edited my question. the xml is saved as ntext in the database. I convert it first to xml type. – ThdK Jan 10 '12 at 18:51
  • See my edits, using `ntext` input and a table approach with CTE to transform to `xml`. – Yuck Jan 10 '12 at 18:56
2

if the xml data is stored in sql server as a string column then use cast

select cast(your_field as XML)
       .value('(/email/account/@language)[1]', 'varchar(20)') 
from your_table
Golden Lion
  • 3,840
  • 2
  • 26
  • 35