Retrieve an element without sub-elements in SQL Server

Question

I am using SQL Server 2008 and I want to retrieve XML elements and return the element itself and its attributes without any sub elements and without the text(). Eg 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"/>

Eg doc should be returned without any sub-nodes and apple should be returned without the b sub-node. But the problem is, if I use the SQL Server nodes and query method I can't remove the sub-nodes. Using SQL Server 2008 the closest I have is:

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 local-name) and the attributes of the node and returns:

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

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

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

Castello

Answer 1

Usually you would use element construction with dynamic tag names, but SQL Server doesn't 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 XQuery Update alternative (tested with SQL Server 2012), we can fetch all nodes (with all contents) and delete their subnotes.

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);

Hambley

OK I tried it in SQL Server but it doesn't like it - the error is Only constant expressions are supported for the name expression of computed element and attribute constructors. So I think local-name($e) is not allowed as an element constructor in SQL server (this makes the element constructor not very useful in SQL Server but that's another story). Are there any other variants of the XQuery I can test? – Castello

Seems to be a real problem. There is an answer to another question which is using string concatenation to build the XML - seems really, really ugly. "If this approach is offensive, you can stop reading now :)"... Better have a look on your problem from a higher level of view; what's your actual problem you're trying to solve? – Hambley

I added some lines to my answer, maybe SQL Server supports copy/modify. – Hambley

SQL Server supports modify but only as a method that can be applied to an XML variable or column. I tried your code above for $node in //* ... as part of the query but it reports syntax error near 'modify', expected 'where', 'order by' or 'return'. I think the SQL Server limit on what you can do in XQuery is FLWOR (for, let, where, order by, return). BTW I don't mind if the solution does modify the database in this situation. I am still holding out for a proper XQuery solution, as I still have the backup option of converting to strings and replacing xx by the real node name. – Castello

See my updated answer: Not exactly an elegant solution, but you can use XQuery Update to modify a local variable to delete all subnotes. Finally I fetched SQL Server from MSDN and it is working totally fine. :) It should be possible to use the original solution for rewriting the tag names using XQuery Update, too. – Hambley

Many thanks for testing it with SQL Server - I will give it a go. – Castello

I just tested it and that's perfect. The magic seems to be the delete /*/node() command which deletes all sub-nodes once @x is split into nodes. One minor detail, I don't need the separate Node and Attr columns and I only used these originally as I thought I would need string manipulation to do what I want. I will tweak the answer to reflect this and mark this as answered. Many thanks for your kind help. – Castello