No problem.

I did spend a few minutes today and came up with the following rather simplistic/crude script which may offer some kind of option for you. I don't really like this personally and it hasn't been tested at all (other than the one example) so feel free to do with it as you wish. I don't know how it will handle large or deep xml structures either. It does rely on another function called XMLTable which is blogged here: http://beyondrelational.com/modules/2/blogs/28/posts/10495/xquery-lab-58-select-from-xml.aspx which you'll need first.

The idea of this script is that it breaks down the xml to a flat table and then reconstructs the xml back again whilst adding in an new attribute into every node.

DECLARE @xml XML

SET @xml = '<books>

<book>

<author>

<name>aaaaa</name>

<age>12</age>

<title>mr</title>

</author>

</book>

</books>'

--declare variable to hold column information and set to empty string

DECLARE @cols VARCHAR(MAX)

SET @cols = ''

--create string of all columns in the xml and set the column alias to the fullpath in the xml

--add in the new attribute as part of the columns

--use the xmltable function from Jacob Sebastian

SELECT @cols = @cols + '''1'' as ''' + FullPath + '/@new_attribute'', ''' + ISNULL(Value, '') + ''' as ''' + FullPath + ''','

FROM dbo.XMLTable(@xml)

--declare sql variable and construct the dynamic sql statement with the columns

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'select ' + LEFT(@cols, LEN(@cols) - 1) + ' FOR XML PATH('''')'

--execute

EXEC sys.sp_executesql @SQL