Get item without subitems in SQL Server

Question

I am using SQL Server 2008, and I want to get the XML elements and return the element itself and its attributes without any sub-elements and without text (). For example, the following XML has 4 nodes (doc, apple, b, banana):

<doc> <apple type="bramley"> tasty <b>yum!</b> </apple> <banana color="yellow" shape="bendy"> nice in smoothies </banana> </doc> 

I would like to return:

 <doc/> <apple type="bramley"/> <b/> <banana color="yellow" shape="bendy"/> 

For example, doc should be returned without any sub-nodes, and apple should be returned without b under node. But the problem is that if I use the SQL Server nodes and the query method, I cannot delete the sub-nodes. Using SQL Server 2008, I have the closest:

 declare @x xml = '<doc> <apple type="bramley"> tasty <b>yum!</b> </apple> <banana color="yellow" shape="bendy"> nice in smoothies </banana> </doc>'; select c.query('local-name(.)') as Node, c.query('for $e in . return <xx> {$e/@*} </xx>') as Attr from @x.nodes('//*') as T(c); 

This gets the name of each node (using the local name) and the node attributes and returns:

 Node Attr ---- ---- doc <xx /> apple <xx type="bramley" /> b <xx /> banana <xx color="yellow" shape="bendy" /> 

I understand that I can process this result, convert Attr to varchar, replace xx with the node column and convert back to XML. But is there an easier way without string manipulation?

PS: If this helps, I don't mind if the solution uses SQL Server 2008 or SQL Server 2012.

Answer 1

Normally, you should use element constructs with dynamic tag names, but SQL Server does not support this:

 declare @x xml = '<doc> <apple type="bramley"> tasty <b>yum!</b> </apple> <banana color="yellow" shape="bendy"> nice in smoothies </banana> </doc>'; select c.query('local-name(.)') as Node, c.query('for $e in . return element { local-name($e) } { $e/@* } </xx>') as Attr from @x.nodes('//*') as T(c); 

---

As an alternative to XQuery Update (tested with SQL Server 2012), we can get all the nodes (with all the contents) and delete their notes.

 DECLARE @x xml = '<doc>test <apple type="bramley"> tasty <b>yum!</b> </apple> <banana color="yellow" shape="bendy"> nice in smoothies </banana> </doc>'; -- Fetch all nodes SET @x = @x.query('//*') -- Delete all subnodes SET @x.modify('delete /*/node()') select c.query('.') as NewNode from @x.nodes('/*') as T(c);