Select XML Nodes as Attributes

Question

I assume that the answer to my question will be so simple that I cannot understand myself. Here is the scenario:

I am using SQL Server 2008 R2, where the table has an XML column, where the data is saved in the following format:

<Person> <firstName>John</firstName> <lastName>Lewis</lastName> </Person> 

Person node can have any number of child nodes for which element names can be different (unknown in advance). I am looking for a query to return XML that has values ​​for all nodes as attributes.

So the output for the above XML should be:

 <Person firstName="John" lastName="Lewis"/> 

I cannot come up with a query to get the above output. I do not want to use a query like

 Select PersonColumn.value('(/Person/firstName)[1]', 'varchar(100)') AS '@firstName' , PersonColumn.value('(/Person/lastName)[1]', 'varchar(100)') AS '@lastName' FROM MyTable WHERE MyTable.MyPrimaryKey=1 FOR XML PATH('Person'), TYPE 

since I don’t know which nodes can be under Person node.

Answer 1

I tried to do it

 select PersonColumn.query(' element Person { for $i in /Person/* return attribute {local-name($i)} {string($i)} } ') from MyTable 

but it turns out that you cannot use dynamic attribute names

 XQuery [MyTable.PersonColumn.query()]: Only constant expressions are supported for the name expression of computed element and attribute constructors.: select PersonColumn.query(' element Person { for $i in /Person/* return attribute {local-name($i)} {string($i)} } ') from MyTable 

So the best I can do so far

 select cast( '<Person ' + ( select PersonColumn.query(' for $i in /Person/* return concat(local-name($i), "=""", data($i), """") ').value('.', 'nvarchar(max)') for xml path('') ) + '/>' as xml) from MyTable 

It is also possible to do

 select cast( '<Person ' + PersonColumn.query(' for $i in /Person/* return concat(local-name($i), "=""", data($i), """") ').value('.', 'nvarchar(max)') + '/>' as xml) from MyTable 

but it will not work if your data contains some characters, such as < > , etc.

Answer 2

It looks like you essentially want a PIVOT on behalf of the elements, which are direct children of the Person node. If you must do all this in TSQL, you must build the query dynamically. See How to include XML column attributes in T-SQL for inspiration. In this case, the rotation expression is also the attribute value, while in your case it is the name of the element.

I feel obligated to point out the obvious - that such a decision is likely to be poorly executed, the results will be difficult to consume (because the column names and their number are unknown) and may be susceptible to attacks using sql injections depending on the context.