如何选择XML中的所有列
DECLARE @x AS XML
SET @x = '<Table1><c1><![CDATA[1]]></c1><c2><![CDATA[Sample Record]]></c2><c3><![CDATA[Test Data]]></c3></Table1>'
SELECT * FROM @x.nodes('/Table1')
我想选择所有列而不定义列名(使用 *)
I want to select all columns without defining the column name (using *)
推荐答案
没有与 select * 等效的东西.最接近的是获取一列中的节点值和另一列中的节点名称.
There is no equivalent to select *. The closest you can get is to get the node values in one column and the node names in another column.
select T.X.value('local-name(.)', 'nvarchar(max)') as ColName,
T.X.value('text()[1]', 'nvarchar(max)') as ColValue
from @x.nodes('Table1/*') as T(X)
结果:
ColName ColValue
-------------------- --------------------
c1 1
c2 Sample Record
c3 Test Data
如果您希望节点名称作为输出中的列名称，您必须构造一个查询，指定要从中获取值的节点，并且您必须指定用于该列的列别名.
If you want the node names as column names in the output you have to construct a query that specifies the node to get the value from and you have to specify the column alias to use for that column.
select T.X.value('(c1/text())[1]', 'nvarchar(max)') as c1,
T.X.value('(c2/text())[1]', 'nvarchar(max)') as c2,
T.X.value('(c3/text())[1]', 'nvarchar(max)') as c3
from @x.nodes('Table1') as T(X)
c1 c2 c3
-------------------- -------------------- --------------------
1 Sample Record Test Data
可以使用 XML 作为源动态构建和执行该查询.
That query can be built and executed dynamically using the XML as the source.
declare @SQL nvarchar(max) =
'select '+stuff((select ',T.X.value(''('+C.Name+'/text())[1]'', ''nvarchar(max)'') as '+C.Name
from @x.nodes('Table1/*') as T(X)
cross apply (select T.X.value('local-name(.)', 'nvarchar(max)')) as C(Name)
for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 1, '')+
' from @x.nodes(''Table1'') as T(X)'
exec sp_executesql @SQL, N'@x xml', @x
相关文章