SQL Server Get the names of attributes from an element in a SQL XML column

n3ipq98p 于 2023-10-15 发布在其他

关注(0)|答案(4)|浏览(117)

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

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

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.

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.

sql-server

来源：https://stackoverflow.com/questions/241407/get-the-names-of-attributes-from-an-element-in-a-sql-xml-column

4条答案

按热度按时间

1sbrub3j1#

DECLARE @xml as xml

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

SELECT DISTINCT
 CAST(Attribute.Name.query('local-name(.)') AS VARCHAR(100)) Attribute,
 Attribute.Name.value('.','VARCHAR(100)') Value
FROM @xml.nodes('//@*') Attribute(Name)

Returns:

Attribute Value

ba 1

bb 2

bc 3

bd 3

赞(0）回复(0）举报 2023-10-15

h79rfbju2#

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

for element 'b'

it returns

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

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

赞(0）回复(0）举报 2023-10-15

igetnqfo3#

this:

declare @xml as xml

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

select @xml.query('
    for $attr in /doc/b/@*
    return local-name($attr)')

returns:

ba bb bc

赞(0）回复(0）举报 2023-10-15

cunj1qz14#

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

Select n.value('local-name(.)', 'varchar(max)')  from @xml.nodes('/doc/*/@*') a(n)

Returns ba bb bc bd

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Get the names of attributes from an element in a SQL XML column

4条答案

相关问题

热门标签

最新问答