SQL Server XML應用(一)

XML在T-SQL中已定位為原生型別
就像int一樣，是由html沿伸出來的標準語言
在T-SQL中可以利用XSD 來制定XML裡元素的格式
避免資料誤值

XML的應用當廣泛，T-SQL支援將查詢結果
以XML的方式轉換輸出，使資料間傳輸更為容易

USE 練練
GO

--建立 XML SCHEMA COLLECTION 設定要放入XML裡的格式------------------------------
CREATE XML SCHEMA COLLECTION 員工履歷XML格式
AS
 '<xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="員工履歷">
    <xs:complexType>
    <xs:sequence>
   <xs:element name="員工編號" type="xs:string" minOccurs="1" maxOccurs="1"/>
   <xs:element name="姓名" type="xs:string" minOccurs="1" maxOccurs="1"/>
   <xs:element name="性別" type="xs:string" minOccurs="1" maxOccurs="1"/>
   <xs:element name="薪資" type="xs:decimal" minOccurs="1" maxOccurs="1"/>
    </xs:sequence>
   </xs:complexType>
  </xs:element>
 </xs:schema>'
GO

-----------------------------------------------------------------------------
--練習用建出來的table，資料的欄位是XML格式並且遵守上面XSD----------------------------
CREATE TABLE XML資料
(
 編號 INT,
 資料 XML(dbo.員工履歷XML格式)
)


-----------------------------------------------------------------------------
--只接受 Well-Form 的 XML
--標準格式編製才可INSERT進去
--有分大小寫，有首尾結點，單結點要有結束標計等等

INSERT INTO XML資料 VALUES(1,'<員工履歷><員工編號>1</員工編號><姓名>王小明</姓名><性別>男</性別><薪資>36000</薪資></員工履歷>')

--以下2筆放不進去，因為與格式不符--------------------------------------------------
INSERT INTO XML資料 VALUES(2,'<員工><員工編號>2</員工編號><姓名>李小英</姓名><薪資>35000</薪資></員工>')
INSERT INTO XML資料 VALUES(3,'<員工><編號>3</編號><姓名>林大雄</姓名><興趣>游泳、睡覺</興趣></員工>')

SELECT * FROM XML資料

再來與XML有關的取值

1. RAW：就是列取值，取出來的資料預設是屬性
   ROOT可加可不加，正常都會加
   如果想要用元素的方式表示，最後要加上ELEMENTS
2. AUTO：自動化，大至語法同上

以上二個取值比較制式，取出來客製化程度也較低

SELECT 產品編號,品名, CAST(價錢 AS MONEY) AS 價錢
FROM 巨巨
FOR XML RAW('產品'),
ROOT('產名資料'),ELEMENTS


SELECT 產品編號,品名, CAST(價錢 AS MONEY) AS 價錢
FROM 巨巨
FOR XML AUTO,
ROOT('產名資料'),ELEMENTS

接著是客製化程度比較高取值：
EXPLICIT(課堂上未提)

PATH
下面是用PATH的方式來呈現SELECT的結果
SELECT的結果後面用單引號包住
可以針對不同的資料給予不同的路徑
使閱讀起來更為容易

同時也可以針對某個欄位想讓它用屬性的方式展現
只要在最前面加上@即可

PS.PATH的預設狀態即有ELEMENTS

SELECT 產品編號 '@編號'            --編號會是屬性
      ,品名 '產品名'               --以產品名為子元素
      ,CAST(價錢 AS MONEY) '價錢' --以價錢為子元素
FROM 巨巨
FOR XML PATH('產品'),ROOT('產名資料')

-----------------------------------------------------------------------------
USE 中文北風
GO

SELECT 員工編號 '@編號'                    --屬性
      ,姓名 '個資/姓名'                    --會在「個資」下的姓名
      ,稱呼 '個資/性別'                    --會在「個資」下的性別
      ,CAST(雇用日期 AS DATE) '職務/到職日' --會在「職務」下的到職日
      ,職稱 '職務/職位'                    --會在「職務」下的職位
      ,薪資 '職務/薪資'                    --會在「職務」下的薪資
FROM 員工
FOR XML PATH('員工資料'),ROOT('員工資料表清單')


-----------------------------------------------------------------------------
--PATH槽狀XML查詢，子查詢的結果會在主查詢中成為一個元素，以列表方式展開-----------------
SELECT 客戶編號,公司名稱,連絡人,連絡人職稱,地址,
    (
  SELECT 客戶編號,訂單號碼,訂單日期,運費
  FROM 訂貨主檔 
  WHERE 客戶編號 = 客戶.客戶編號
  FOR XML RAW('訂單'),TYPE
    ) AS  相關訂單
FROM 客戶
FOR XML PATH('客戶資料'),ROOT('客戶訂單一覽')

-----------------------------------------------------------------------------
--AUTO的好處，加上ORDER BY 就會像上面的結果，不同的是全都以屬性展現--------------------
SELECT 客戶.客戶編號,訂單號碼,訂單日期,運費,公司名稱,連絡人,連絡人職稱,地址
FROM 訂貨主檔 JOIN 客戶 
ON 訂貨主檔.客戶編號 = 客戶.客戶編號
ORDER BY 客戶.客戶編號
FOR XML AUTO,ROOT('客戶訂單一覽')