How Can I Extract Attribute Values from XML Data Using SQL?

Obtaining Attribute Values from XML Data in SQL

When working with XML data in database tables, it may be necessary to retrieve the value of specific attributes within the XML content. One possible challenge involves extracting the value of an attribute when the data is stored as an XML datatype.

XML Data Format

Consider the following XML snippet stored in a database:

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

Querying the Attribute Value

To retrieve the value of the "language" attribute, you can utilize XQuery within your SQL statement. XQuery is an XML query language specifically designed for querying and processing XML data.

XQuery Expression

The following XQuery expression retrieves the value of the "language" attribute from the XML data:

/email/account/@language

SQL Query

Incorporating the XQuery expression into your SQL query, you can obtain the attribute value as follows:

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

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

Additional Examples

Suppose you have a table named "@t" containing multiple XML rows:

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

To extract the attribute values from each row, you can execute the following query:

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

Output

The output of the above query will be a list of attribute values:

en
fr

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