Load/Import XML file into database table (Oracle or SQL Server)
Load/Import XML file into database table (Oracle or SQL Server)
RESULT
Let say you have some XML file and you want to load xml data into database table, there are tons of xml structures and way to do the same.
I have taken very simple type of XML so that it should be easy to understand the basic of this functionality. I have seen lots of example given to take input parameter as xml string not xml as file. I am giving example to take input as xml file.
I also have given example that works with Oracle database as well in SQL server, because database could be any for this requirement.
I have tested following example with
- Oracle Express Edition 10.2
- SQL Server 2008
- Window 7
XML File Name: employee.xml
<ROWSET>
<ROW ID="10">
<EmpName>RiteshEmpName>
<EmpSal>10000EmpSal>
<DeptNo>1010DeptNo>
<JoinDate>03-Apr-2006JoinDate>
ROW>
<ROW ID="20">
<EmpName>ArtiEmpName>
<EmpSal>20000EmpSal>
<DeptNo>2020DeptNo>
<JoinDate>05-May-2007JoinDate>
ROW>
ROWSET>
Note: “ID” value just taken here to demo to read attribute value.
Oracle Solution
1) Create Virtual directory from Oracle SQL Prompt
SQL> CREATE directory test_dir AS 'c:\Test';
2) Copy “employee.xml” file into “c:\Test”
3) Create database Table name “EmployeeXML”
On the above table for Oracle I have created table with all columns data type as varchar2, I could not find correct syntax in a SQL query to convert datatype from String to int etc.
But anyway this is good to start, I will update later if I get a time.
3) Oracle SQL query to load data into EmplyeeXML Table
INSERT INTO EmployeeXML(EmpID,EmpName,EmpSal,DeptNo,JoinDate)
WITH t AS (SELECT xmltype(bfilename('TEST_DIR','employeexml.xml'), nls_charset_id('WE8ISO8859P1'))xmlcol FROM dual)
SELECT
extractValue(value(x),'/ROW/@ID') empid ///* READ ATTRIBUTES */
,extractValue(value(x),'/ROW/EMPNAME') empname ///* READ VALUES */
,extractValue(value(x),'ROW/EMPSAL') empsal
,extractValue(value(x),'ROW/DEPTNO') deptno
,extractValue(value(x),'ROW/JOINDATE') joindate
FROM t,TABLE(XMLSequence(extract(t.xmlcol,'/ROWSET/ROW'))) x;
SQL Server Solution
In SQL server mostly you pass xml as string to stored procedure input parameter, but I was looking to pass XML file name as input parameter and below I am giving example using Bulk Insert.
In SQL server we need to go with 2 steps process, add data into temp table and then load data from temp table to emplyeexml table.
1) Create Temp table and load XML into it
--Create Temp Table
CREATE TABLE #TempTable (Data XML)
2) Create ‘EmployeeXML’ table and load XML data into it
--Check EmployeeXML table if already exist then TRUNCATE
IF NOT EXISTS (SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[EmployeeXML]')
AND type in (N'U'))
CREATE TABLE [dbo].[EmployeeXML] (
[EmpId] [int] NOT NULL
,[EmpName] [nvarchar](200) NULL
,[EmpSal] [int] NULL
,[DeptNo] [int] NULL
,[JoinDate] [datetime] NULL
)
ELSE
TRUNCATE TABLE dbo.[EmployeeXML]
3) Insert XML into Temp table
/* Populate the temp table with the employee.xml file */
INSERT INTO #TempTable
SELECT *
FROM OPENROWSET(BULK 'c:\Ritesh\employee.xml',SINGLE_BLOB) AS data
4) Insert XML into Temp table
/* Import the users records from the working table */
DECLARE @XML AS XML
,@hDoc AS INT
SELECT @XML = Data
FROM #TempTable
EXEC sp_xml_preparedocument
@hDoc OUTPUT ,
@XML
/* Insert data into employeexml table from temptable */
INSERT INTO dbo.EmployeeXML
( EmpId
,EmpName
, EmpSal
,DeptNo
,JoinDate)
SELECT Id
,EmpName
,EmpSal
,DeptNo
,CAST(JoinDate AS DATETIME)
FROM OPENXML (@hDoc, '/ROWSET/ROW', 2) /* 2- READ VALUE (Hint below) */
WITH ( ID INT '@ID', /* READ ATTRIBUTE*/
EmpName NVARCHAR(200) 'EmpName', /*READ VALUES */
EmpSal INT 'EmpSale',
DeptNo INT 'DeptNo',
JoinDate VARCHAR(50) 'JoinDate')
/* Clean up and empty out temporary table */
EXEC sp_xml_removedocument @hDoc
4) Drop temp table
DROP TABLE #TempTable
Data will be inserted into database table
沒有留言:
張貼留言