[MS SQL] XML資料型態

之前寫過一篇 MS SQL 利用Trigger紀錄Table異動資料，
雖然可以記錄每次異動的資料，但缺點就是每個table欄位不同，
就要將每個要記錄的table分開新建tablex來記錄，感覺似乎也不太適合大量使用，
後來有用到XML STUFF，發現XML格式也可適用此問題，來達到記錄異動資料的需求，
好處就是不用考慮每個表格欄位數的多寡，一個欄位就可以達到記錄目的！

--建立測試表格
IF (select OBJECT_ID('tempdb..#TMP_XML')) IS NOT NULL DROP TABLE #TMP_XML
CREATE TABLE #TMP_XML (
    [ProdNo] [varchar](16) NOT NULL,
    [PnameD] [nvarchar](100) NULL,
    [InDay] [datetime] NULL,
    [UsrNo] [varchar](10) NULL) ON [PRIMARY]
   
--新增資料
INSERT INTO #TMP_XML VALUES ('A11','Google',CONVERT(VARCHAR,GETDATE(),120),'Dean')
INSERT INTO #TMP_XML VALUES ('B99',NULL,CONVERT(VARCHAR,GETDATE(),120),'Dean')

--XML 參數差異比較
-------------------------------------------------------------------------------------
select *
from #TMP_XML
FOR XML RAW('TMP')

/*--結果
<TMP ProdNo="A11" PnameD="Google" InDay="2014-12-30T17:11:30" UsrNo="Dean" />
<TMP ProdNo="B99" InDay="2014-12-30T17:11:30" UsrNo="Dean" />
*/
-------------------------------------------------------------------------------------
select *
from #TMP_XML
FOR XML RAW('TMP'), Elements

/*--結果
<TMP>
  <ProdNo>A11</ProdNo>
  <PnameD>Google</PnameD>
  <InDay>2014-12-30T17:11:30</InDay>
  <UsrNo>Dean</UsrNo>
</TMP>
<TMP>
  <ProdNo>B99</ProdNo>
  <InDay>2014-12-30T17:11:30</InDay>
  <UsrNo>Dean</UsrNo>
</TMP>
*/
-------------------------------------------------------------------------------------
select *
from #TMP_XML
FOR XML AUTO

/*--結果
<_x0023_TMP_XML ProdNo="A11" PnameD="Google" InDay="2014-12-30T17:11:30" UsrNo="Dean" />
<_x0023_TMP_XML ProdNo="B99" InDay="2014-12-30T17:11:30" UsrNo="Dean" />
*/
-------------------------------------------------------------------------------------
select *
from #TMP_XML
FOR XML AUTO, Elements

/*--結果
<_x0023_TMP_XML>
  <ProdNo>A11</ProdNo>
  <PnameD>Google</PnameD>
  <InDay>2014-12-30T17:11:30</InDay>
  <UsrNo>Dean</UsrNo>
</_x0023_TMP_XML>
<_x0023_TMP_XML>
  <ProdNo>B99</ProdNo>
  <InDay>2014-12-30T17:11:30</InDay>
  <UsrNo>Dean</UsrNo>
</_x0023_TMP_XML>
*/
-------------------------------------------------------------------------------------

--==注意==--

1. 如果欄位為NULL，則不會紀錄
2. 跨伺服器會出現"分散式查詢不支援 XML 資料類型"

參考自
黃昏的甘蔗-[SQL] 將 Table 資料匯出成 XML
軟體開發的天空-使用 Trigger 紀錄資料表的新增、修改、刪除的行為
MSDN-xml (Transact-SQL)