Return attribute nodes from multiple element nodes using SQL 2008 and Xquery
Posted on 2011-03-17
I have a table in SQL 2008 which contains one column as an xml data type.
This table has thousands of rows and for that xml column the xml data in that field would have a stucture like follows:
<client_info first_name="John" last_name="Doe">
<cs date="09/01/2010" />
<cs date="10/01/2010" />
<teacher date="08/06/2010" />
<teacher date="10/18/2010" />
How can I return, preferably as a single XML datagram, a document that has the value of every date attribute and the local-name of it's parent node bearing in mind this is just a simple XML example and there can and will be more levels to this document and it will need to be processed for each row on the table ideally returning a single XML datagram.
Any SQL/XML experts who can share a solution I would greatly appreciate it.