从SQL XML列中的元素获取属性名称 [英] Get the names of attributes from an element in a SQL XML column

问题描述

对于此xml(在SQL 2005 XML列中):

For this xml (in a SQL 2005 XML column):

<doc>
 <a>1</a>
 <b ba="1" bb="2" bc="3" />
 <c bd="3"/>
<doc>

我希望能够检索SQL Server 2005内的属性名称(ba，bb，bc，bd)而不是值.好吧，XPath当然可以使用name()来允许它，但是SQL不支持它.这是我在SQL中使用XML的主要抱怨.您必须弄清楚其中包含XML/Xpath/XQuery规范的哪些部分.

I'd like to be able to retrieve the names of the attributes (ba, bb, bc, bd) rather than the values inside SQL Server 2005. Well, XPath certainly allows this with name() but SQL doesn't support that. This is my chief complaint with using XML in SQL; you have to figure out which parts of the XML/Xpath/XQuery spec are in there.

我想到的唯一方法是构建一个CLR proc，该proc将XML加载到XML文档(iirc)中并运行XPath来提取节点的名称.我愿意在这里提出建议.

The only way I can think of to do this is to build a CLR proc that loads the XML into an XML Document (iirc) and runs the XPath to extract the names of the nodes. I'm open to suggestions here.

推荐答案

DECLARE @xml as xml
DECLARE @path as varchar(max)
DECLARE @index int, @count int

SET @xml = 
'<doc>
 <a>1</a>
 <b ba="1" bb="2" bc="3" />
 <c bd="3"/>
</doc>'



SELECT @index = 1

SET @count = @xml.query('count(/doc/b/@*)').value('.','int')

WHILE @index <= @count 
BEGIN
    SELECT  @xml.value('local-name((/doc/b/@*[sql:variable("@index")])[1])', 'varchar(max)')
    SET @index = @index + 1
END

对于元素"b"

返回

• ba
• bb
• bc

您可以构建一个循环来获取xml中每个元素的属性.

You can build a loop to get attributes for each element in the xml.

顺便说一句样本中的XML应该在doc标记关闭时关闭.

BTW The XML in your sample should be closed at closing doc tag.

这篇关于从SQL XML列中的元素获取属性名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！