SQL XML (Re: SQL cursors: This was a funny one)

Just so that I have said it: Don’t come here for XQuery expert advice, I don’t understand any part of XML. I just copy other people. Still, I am pretty good at copying. I got a reply on my post “SQL cursor: This was a funny one” that was so nice, I think it deserves a new post:

--------------------------------------------
Michael Rys:

You mean you could write the following simple expression with XML:

SELECT (SELECT * FROM Customers FOR XML AUTO, ELEMENTS, TYPE).query(
'for $c in /Customers
return
<Customers>{
for $p in $c/*
order by local-name($p)
return $p
}</Customers>'
)

:-)
eoMichael Rys:
--------------------------------------------

USE AdventureWorks
GO

CREATE PROCEDURE pXmlAllFieldsSorted(@tableName nvarchar(MAX))
AS
DECLARE @sql AS nvarchar(MAX)
SET @sql = 'SELECT (SELECT * FROM ' + @tableName + ' FOR XML AUTO, ELEMENTS, TYPE)' +
'.query(''for $c in /' + @tableName + ' return <' + @tableName +
'>{ for $p in $c/* order by local-name($p) return $p' +
'}</' + @tableName + '>'')'

PRINT @sql
EXEC (@sql)
GO

pXmlAllFieldsSorted 'HumanResources.Employee'