Dunk

-- 資料來源:德瑞克老師的上課筆記+自行修改
-- 出現無法轉換代碼可以參考如下
-- http://blog.csdn.net/dfzone/article/details/4852313
-- http://social.msdn.microsoft.com/Forums/zh-TW/233/thread/e3db1817-05fd-4e9d-8495-c7b76479b9d3
-- http://www.cnblogs.com/coolhao-chen/archive/2011/08/10/2133647.html
-- http://s.yanghao.org/program/viewdetail.php?i=67921
-- http://www.zhtwco.info/index.php?db=so&id=384974
USE ProductDB
GO

-- 把將網頁的資料擷取出來
-- function dbo.uf_FetchHtmlContent 可以參照下列網址建立
-- http://www.dotblogs.com.tw/killysss/archive/2012/09/05/74615.aspx
-- 如果檔頭包含<?xml version="1.0" encoding="utf-8"?>，要將檔頭的字串去除
-- SELECT substring(dbo.uf_FetchHtmlContent(@url),39,len(dbo.uf_FetchHtmlContent(@url))-38) AS [網頁內容]  
-- 這裡以<<蘋果日報>>運動靚妹的RSS訂閱做練習
DECLARE @url nvarchar(500);
SET @url = N'http://www.appledaily.com.tw/rss/create/kind/col/type/33395717';
DECLARE @htmlsource xml;
SET @htmlsource = substring(dbo.uf_FetchHtmlContent(@url), 39, len(dbo.uf_FetchHtmlContent(@url)) - 38);
SELECT @htmlsource AS [網頁內容] 

-- 建立XML資料表為單位
DECLARE @xmltable table
(
	STAFF nvarchar(6),
	EmpInfo xml
);

declare @STAFF nvarchar(6)
SET @STAFF = N'123456';

INSERT INTO @xmltable (STAFF
					 , EmpInfo)
SELECT @STAFF AS EMPID
	 , (SELECT STAFF
			 , NAME
			 , BRNO
		FROM
			(SELECT *
			 FROM
				 dbo.EMPLOYEE
			 WHERE
				 STAFF = @STAFF) a
		FOR XML
			PATH ('EMPDATA'), ROOT ('EMPLOYEE')) AS EMPDATA
SELECT *
FROM
	@xmltable
GO

-- 使用OPENXML
DECLARE   @idoc   int 
DECLARE   @doc   varchar(max)
SET @doc = N'<?xml version="1.0" encoding="gb2312" ?><ArrayOfHotelDetail><HotelDetail><id>00101006</id><name>?德?</name><country>China</country></HotelDetail></ArrayOfHotelDetail>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *
FROM
	openxml(@idoc, '/ArrayOfHotelDetail/HotelDetail', 2) WITH (id CHAR(15), name NVARCHAR(50), country NVARCHAR(50))

EXEC sp_xml_removedocument @idoc

-- http://www.codeproject.com/Tips/393872/Generate-XML-Using-FOR-XML
-- http://www.cnblogs.com/guanhp2013/archive/2012/07/16/2594393.html
-- 使用XML AUTO
SELECT *
FROM
	ProductDB.dbo.EMPLOYEE
FOR XML
	AUTO

-- XML RAW
SELECT *
FROM
	ProductDB.dbo.EMPLOYEE
FOR XML
	RAW

SELECT *
FROM
	ProductDB.dbo.EMPLOYEE
FOR XML
	RAW ('EmpData')


-- XML PATH
SELECT *
FROM
	ProductDB.dbo.EMPLOYEE
FOR XML
	PATH ('STAFF')


-- XML EXPLICIT
DECLARE @STAFF char(6)
SET @STAFF = '123456';

SELECT 1 AS Tag
	 , NULL AS Parent
	 , STAFF AS 'EMPDATA!1!STAFF'
	 , NULL AS 'Detail!2!NAME'
	 , NULL AS 'Detail!2!BRNO'
	 , NULL AS 'Detail!2!STATUS_CODE'
FROM
	ProductDB.dbo.EMPLOYEE
WHERE
	STAFF = @STAFF
UNION ALL
SELECT 2 AS Tag
	 , 1 AS Parent
	 , NULL
	 , NAME
	 , BRNO
	 , STATUS_CODE
FROM
	ProductDB.dbo.EMPLOYEE
WHERE
	STAFF = @STAFF
FOR XML
	EXPLICIT