来自选择查询的通用XML(generic XML from select query)

来自选择查询的通用XML(generic XML from select query)

有没有办法从“select * from ANY_TABLE”查询中获取此XML？

<root> <row> <column name="id" value="321"> <column name="name" value="Book"> </row> <row> <column name="id" value="654"> <column name="name" value="Pen"> </row> </root>

Is there a way to get this XML from "select * from ANY_TABLE" query?

<root> <row> <column name="id" value="321"> <column name="name" value="Book"> </row> <row> <column name="id" value="654"> <column name="name" value="Pen"> </row> </root>

最满意答案

这是一种获得你想要的结果的方法。

declare @T table(id int, name varchar(20)) insert into @T values(321, 'Book') insert into @T values(654, 'Pen') select ( select 'id' as '@name', T.id as '@value' for xml path('column'), type ), ( select 'name' as '@name', T.name as '@value' for xml path('column'), type ) from @T as T for xml path('row'), root('root')

如果你想让它适用于任何使用select *表，你可以使用它来代替。 只需将@T替换为你的表名即可。 注意：这只有在列名是有效的XML元素名称时才有效。

select ( select T3.N.value('local-name(.)', 'sysname') as '@name', T3.N.value('.', 'nvarchar(max)') as '@value' from ( select T1.* for xml path(''), type ) T2(N) cross apply T2.N.nodes('*') as T3(N) for xml path('column'), root('row'), type ) from @T as T1 for xml path(''), root('root')

Here is a way to get the result you want.

declare @T table(id int, name varchar(20)) insert into @T values(321, 'Book') insert into @T values(654, 'Pen') select ( select 'id' as '@name', T.id as '@value' for xml path('column'), type ), ( select 'name' as '@name', T.name as '@value' for xml path('column'), type ) from @T as T for xml path('row'), root('root')

If you want this to work for any table using select * you can use this instead. Just replace @T with whatever your table name is. Note: this will only work if the column names are valid XML element names.

select ( select T3.N.value('local-name(.)', 'sysname') as '@name', T3.N.value('.', 'nvarchar(max)') as '@value' from ( select T1.* for xml path(''), type ) T2(N) cross apply T2.N.nodes('*') as T3(N) for xml path('column'), root('row'), type ) from @T as T1 for xml path(''), root('root')