Inserting xml data into sql server Table

Question

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 20-May-13 23:51pm

brajunaresh

Updated 31-Jan-18 4:32am

Add a Solution

2 solutions

Solution 1

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!
:)

Posted 20-May-13 23:58pm

Aarti Meswania

Updated 21-May-13 0:06am

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?

Add a Solution

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

Adarsh chauhan · Accepted Answer · 2013-05-22T00:39:00

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 :)

Inserting xml data into sql server Table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Inserting xml data into sql server Table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us