SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help Me to insert XML file into a Data Base
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AraKan
Starting Member

Canada
2 Posts

Posted - 02/25/2014 :  16:00:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 02/25/2014 :  18:01:02  Show Profile  Reply with Quote
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
Go to Top of Page

AraKan
Starting Member

Canada
2 Posts

Posted - 02/26/2014 :  10:25:49  Show Profile  Reply with Quote
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

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 02/26/2014 :  16:37:41  Show Profile  Reply with Quote
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)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000