Menu
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?
916
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.
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.
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.
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.
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 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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
