將以下xml如何決議成value一行顯示呢?
declare @xml xml
select @xml='<event name="sql_batch_completed" package="sqlserver" timestamp="2020-07-30T03:02:57.197Z">
<data name="cpu_time">
<value>0</value>
</data>
<data name="duration">
<value>999</value>
</data>
<data name="physical_reads">
<value>2</value>
</data>
<data name="logical_reads">
<value>3</value>
</data>
<data name="writes">
<value>4</value>
</data>
<data name="spills">
<value>5</value>
</data>
<data name="row_count">
<value>6</value>
</data>
<data name="result">
<value>7</value>
<text>OK</text>
</data>
<data name="batch_text">
<value>use [master]</value>
</data>
<action name="transaction_sequence" package="sqlserver">
<value>8</value>
</action>
<action name="transaction_id" package="sqlserver">
<value>9</value>
</action>
</event>'
uj5u.com熱心網友回復：
SELECT STUFF(
(SELECT ','+ xmlValue FROM (
SELECT
T.C.value('value[1]','varchar(max)') AS xmlValue
FROM @xml.nodes('/event/data') AS T(C)
) t FOR XML PATH('')),1,1,'') AS newValue
結果:
newValue
--------------------------------------------------------------------------------------
0,999,2,3,4,5,6,7,use [master]
(1 行受影響)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/14516.html
標籤：基礎類
上一篇：DTExecUI配置問題
下一篇：SQL 列分成小列