匯入Xml IN DB 並轉換為 Table

摘要:匯入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 。

結語說明:

以上是小弟的示範,寫法、觀念上不足之處,請大家見諒,也麻煩大家不吝給予指教。

參考資料:

1. OPENXML (Transact-SQL)

2. 使用 BULK INSERT 或 OPENROWSET(BULK...) 匯入大量資料

3. 大量匯入和匯出 XML 文件的範例