declare @xml xml

set @xml = '<book Category="Hobbies And Interests" PropertyName="C#" CategoryID="44" />
<sport Category="Hobbies And Interests" PropertyName="Cricket" CategoryID="46" />'

select T.c.value('@PropertyName', 'varchar(100)')
from @xml.nodes('/*') T(c)

如果您希望某些元素没有PropertyName属性，则可以使用:

If you expect that there can be elements without PropertyName attribute, you can use:

select T.c.value('@PropertyName', 'varchar(100)')
from @xml.nodes('/*[@PropertyName]') T(c)

如果您还希望元素可以嵌套，则可以使用:

If you also expect that elements can be nested, you can use:

select T.c.value('@PropertyName', 'varchar(100)')
from @xml.nodes('//*[@PropertyName]') T(c)