問題描述

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

這篇關于如何選擇XML中的所有列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！