The goal here is to extract an attribute value from a SQL Server 2008 table column. The column is an XML data type, of course, named 'ProductName'. Here's the sample data
<locale en-US="My Text" />
And here's my latest attempt at getting to 'My Text'
SELECT ProductName.value('/@locale en-US', 'nvarchar(max)')
AS ProductName FROM MyTable
Thanks and good luck!
SELECT ProductName.value('(/locale/@en-US)[1]', 'nvarchar(max)') AS ProductName
FROM MyTable
Try this:
SELECT
ProductName.value('(/locale/@en-US)[1]', 'varchar(50)')
FROM
dbo.MyTable
The /locale part of the XPath expression matches the <locale> element, while the @en-US part matches the en-US attribute of that XML element.
declare @xml xml =
'<root>
<locale en-US="My Text" />
</root>'
select @xml.value('(/root/locale/@en-US)[1]', 'nvarchar(max)')
Result
My Text
