摘要:匯入Xml IN DB 並轉換為自訂格式的 Table
前言：
因工作專案需求，寫下來紀錄。
平時，我們在將 Xml 的內容匯入時，可以採用下方作法： OPENXML (Transact-SQL)
範例：
執行後：
假使我們今天要轉換的 xml 資料是一整個檔案，並且轉換為我們自訂的格式時，做法內容如下：
檢視 xml 內容：
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ReportDefinition type="xml">
<ReportSection optionalSubsections="false" enabled="false">
<Title>報表彙總資訊</Title>
<SubSection enabled="true">
<Title>測試資訊</Title>
<Description>提供顯示測試的基本資訊</Description>
<Text>測試日期:2011/6/25 ,測試主機名稱:TEST-AP-01</Text>
</SubSection>
</ReportSection>
<ReportSection optionalSubsections="false" enabled="true">
<Title>測試彙總清單</Title>
<SubSection enabled="true">
<Title>測試類別數統計:等級一</Title>
<IssueListing limit="-1" listing="false">
<Refinement>等級一</Refinement>
<Chart chartType="table">
<Axis>File Name</Axis>
<MajorAttribute>Analysis</MajorAttribute>
<GroupingSection count="14">
<groupTitle>Folder/AAA/BBB/CCC/FileName1.aspx.cs</groupTitle>
</GroupingSection>
<GroupingSection count="10">
<groupTitle>Folder/AAA/BBB/CCC/FileName2.aspx.cs</groupTitle>
</GroupingSection>
<GroupingSection count="6">
<groupTitle>Folder/AAA/BBB/CCC/FileName3.aspx.cs</groupTitle>
</GroupingSection>
</Chart>
</IssueListing>
</SubSection>
<SubSection enabled="true">
<Title>測試類別數統計:等級二</Title>
<IssueListing limit="-1" listing="false">
<Refinement>等級二</Refinement>
<Chart chartType="table">
<Axis>File Name</Axis>
<MajorAttribute>Analysis</MajorAttribute>
<GroupingSection count="7">
<groupTitle>Folder/AAA/BBB/CCC/FileName4.aspx.cs</groupTitle>
</GroupingSection>
<GroupingSection count="1">
<groupTitle>Folder/AAA/BBB/CCC/XXX/FileName5.aspx.cs</groupTitle>
</GroupingSection>
<GroupingSection count="1">
<groupTitle>Folder/AAA/BBB/CCC/XXX/FileName6.aspx.cs</groupTitle>
</GroupingSection>
</Chart>
</IssueListing>
</SubSection>
</ReportSection>
</ReportDefinition>
自訂的 table 格式：
我們希望能將 Xml 內容轉換為 以下 table 格式
Group Env Module FileName CountNum ------------------------------------------------------------------------ AAA BBB CCC FileName1 14 AAA BBB CCC FileName2 10 AAA BBB CCC FileName3 6 AAA BBB CCC FileName4 7 AAA BBB CCC FileName5 1 AAA BBB CCC FileName6 1
檢視 xml 內容中，字串格式：
Folder/AAA/BBB/CCC/FileName1.aspx.cs 可以將其看成 Folder/GroupFolder/EnvFolder/ModuleFolder/FileName
前置動作：
由於我們需要大量資料匯入，故可以使用 BULK INSERT 或 OPENROWSET(BULK...) 匯入大量資料 、大量匯入和匯出 XML 文件的範例
DECLARE @xml XML
DECLARE @Nxml XML
SELECT @xml=BulkColumn FROM OPENROWSET(BULK 'D:\XML\test.xml',SINGLE_BLOB) RS
作法：
我們的需求資料於"測試彙總清單"，那取出 "測試彙總清單" 的作法，該怎麼做呢? 請繼續看下去 (......)
SELECT @Nxml=@xml.query('/ReportDefinition/ReportSection[@enabled="true"]/SubSection[1]/IssueListing/Chart')
1. 搜尋 ReportDefinition 節點
2. ReportDefinition 狀態為 enabled="true"
3. 找尋 SubSection[1] ： 1 為「等級一」，2為「等級二」
再取出 「等級一」、「等級二」中，我們本次任務最重要的資料，並將資料寫入MyTable 中
T-SQL：
SET QUOTED_IDENTIFIER ON
DECLARE @xml XML
DECLARE @Nxml XML
SELECT @xml=BulkColumn FROM OPENROWSET(BULK 'D:\XML\test.xml',SINGLE_BLOB) RS
--============================擷取 XML 字串資訊，SubSection[1]為 等級一 ============================
SELECT @Nxml=@xml.query('/ReportDefinition/ReportSection[@enabled="true"]/SubSection[1]/IssueListing/Chart')
DECLARE @docHandle AS int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @Nxml
SELECT * INTO #TEMP_1 FROM
OPENXML (@docHandle, '/Chart/GroupingSection/groupTitle')
with (CountNum int '../@count',
[FileName] varchar(150) '../groupTitle')
--使用 可程式性/函數/純量值函數 的 Function，擷取字串，例： Folder/GroupFolder/EnvFolder/ModuleFolder/FileName1.aspx.cs
INSERT MyTable
SELECT
LEFT(DBO.DBA_STR([FileName]),CHARINDEX('/',DBO.DBA_STR([FileName]))-1) AS GroupFolder,
LEFT(DBO.DBA_STR(DBO.DBA_STR([FileName])),CHARINDEX('/',DBO.DBA_STR(DBO.DBA_STR([FileName])))-1) AS EnvFolder,
LEFT(DBO.DBA_STR(DBO.DBA_STR(DBO.DBA_STR([FileName]))),CHARINDEX('/',DBO.DBA_STR(DBO.DBA_STR(DBO.DBA_STR([FileName]))))-1) AS ModuleFolder,
SUBSTRING([dbo].[DBA_STR_R]([FileName]),0,CHARINDEX('.',[dbo].[DBA_STR_R]([FileName])))AS tFileName,
CountNum
FROM #TEMP_1
DROP TABLE #TEMP_1
--============================擷取 XML 字串資訊，SubSection[2]為 等級二 ============================
SELECT @Nxml=@xml.query('/ReportDefinition/ReportSection[@enabled="true"]/SubSection[2]/IssueListing/Chart')
DECLARE @docHandle AS int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @Nxml
SELECT * INTO #TEMP_2 FROM
OPENXML (@docHandle, '/Chart/GroupingSection/groupTitle')
with (CountNum int '../@count',
[FileName] varchar(150) '../groupTitle')
--使用 可程式性/函數/純量值函數 的 Function，擷取字串，例： Folder/GroupFolder/EnvFolder/ModuleFolder/FileName1.aspx.cs
INSERT MyTable
SELECT
LEFT(DBO.DBA_STR([FileName]),CHARINDEX('/',DBO.DBA_STR([FileName]))-1) AS GroupFolder,
LEFT(DBO.DBA_STR(DBO.DBA_STR([FileName])),CHARINDEX('/',DBO.DBA_STR(DBO.DBA_STR([FileName])))-1) AS EnvFolder,
LEFT(DBO.DBA_STR(DBO.DBA_STR(DBO.DBA_STR([FileName]))),CHARINDEX('/',DBO.DBA_STR(DBO.DBA_STR(DBO.DBA_STR([FileName]))))-1) AS ModuleFolder,
SUBSTRING([dbo].[DBA_STR_R]([FileName]),0,CHARINDEX('.',[dbo].[DBA_STR_R]([FileName])))AS tFileName,
CountNum
FROM #TEMP_2
DROP TABLE #TEMP_2
T-SQL中，使用的函數介紹：
在以上語法中，使用了兩個 Function，來擷取字串：DBO.DBA_STR & DBA_STR_R ，可自行 CREATE
FUNCTION [dbo].[DBA_STR] (@Fname varchar(100))
RETURNS VARchar(100)
AS
BEGIN
DECLARE @RETURN VARCHAR(100)
SET @RETURN=SUBSTRING(@Fname ,CHARINDEX('/',@Fname )+1,LEN(@Fname ))
return @RETURN
END
FUNCTION [dbo].[DBA_STR_R] (@Fname varchar(100))
RETURNS VARchar(100)
AS
BEGIN
DECLARE @RETURN VARCHAR(100)
set @RETURN=@Fname
while (CHARINDEX('/',@RETURN)>0)
begin
SET @RETURN=SUBSTRING(@RETURN,CHARINDEX('/',@RETURN)+1,LEN(@RETURN))
end
return @RETURN
END
函數說明：
DBO.DBA_STR 是為了去擷取 " / " 之前的 Folder 資料，從 Group 開始抓，取出 Group、Env、Module 這三個我們需要的值即可。
而 DBO.DBA_STR_R 是利用 DBO.DBA_STR 函數的字串，採用遞迴方式去抓我們需要的 FileName
如此一來就可以達成我們 Xml 轉換成 自訂格式的 table 。
結語說明：
以上是小弟的示範，寫法、觀念上不足之處，請大家見諒，也麻煩大家不吝給予指教。
