将xml数据插入sql server表 [英] Inserting xml data into sql server Table

问题描述

i在我的本地机器上有一个xml文件，即

Hi,

i had one xml file in my local mechine,i.e..

-<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需要帮助才能插入xml数据直接通过查询进入sql server表，任何人都可以帮助我...

i need help to insert the xml data into the sql server table directly throught the query, can any one help me...

推荐答案

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

你好brajunaresh，



你可以试试这个...

Hello brajunaresh,

You can try this...

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

这篇关于将xml数据插入sql server表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！