Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL: How can I get the value of an attribute in XML datatype?

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?

like image 916
ThdK Avatar asked Jan 10 '12 18:01

ThdK


People also ask

How can I get SQL query results in XML?

You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in subqueries. The top-level FOR XML clause can be used only in the SELECT statement.

How do I get data from XML format in SQL Server?

SQL Server lets you retrieve data as XML by supporting the FOR XML clause, which can be included as part of your query. You can use the FOR XML clause in the main (outer) query as well as in subqueries. The clause supports numerous options that let you define the format of the XML data.

How do I query XML data?

Querying in an XQuery context If your query invokes an XQuery expression directly, you must prefix it with the case-insensitive keyword XQUERY. To retrieve all of the XML documents previously inserted into the INFO column, you can use XQuery with either db2-fn:xmlcolumn or db2-fn:sqlquery.

How do I include in XML attribute value?

An attribute should be declared using the attribute-list declaration in the DTD (Document Type Definition). An attribute element is used without any quotation and the attribute value is used in a single (' ') or double quotation (” “). An attribute name and its value should always appear in pair.


2 Answers

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 
like image 66
Kirill Polishchuk Avatar answered Sep 28 '22 00:09

Kirill Polishchuk


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) 
like image 44
Bassam Mehanni Avatar answered Sep 28 '22 02:09

Bassam Mehanni