Working with a sequence of attributes in XQuery is fairly straight forward with the “attribute” axis even if you don’t know or need to know the attribute names. By using this axis you can access all of the attributes within the given context node. The specific scenario I had was to find a particular duplicated node within the xml that had no attributes and delete it thus keeping the other node that I knew had attributes.

In this particular case i used the count() function as part of the predicate of an XML delete statement but getting a sequence of attributes could be useful in a variety of other use cases.

For example, the following is a contrived example of an XML structure and a query that shreds the XML and for each <SomeElement> node, counts the number attributes withint that node.

DECLARE @xml XML
SET @xml = '
<Data>
<SomeElement This="123" That="456">This element has attributes</SomeElement>
<SomeElement>This element does not</SomeElement>
<SomeElement TheOther="7890">This element has one</SomeElement>
</Data>
'
SELECT t.c.query('.') AS 'SomeElement'
, t.c.value('count(attribute::*)', 'int') AS 'CountOfAttributes'
FROM @xml.nodes('/Data/SomeElement') t(c)

Returns:

To access the attributes axis all you need to use is “attribute::” as part of the expression. I used “*” to denote all attributes within the context node. So by passing in all the attributes to the count() function, we can easily count them.

With this you can easily add predicates on the attributes to filter by the attribute name (local-name() function) or value (data() function) and is particularly useful if for whatever reason you may not know all of the attributes names.

To take it one step further, the following example takes the XML in the previous query and by using a FLWOR statement, returns all of the attributes in the entire XML as elements within a new XML structure:

SELECT @xml.query('
<AllAttributes>
{
for $x in //attribute::* return
<Attribute>
<Name>{local-name($x)}</Name>
<Value>{data($x)}</Value>
</Attribute>
}
</AllAttributes>
')

Returns:

Enjoy!

Follow me on twitter @sqlserverrocks

Subscribe to my blog RSS feed

Comment on this or any of my posts or contact me directly from http://www.olcot.co.uk