David的部落格

• 準備資料：資料來源政府資料開放平台

建檔公廁明細-苗栗縣(https://data.gov.tw/dataset/34866)

• 建立一個存放XML資料表，若已存在，該步驟可以省略

CREATE TABLE XMLwithOpenXML
(
   XMLData XML
)

• 建立匯入資料表

create table Toilet
(
     Country nvarchar(100),  --若資料有可能空值需設定為   Country nvarchar(100) Nulll,
     City nvarchar(100),
     Village nvarchar(100),
     Number nvarchar(100),
       Name nvarchar(100),
       Address nvarchar(100),
     Administration nvarchar(100),
     Latitude float,
     Longitude float,
     Grade nvarchar(100),
     Type2 nvarchar(100),
       Type nvarchar(100)
)

• 若資料表已存在

truncate table XMLwithOpenXML
truncate table Toilet

• 將XML 放入xml table 中

INSERT INTO XMLwithOpenXML(XMLData )
     SELECT CONVERT( xml ,BulkColumn, 2)  AS BulkColumn
     FROM OPENROWSET(BULK 'D:\教學\Database\SQL server\建檔公廁明細-苗栗縣.xml', SINGLE_BLOB) AS x;

• 將XML資料匯入Table中

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
INSERT INTO Toilet (Country, City, Village, Number, Name, Address, Administration, Latitude, Longitude, Grade, Type2, Type)   --匯入資料表
     SELECT CTY, City, Village, Number, Name, Address, Administration, Latitude, Longitude, Grade, Type2, Type    --欄位名稱需與OPENXML 解析出來欄位對應
          FROM OPENXML(@hDoc, 'OTH01089/Data')    -- XML path，需要設定到最底層，若僅設定到中間層，以下層資料不會擷取
          WITH
          (
               CTY nvarchar(100) 'Country',    --該層的資料，若是attribute value必須使用@  ex: '@ident'
               City nvarchar(100) 'City',           --單引號參數是XML的Tag value 或 Attribute value (@)
               Village nvarchar(100) 'Village',     --若是取上一層Tag，利用../相對路徑取得  ex: '../../class/version'
               Number nvarchar(100) 'Number',
                         Name nvarchar(100) 'Name',
                         Address nvarchar(100) 'Address',
               Administration nvarchar(100) 'Administration',
               Latitude float 'Latitude',
               Longitude float 'Longitude',
               Grade nvarchar(100) 'Grade',
               Type2 nvarchar(100) 'Type2',
               Type nvarchar(100) 'Type'
          )
EXEC sp_xml_removedocument @hDoc
GO

SQL script 
/* 

drop table XMLwithOpenXML

CREATE TABLE XMLwithOpenXML

(

XMLData XML

)

--Country(縣市)、City(鄉鎮)、Village(村里)、Number(建檔編號)、Name(建檔名稱)、Address(地址)、Administration(主管機關)、Latitude(緯度)、Longitude(經度)、Grade(等級)、Type2(公廁類別)、Type(公廁類型)

create table Toilet

(

Country nvarchar(100),  --若資料有可能空值需設定為   Country nvarchar(100) Nulll,

City nvarchar(100),

Village nvarchar(100),

Number nvarchar(100),

Name nvarchar(100),

Address nvarchar(100),

Administration nvarchar(100),

Latitude float,

Longitude float,

Grade nvarchar(100),

Type2 nvarchar(100),

Type nvarchar(100)

)

*/

truncate table XMLwithOpenXML

truncate table Toilet

INSERT INTO XMLwithOpenXML(XMLData )

SELECT CONVERT( xml ,BulkColumn, 2)  AS BulkColumn 

FROM OPENROWSET(BULK 'D:\教學\Database\SQL server\建檔公廁明細-苗栗縣.xml', SINGLE_BLOB) AS x;

--查詢匯入XML資料

SELECT * FROM XMLwithOpenXML

--將資料由xml Table轉入資料表中

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM XMLwithOpenXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

--利用insert指令，將資料逐一insert到資料表中

INSERT INTO Toilet (Country, City, Village, Number, Name, Address, Administration, Latitude, Longitude, Grade, Type2, Type)   --匯入資料表

SELECT CTY, City, Village, Number, Name, Address, Administration, Latitude, Longitude, Grade, Type2, Type    --欄位名稱需與OPENXML 解析出來欄位對應

FROM OPENXML(@hDoc, 'OTH01089/Data')    -- XML path，需要設定到最底層，若僅設定到中間層，以下層資料不會擷取

WITH 

(

CTY nvarchar(100) 'Country',    --該層的資料，若是attribute value必須使用@  ex: '@ident'

City nvarchar(100) 'City',           --單引號參數是XML的Tag value 或 Attribute value (@)

Village nvarchar(100) 'Village',     --若是取上一層Tag，利用../相對路徑取得  ex: '../../../../../../class/version'

Number nvarchar(100) 'Number',

Name nvarchar(100) 'Name',

Address nvarchar(100) 'Address',

Administration nvarchar(100) 'Administration',

Latitude float 'Latitude',

Longitude float 'Longitude',

Grade nvarchar(100) 'Grade',

Type2 nvarchar(100) 'Type2',

Type nvarchar(100) 'Type'

)

EXEC sp_xml_removedocument @hDoc

GO

select *  from Toilet