SQL: How can I get the value of an attribute in XML datatype?
93,224
Solution 1
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
Solution 2
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)
Solution 3
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.
Solution 4
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
Comments
-
ThdK almost 2 years
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?