How Can I Retrieve Attribute Values from XML Data in SQL?

Retrieving Attribute Values from XML Datatype in SQL

When working with XML data in SQL, it's often necessary to extract the values of specific attributes. For instance, we may need to obtain the value of the "language" attribute from the following XML:

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

To achieve this, we can leverage XQuery expressions. Here's a modified version of your SQL query using XQuery:

SELECT @xml.value('(/email/account/@language)[1]', 'nvarchar(max)') FROM Mail

In this query, we use the .value() function to query the XML document represented by the "@xml" variable. The XQuery expression "/email/account/@language" specifies the path to the desired attribute, and the "[1]" ensures that we retrieve the value of the first (and only) matching attribute.

Alternatively, you can use the following table expression to retrieve the attribute values:

declare @t table (m xml)

insert @t values 
    ('<email><account language="en"></account></email>'), 
    ('<email><account language="fr"></account></email>')

select m.value('(/email/account/@language)[1]', 'nvarchar(max)')
from @t

This approach provides a more concise and scalable solution for retrieving attribute values from multiple XML documents in a table. The output of the query will be:

en
fr

The above is the detailed content of How Can I Retrieve Attribute Values from XML Data in SQL?. For more information, please follow other related articles on the PHP Chinese website!