在不使用SQL Server查询中的节点名称的情况下读取XML节点属性 [英] Read XML node attributes without using node name in SQL Server query

问题描述

我将具有不同标签名称但属性名称相同的XML存储到数据库中:

I have XML stored into database with the different tag names but the same attribute names:

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

这只是两个示例，但是标签名称可以是任何东西.我想从所有节点读取"PropertyName"属性.

These are just two examples but the tag name can be anything. I want to read "PropertyName" attribute from all node.

有可能吗?如果是，那么请任何人指导我.

Is it possible? If yes then please anyone guide me.

推荐答案

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)

这篇关于在不使用SQL Server查询中的节点名称的情况下读取XML节点属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！