Help Me to insert XML file into a Data Base

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help Me to insert XML file into a Data Base

Author

Topic

AraKan
Starting Member

2 Posts

Posted - 2014-02-25 : 16:00:38

Hi, I would like to insert a XML file like below into a 2008 SQL Server database. Please help me.

?xml version="1.0" encoding="UTF-8"?>
<Data>
<records>
<record>
<LogDateTime>2013-11-12T11:51:28</LogDateTime>
<StudentNumber>1245345</StudentNumber>
<FirstName>Hello</FirstName>
<LastName>THERE</LastName>
<PhoneNumber>456-999-1111</PhoneNumber>
<CH>98.0</CH>
<MTH>89.9</MTH>
</record>
<record>
<LogDateTime>2013-11-12T11:51:28</LogDateTime>
<StudentNumber>1243335</StudentNumber>
<FirstName>John</FirstName>
<LastName>Doe</LastName>
<PhoneNumber>456-999-1111</PhoneNumber>
<CH>91.0</CH>
<MTH>87.9</MTH>

More Record like These

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-02-25 : 18:01:02

If you want to insert the XML just create a table with a single XML column and insert into it. Like in this example:

CREATE TABLE #xmlTable(xmlCol XML);
INSERT INTO #xmlTable VALUES (@x); -- here @x is your xml string

If you want to shred the XML and insert into a relational table, do it like this - the example shows your first two columns. Repeat as necessary for other columns

CREATE TABLE #ShreddedTable (LogDateTime DATETIME, StudentNumber INT);
INSERT INTO #ShreddedTable
        ( LogDateTime, StudentNumber )
SELECT
	c2.value('LogDateTime[1]','datetime') AS LogDatetime,
	c2.value('StudentNumber[1]','INT') AS StudentNumber
FROM
	(SELECT @x) AS T1(c1)
	CROSS APPLY c1.nodes('Data/records/record')T2(c2)

If the XML is in a file in Windows and you want to import it into SQL database, use one of the queries shown on this page: http://technet.microsoft.com/en-us/library/ms191184.aspx

AraKan
Starting Member

2 Posts

Posted - 2014-02-26 : 10:25:49

Hi James, Thank you for your message. But I still having hard time to insert XML file into 2008 SQL server data base. Can you please help me?

I alreday created a table like below for the XML file.

Tabel Name: Student_Info
The columns Names are: LogDateTime, StudentNumber, FirstName, LastName, PhoneNumber, CH and MTH

and I have the XML file call Student.xml on my Windows C: drive and contains the following info.

?xml version="1.0" encoding="UTF-8"?>
<Data>
<records>
<record>
<LogDateTime>2013-11-12T11:51:28</LogDateTime>
<StudentNumber>1245345</StudentNumber>
<FirstName>Hello</FirstName>
<LastName>THERE</LastName>
<PhoneNumber>456-999-1111</PhoneNumber>
<CH>98.0</CH>
<MTH>89.9</MTH>
</record>
<record>
<LogDateTime>2013-11-12T11:51:28</LogDateTime>
<StudentNumber>1243335</StudentNumber>
<FirstName>John</FirstName>
<LastName>Doe</LastName>
<PhoneNumber>456-999-1111</PhoneNumber>
<CH>91.0</CH>
<MTH>87.9</MTH>

More Record like These

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-02-26 : 16:37:41

Use the link that I gave you in my previous post. So for example, if you had the file on C:\temp and is named Sample.xml, you would do this to read the xml file

SELECT
   CAST(c AS XML) AS c1
FROM
   OPENROWSET(BULK 'c:\Temp\Sample.xml', SINGLE_BLOB) AS x ( c );

Remember that the path is on the SERVER, not on the local machine. If your file is on the local machine, use UNC paths.

You can insert the results of the query into a temporary table with an XML column and then do whatever you want with it. If you want to directly shred it without first storing into a temp table, do it like this:

;WITH cte AS
(
	 SELECT
	   CAST(c AS XML) AS c1
	FROM
	   OPENROWSET(BULK 'c:\Temp\Sample.xml', SINGLE_BLOB) AS x ( c )
)
 
SELECT
	c2.value('LogDateTime[1]','datetime') AS LogDatetime,
	c2.value('StudentNumber[1]','INT') AS StudentNumber
FROM
	cte AS T1
	CROSS APPLY c1.nodes('Data/records/record')T2(c2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources