Click here to Skip to main content
14,872,753 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hi,

i had one xml file in my local mechine,i.e..
XML
-<Processes> -<Process> <UserID>PW-IT\10819420</UserID> <MachineName>UTCIT-030</MachineName> <Date>21-05-2013</Date> <DocumentTitle>Importing XML file and insert data into SQL server table </DocumentTitle> <ApplicationName> Windows Internet Explorer provided by Infotech Enterprises Limited</ApplicationName> <StartTime>12:23</StartTime> <EndTime>12:24</EndTime> <Usage>1</Usage> </Process>



i need help to insert the xml data into the sql server table directly throught the query, can any one help me...
Posted
Updated 31-Jan-18 4:32am

SQL
DECLARE @xml xml
SET @xml = N'<Processes> 
<Process> <UserID>PW-IT\10819420</UserID> 
<MachineName>UTCIT-030</MachineName> 
<Date>21-05-2013</Date> 
<DocumentTitle>Importing XML file and insert data into SQL server table </DocumentTitle> 
<ApplicationName> Windows Internet Explorer provided by Infotech Enterprises Limited</ApplicationName>
 <StartTime>12:23</StartTime> 
 <EndTime>12:24</EndTime> 
 <Usage>1</Usage> 
 </Process></Processes> '


SELECT
 doc.col.value('UserID[1]', 'nvarchar(10)') UserID
,doc.col.value('MachineName[1]', 'varchar(100)') MachineName 
,doc.col.value('Date[1]', 'nvarchar(10)') Date 
,doc.col.value('DocumentTitle[1]', 'nvarchar(10)') DocumentTitle 
,doc.col.value('ApplicationName[1]', 'nvarchar(10)') ApplicationName 
,doc.col.value('StartTime[1]', 'nvarchar(10)') StartTime 
,doc.col.value('EndTime[1]', 'nvarchar(10)') EndTime 
,doc.col.value('Usage[1]', 'nvarchar(10)') Usage 
FROM @xml.nodes('/Processes/Process') doc(col)

Happy Coding!
:)
   
v2
Comments
brajunaresh 21-May-13 6:11am
   
hi Aathi,
thank u, but i need to pass the xml local path in the Query, then the data in the xml will inserted into the table.

SELECT @xmlData = BulkColumn
FROM OPENROWSET
(BULK 'D:\Process.xml',SINGLE_CLOB ) as a

SELECT UserID = a.value('(USERID/text())[1]','VARCHAR(100)'),
Machine_Name = a.value('(MAchineName/text())[1]','VARCHAR(100)'),
CurrentDate = a.value('(CurrentDate/text())[1]','VARCHAR(100)'),
DocumentTitle = a.value('(DocumentTitle/text())[1]','VARCHAR(100)')
from @xmlData.nodes('/Processes/Process') x(a)
Aarti Meswania 21-May-13 6:30am
   
have you visited link ?
http://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/
brajunaresh 21-May-13 6:24am
   
Hi Aarti,
thank u, its working fine, but in my Process.xml i had lot of records can directly load into @XML by passing local file path
Aarti Meswania 21-May-13 6:51am
   
okay I don't know but you can simply search
"How to read file using sql" then assign file data in @xml variable
Member 11695004 18-May-15 16:21pm
   
can we insert a value from parameter with xml bulk insertion?
i want to insert all from xml and one column from parameter. for foreign key propose. can i do this?
Hello brajunaresh,

You can try this...

SQL
declare @xmldata as xml

set @xmldata= (SELECT CONVERT(XML, BulkColumn) AS BulkColumn
FROM OPENROWSET(BULK 'D:\a.xml', SINGLE_BLOB)as X)

or

select @xmldata = CONVERT(XML, BulkColumn) FROM OPENROWSET(BULK 'D:\a.xml', SINGLE_BLOB)as X





happy coding :)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900