How to Extract Attribute Values from XML Data using SQL Queries?

Retrieving Attribute Values from XML Data in SQL

When working with XML data in SQL databases, it's often necessary to extract specific attribute values. For instance, consider the following XML document stored in a table:

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

To retrieve the value of the language attribute, we can use an XQuery expression in the SQL query. XQuery provides a powerful way to navigate and extract information from XML documents.

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

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

This query uses the value() function to evaluate the XQuery expression and return the node value corresponding to the specified XPath expression. In this case, the XPath expression (/email/account/@language)[1] selects the first language attribute value under the account element in the XML document.

Alternatively, if the XML data is stored in a table as shown:

CREATE TABLE @t (m XML)
INSERT INTO @t VALUES
    ('<email><account language="en" /></email>'),
    ('<email><account language="fr" /></email>')

We can retrieve the attribute values using a correlated subquery as follows:

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

Using these techniques, we can effectively retrieve attribute values from XML data in our SQL queries.

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