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.
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 columnsCREATE 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 StudentNumberFROM (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_InfoThe columns Names are: LogDateTime, StudentNumber, FirstName, LastName, PhoneNumber, CH and MTHand 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 fileSELECT CAST(c AS XML) AS c1FROM 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 StudentNumberFROM cte AS T1 CROSS APPLY c1.nodes('Data/records/record')T2(c2) |
|
|
|
|
|
|
|