来自选择查询的通用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')更多推荐
发布评论